Oracle Apps R12 Schema Analysis
Continuing on my series on analysis, here is some of the initial analysis I did after installing Oracle applications R12. Much of the analysis involves queries to the Oracle data dictionary under the covers. This analysis is on the sample “database”, VIS, which is actually composed of many schemas.
I also did a fair bit of analysis from a DBA standpoint: tablespaces, logs, processes, DBMS_JOBS, etc. And other analysis on the unix and middle tier side. Tuning. I’ll try to keep it all organized in different posts.
When I’ve started a new position, these are the types of queries I might run on the schemas to get a feel for the systems. Just how was it made? Does it use PK, triggers, or external code to enforce data integrity? What are the most important tables? What are potential performance issues? Any issues? And so on.
Many of these queries you won’t find the GUIs like SQL Developer, or Toad. You have to write them yourself. It pays big dividends to know the data dictionary!
(Sorry for the odd font. WordPress has not made changing the font for select sets of text easy or obvious. If anyone knows the secret, on how to change fonts in WordPress A LA Wordpad, please let me know. )
------------------------------------------------ ------------------------------------------------ How many tables are there?
select count(*)
from dba_tables
COUNT(*)
----------
35637
------------------------------------------------
------------------------------------------------
What are the schemas, and how many OBJECTS in each schema?
select OWNER
, count(*)
from dba_objects
group by owner
order by owner
OWNER COUNT(*)
------------------------------ ----------
ADS10GEUL_US 266
AHL 632
AK 191
AMS 1540
AMV 188
AMW 500
AOLDEMO 12
AP 1132
APPLSYS 4122
APPLSYSPUB 25
APPS 193095
AR 3501
ASF 10
ASG 181
ASL 59
ASN 4
ASO 297
ASP 14
AST 38
AX 145
AZ 107
B2B 3812
BAM 59
BEN 2796
…
TSMSYS 3
UDDISYS 189
VEA 48
WCRSYS 30
WIP 356
WIRELESS 1206
WKSYS 370
WK_TEST 47
WMS 413
WPS 7
WSH 517
WSM 133
XDB 880
XDO 64
XDP 295
XLA 1214
XLE 85
XNB 13
XNP 265
XTR 545
ZFA 4
ZPB 3636
ZSA 4
ZX 383
271 rows selected.
------------------------------------------------
------------------------------------------------
What are the schemas, and how many TABLES in each schema?
Order by the count, to see which schemas have the most tables,
as a proxy for importance.
select owner
, count(*)
from dba_tables
group by owner
order by 2
...
FII 589
JTF 651
AR 686
MSC 701
BIS 707
PA 720
APPLSYS 937
SYS 955
BSC 986
HR 1300
APPS 1492
IGS 1636
FEM 1715
GL 2128
229 rows selected.
From the number of tables, the important schemas are:
HR, APPS, IGS, FEM, GL
------------------------------------------------
------------------------------------------------
What are the most common schemas and object types?
select owner, object_type
, count(*)
from dba_objects
group by owner, object_type
order by 3
...
APPS TABLE 1492
BSC INDEX 1513
ORDSYS JAVA CLASS 1525
B2B TRIGGER 1528
IGS TABLE 1636
FEM TABLE 1715
BEN INDEX 1819
GL TABLE 2128
APPS INDEX 2157
FEM INDEX 2422
EGO INDEX SUBPARTITION 2514
HR INDEX 2799
EGO INDEX PARTITION 2882
IGS INDEX 3101
APPS TYPE 3775
SYS VIEW 3776
GL INDEX 4036
APPS TRIGGER 4183
SYS JAVA CLASS 20335
APPS VIEW 32991
PUBLIC SYNONYM 35181
APPS SYNONYM 42331
APPS PACKAGE BODY 51284
APPS PACKAGE 52328
1656 rows selected.
The schema APPS, uses a LOT of packages. Only 1492 tables, but 51,284 packages!
Over 34 packages per table!
APPS also has 3775 TYPEs! I've never seen so many TYPEs before.
------------------------------------------------
------------------------------------------------
How many indexes are unique vs. not?
select UNIQUENESS
, count(*)
from dba_indexes
group by UNIQUENESS
UNIQUENES COUNT(*)
--------- ----------
UNIQUE 31216
NONUNIQUE 30661
In the entire database, only about half of all the indexes are Unique.
------------------------------------------------
------------------------------------------------
By schema, how many indexes are Unique?
select owner, UNIQUENESS
, count(*)
from dba_indexes
group by owner, UNIQUENESS
order by count(*)
...
BIS UNIQUE 513
AMS NONUNIQUE 521
JTF UNIQUE 555
AR UNIQUE 570
OKL NONUNIQUE 572
PA UNIQUE 582
BIS NONUNIQUE 626
JTF NONUNIQUE 700
PA NONUNIQUE 732
GL UNIQUE 743
B2B UNIQUE 770
AR NONUNIQUE 821
APPS UNIQUE 822
SYS UNIQUE 855
APPLSYS UNIQUE 1018
HR UNIQUE 1305
BEN NONUNIQUE 1355
BSC UNIQUE 1386
IGS NONUNIQUE 1464
APPS NONUNIQUE 1485
HR NONUNIQUE 1559
IGS UNIQUE 1651
FEM UNIQUE 2218
GL NONUNIQUE 3293
437 rows selected.
The important schemas, APPS, HR, GL, mainly use NONUNIQUE indexes.
------------------------------------------------
------------------------------------------------
By schema, what is the count of Unique vs. Non-Unique indexes,
where the unique index count, is greater than the non-unique?
SELECT u.owner
, u.the_count as unique_count
, nonunique.the_count as nonunique_count
from
(
select owner, UNIQUENESS
, count(*) AS the_count
from dba_indexes
where uniqueness = 'UNIQUE'
group by owner, UNIQUENESS
) U ,
(
select owner, UNIQUENESS
, count(*) AS the_count
from dba_indexes
where uniqueness = 'NONUNIQUE'
group by owner, UNIQUENESS
) nonunique
where u.owner = nonunique.owner
and u.the_count > nonunique.the_count
order by u.owner
OWNER UNIQUE_COUNT NONUNIQUE_COUNT
....
PRP 52 18
QPR 100 48
QRM 13 5
RE 17 5
RG 68 19
RRS 32 24
SCOTT 28 21
SIZZLE_DDR 2 1
SSP 9 6
SYS 855 253
SYSTEM 179 126
VEA 14 7
WCRSYS 9 7
WIRELESS 337 173
WKSYS 67 12
WK_TEST 14 10
WSM 36 31
XDB 393 19
XDO 23 1
XDP 101 52
XLA 222 125
XLE 26 17
XNP 83 65
ZPB 236 58
ZX 154 38
120 rows selected.
------------------------------------------------
------------------------------------------------
What is the percentage of non unique indexes by schema, and
which schemas have the highest percent of non-unique indexes?
SELECT u.owner
, u.the_count as unique_count
, nonunique.the_count as nonunique_count
, nonunique.the_count / (u.the_count + nonunique.the_count) * 100
as pct_nonunique
from
(
select owner, UNIQUENESS
, count(*) AS the_count
from dba_indexes
where uniqueness = 'UNIQUE'
group by owner, UNIQUENESS
) U ,
(
select owner, UNIQUENESS
, count(*) AS the_count
from dba_indexes
where uniqueness = 'NONUNIQUE'
group by owner, UNIQUENESS
) nonunique
where u.owner = nonunique.owner
order by u.owner
OWNER UNIQUE_COUNT NONUNIQUE_COUNT PCT_NONUNIQUE
...
OZF 125 335 72.826087
ISC 54 149 73.3990148
OPI 71 196 73.4082397
BEN 467 1355 74.3688255
POA 106 318 75
RLM 8 24 75
AST 3 10 76.9230769
PJI 27 92 77.3109244
GL 743 3293 81.5906838 <-
IES 19 95 83.3333333
BIV 3 17 85
DDD 2 18 90
BIX 3 82 96.4705882
212 rows selected.
The important schema, GL, has 81% non-unique indexes.
------------------------------------------------
------------------------------------------------
Which tables have no constraints at all???
Select OWNER || '.' || TABLE_NAME
As no_CONSTRAINTS
From all_TABLES
Where owner <> 'SYS'
AND owner <> 'SYSTEM'
Group by OWNER || '.' || TABLE_NAME
MINUS
/* Set of all tables with any kind of constraint */
Select OWNER || '.' || TABLE_NAME
As no_constraints
From all_constraints
Where owner <> 'SYS'
AND owner <> 'SYSTEM'
Group by OWNER || '.' || TABLE_NAME
...
ZPB.ZPBDATA2_EXCPT_T
ZPB.ZPBDATA42_EXCPT_T
ZPB.ZPBDATA43_EXCPT_T
ZPB.ZPBDATA62_EXCPT_T
ZPB.ZPBDATA82_EXCPT_T
ZPB.ZPBDATA_EXCPT_T
ZPB.ZPB_BUSAREA_VALIDATIONS
ZPB.ZPB_CURRENT_TIME_TEMP
ZPB.ZPB_DC_VAL_RESULTS_GT
ZPB.ZPB_WF_INACTIVE_USERS_GT
ZX.ZX_DATA_UPLOAD_INTERFACE
ZX.ZX_JURISDICTIONS_GT
ZX.ZX_PTNR_NEG_TAX_LINE_GT
ZX.ZX_TAX_DIST_ID_GT
ZX.ZX_TEST_API_GT
6648 rows selected.
6648 tables have no constraints at all; PK, FK, NOT NULL, value constraints.
------------------------------------------------
------------------------------------------------
Which tables have no foreign keys at all???
Select d1.OWNER || '.' || d1.TABLE_NAME
as table_owner
From all_tables d1
Where d1.owner <> 'SYS'
AND d1.OWNER <> 'SYSTEM'
Group by d1.OWNER || '.' || d1.TABLE_NAME
minus
/* Tables that have parents, are children */
SELECT A1.OWNER || '.' || A1.TABLE_NAME
as table_owner
FROM ALL_CONSTRAINTS A1,
ALL_CONSTRAINTS B1,
ALL_CONS_COLUMNS C1
where A1.Constraint_Name = B1.R_Constraint_name
And A1.Owner=B1.R_Owner
And a1.CONSTRAINT_TYPE IN ('P', 'U')
And C1.Table_name=B1.table_name
And C1.Constraint_Name = B1.Constraint_name
And C1.owner = B1.owner
And c1.owner <> 'SYS'
AND C1.OWNER <> 'SYSTEM'
Group by A1.OWNER || '.' || A1.Table_name
minus
/* Tables that have children, are parents */
SELECT B1.OWNER || '.' || B1.TABLE_NAME
as table_owner
FROM ALL_CONSTRAINTS B1,
ALL_CONS_COLUMNS C1
where B1.R_Constraint_Name = C1.Constraint_name
And B1.R_Owner=C1.Owner
And c1.owner <> 'SYS'
AND C1.OWNER <> 'SYSTEM'
And B1.CONSTRAINT_TYPE = 'R'
GROUP by B1.OWNER || '.' || B1.Table_name
...
XTR.XTR_JOURNALS
XTR.XTR_JOURNAL_ENTRY_ACTIONS
XTR.XTR_MARKET_PRICES
XTR.XTR_MASTER_CURRENCIES
XTR.XTR_MIRROR_DDA_LIMIT_ROW
XTR.XTR_MIRROR_DDA_LIM_ROW_TMP
XTR.XTR_MTS_RECORDS
XTR.XTR_PARTY_DEFAULTS
XTR.XTR_PARTY_INFO
XTR.XTR_PORTFOLIOS
XTR.XTR_POSITION_HISTORY
XTR.XTR_PRODUCT_TYPES
XTR.XTR_PRO_PARAM
XTR.XTR_RATE_SETS
XTR.XTR_REVALUATION_DETAILS
XTR.XTR_REVALUATION_RATES
XTR.XTR_ROLLOVER_TRANSACTIONS
XTR.XTR_STANDING_INSTRUCTIONS
XTR.XTR_TAX_BROKERAGE_RATES
XTR.XTR_TAX_BROKERAGE_SETUP
3540 rows selected.
Elapsed: 00:02:56.38
------------------------------------------------
------------------------------------------------
Which tables have no indexes at all???
Select d1.OWNER || '.' || d1.TABLE_NAME
as table_owner
From all_tables d1
Where d1.owner <> 'SYS'
AND d1.OWNER <> 'SYSTEM'
Group by d1.OWNER || '.' || d1.TABLE_NAME
MINUS
select
TABLE_OWNER || '.' || TABLE_NAME
From all_indexes
Where table_owner <> 'SYS'
AND table_owner <> 'SYSTEM'
…
ZPB.ZPB_UNIV_DIMENSION_GROUPS
ZPB.ZPB_UNIV_HIERARCHIES
ZPB.ZPB_VALIDATION_TEMP_DATA
ZPB.ZPB_WF_INACTIVE_USERS_GT
ZX.ZX_COMPOUND_ERRORS
ZX.ZX_COMPOUND_ERRORS_T
ZX.ZX_DISTCCID_DET_FACTS_GT
ZX.ZX_DISTRIBUTION_LINES_GT
ZX.ZX_MRC_GT
ZX.ZX_PTNR_NEG_LINE_GT
ZX.ZX_PTNR_NEG_TAX_LINE_GT
ZX.ZX_SIM_PROCESS_RESULTS
ZX.ZX_SIM_RULES_B
ZX.ZX_SIM_RULES_TL
ZX.ZX_SIM_RULE_CONDITIONS
ZX.ZX_TAX_DIST_ID_GT
ZX.ZX_TAX_PRIORITIES_T
ZX.ZX_TAX_RELATIONS_T
ZX.ZX_TEST_API_GT
ZX.ZX_UPDATE_CRITERIA_RESULTS
ZX.ZX_VALDN_STATUSES_GT
7225 rows selected.
------------------------------------------------
------------------------------------------------
List tables that have NO PRIMARY KEYS!
Select tables.owner || '.' ||
tables.table_name
as table_owner
From all_tables tables,
(Select
owner,
TABLE_NAME,
constraint_type,
CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
) constr
Where tables.owner = constr.owner (+)
And tables.table_name = constr.table_name (+)
and tables.owner <> 'SYS'
and tables.owner <> 'SYSTEM'
And constr.owner IS NULL
And constr.table_name IS NULL
ORDER BY 1
...
ZX.ZX_SIM_RULES_TL
ZX.ZX_SIM_RULE_CONDITIONS
ZX.ZX_SIM_TRX_DISTS
ZX.ZX_SRVC_SBSCRPTN_EXCLS
ZX.ZX_SRVC_SUBSCRIPTIONS
ZX.ZX_SRVC_TYP_PARAMS
ZX.ZX_STATUS_B
ZX.ZX_STATUS_TL
ZX.ZX_SUBSCRIPTION_DETAILS
ZX.ZX_SUBSCRIPTION_OPTIONS
ZX.ZX_SUMMARY_TAX_LINES_GT
ZX.ZX_TAXES_B
ZX.ZX_TAXES_TL
ZX.ZX_TAX_DIST_ID_GT
ZX.ZX_TAX_PRIORITIES_T
ZX.ZX_TAX_RELATIONS_T
ZX.ZX_TEST_API_GT
ZX.ZX_TRANSACTION
ZX.ZX_TRANSACTIONS_GT
ZX.ZX_TRANSACTION_LINES
ZX.ZX_TRANSACTION_LINES_GT
ZX.ZX_TRX_HEADERS_GT
ZX.ZX_TRX_LINE_APP_REGIMES
ZX.ZX_TRX_PRE_PROC_OPTIONS_GT
ZX.ZX_TRX_TAX_LINK_GT
ZX.ZX_UPDATE_CRITERIA_RESULTS
ZX.ZX_VALDN_STATUSES_GT
ZX.ZX_VALIDATION_ERRORS_GT
30173 rows selected.
Of 35K tables, over 30K had no primary key!
This totally blew me away!!
------------------------------------------------
------------------------------------------------
Count of tables with no PK, by owner:
Select tables.owner
, count(*)
From all_tables tables,
(Select
owner,
TABLE_NAME,
constraint_type,
CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
) constr
Where tables.owner = constr.owner (+)
And tables.table_name = constr.table_name (+)
and tables.owner <> 'SYS'
and tables.owner <> 'SYSTEM'
And constr.owner IS NULL
And constr.table_name IS NULL
group by tables.owner
order by 2
OWNER COUNT(*)
------------------------------ ----------
TSMSYS 1
MWA 1
HCC 1
DISCOVERER5 1
WPS 1
INTERNET_APPSERVER_REGISTRY 1
WCRSYS 1
SSP 1
CTXTEST 2
ASP 2
ASF 2
WK_TEST 3
EDWEUL_US 3
OUTLN 3
HERMAN 3
EUL_US 3
ADS10GEUL_US 3
DEMO 4
OCA 4
XNB 4
SCOTT 4
DCM 4
…
AR 633
BIS 703
PA 718
APPLSYS 753
BSC 986
APPS 1391
IGS 1630
FEM 1695
GL 2124
224 rows selected.
The important schemas, APPS, and GL have thousands of tables with no PK.
----------------------------------------------------
----------------------------------------------------
For each schema, the ratio of tables with no PK
select
alltab.owner
, nopk.noPK_count
, alltab.the_count
, (nopk.noPK_count / alltab.the_count) * 100
as nopkratio
from
(
select owner,
count(*) as the_count
From dba_tables
group by owner
) alltab
,
(
Select tables.owner
, count(*) as noPK_count
From dba_tables tables,
(Select
owner,
TABLE_NAME,
constraint_type,
CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
) constr
Where tables.owner = constr.owner (+)
And tables.table_name = constr.table_name (+)
and tables.owner <> 'SYS'
and tables.owner <> 'SYSTEM'
And constr.owner IS NULL
And constr.table_name IS NULL
group by tables.owner
) nopk
where alltab.owner = nopk.owner
order by 1
OWNER NOPK_COUNT THE_COUNT NOPKRATIO
------------------------------ ---------- ---------- ----------
ADS10GEUL_US 3 48 6.25
AHL 163 164 99.3902439
AK 47 59 79.6610169
AMS 371 375 98.9333333
AMV 32 42 76.1904762
AMW 176 176 100
AOLDEMO 6 6 100
AP 327 327 100
APPLSYS 753 937 80.3628602
APPS 1391 1492 93.230563 <-
AR 633 686 92.2740525 <-
ASF 2 2 100
ASG 49 60 81.6666667
ASL 21 24 87.5
ASO 63 69 91.3043478
ASP 2 2 100
AST 11 11 100
…
FV 171 171 100
GCS 84 84 100
GL 2124 2128 99.8120301 <-
GMA 45 47 95.7446809
…
HERMAN 3 3 100
HR 586 1300 45.0769231
HRI 326 326 100
…
WK_TEST 3 13 23.0769231
WMS 132 133 99.2481203
WPS 1 1 100
WSH 128 128 100
WSM 53 53 100
XDB 17 22 77.2727273
XDO 28 28 100
XDP 73 78 93.5897436
XLA 213 216 98.6111111
XLE 22 22 100
XNB 4 4 100
XNP 41 50 82
XTR 216 216 100
ZPB 200 200 100
ZX 127 127 100
224 rows selected.
So many schemas have no primary keys in any of their tables at all!!!
Over 90% of the tables in the important schemas, APPS, AR, GL, have no primary keys.
------------------------------------------------------------
------------------------------------------------------------
List all the triggers:
Select table_owner || '.' || table_name, trigger_name
from all_triggers
order by 1, 2, 3
...
XTR.XTR_PARTY_INFO XTR_AU_PARTY_INFO_T
XTR.XTR_PORTFOLIOS XTR_AU_PORTFOLIOS_T
XTR.XTR_POSITION_HISTORY XTR_AIUD_POSITION_HIS_T
XTR.XTR_PRODUCT_TYPES XTR_AU_PRODUCT_TYPES_T
XTR.XTR_PRO_PARAM XTR_AU_PRO_PARAM_T
XTR.XTR_RATE_SETS XTR_AU_RATE_SETS_T
XTR.XTR_REVALUATION_DETAILS XTR_AU_REVALUATION_DETAILS_T
XTR.XTR_REVALUATION_RATES XTR_AU_REVALUATION_RATES_T
XTR.XTR_ROLLOVER_TRANSACTIONS XTR_AID_ROLLOVER_TRANS_T
XTR.XTR_ROLLOVER_TRANSACTIONS XTR_AU_ROLLOVER_TRANSACTIONS_T
XTR.XTR_ROLLOVER_TRANSACTIONS XTR_BI_ROLLOVER_TRANSACTIONS_T
XTR.XTR_ROLLOVER_TRANSACTIONS XTR_BU_ROLLOVER_TRANSACTIONS_T
XTR.XTR_STANDING_INSTRUCTIONS XTR_AU_STANDING_INSTRUCTIONS_T
XTR.XTR_TAX_BROKERAGE_RATES XTR_AU_TAX_BROKERAGE_RATES_T
XTR.XTR_TAX_BROKERAGE_SETUP XTR_AU_TAX_BROKERAGE_SETUP_T
6778 rows selected.
------------------------------------------------------------
------------------------------------------------------------
List Triggers by Schema/Owner:
Select owner
, count(*)
from dba_triggers
group by owner
order by 2
OWNER COUNT(*)
------------------------------ ----------
SCOTT 1
FLOWS_FILES 1
INV 1
ODM 1
OE 1
MGDSYS 1
D4OSYS 1
WK_TEST 1
HERMAN 1
RE 2
SYSTEM 2
HR 3
LBACSYS 4
PTG 4
DEMO 6
AR 7
SYS 9
ORABPEL 14
JTF 14
XDB 21
CFD 26
MASTER 27
WKSYS 36
ORASSO 39
MDSYS 47
OLAPSYS 48
MOBILEADMIN 81
WIRELESS 108
FLOWS_010500 159
FLOWS_020000 182
FLOWS_030000 219
B2B 1528
APPS 4183
The important schema, APPS, uses 4183 triggers on its 1492 tables.
2.8 triggers per table, on average.
------------------------------------------------------------
------------------------------------------------------------ Initial Conclusions:
The Oracle Apps R12 sample database, VIS, uses hardly any primary keys.
Many non unique indexes.
And uses a LOT of package and triggers.
Lots of potential for data integrity and tuning issues.
See and do the analysis!
Very nice analysis…keep it up.
I’m amazed more DBAs and developers don’t do this kind of analysis. (!?)