Oracle Applications R12 Schema Analysis


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!

About these ads

2 Responses to Oracle Applications R12 Schema Analysis

  1. Very nice analysis…keep it up.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: