Recursive Query Performance

We have a Oracle Apps Database hosted on oracle 9i (9.2.0.4). Our Apps Instance database has been upgraded from version 10.7 of Oracle Apps to currently latest 1159 (over 7 version upgrades) and over the years the number of objects currently in the databases is around 320,000.

We are considering removing obsolete objects but for effort needed to do this exercise we were wondering about the benefits. The benefits we see from the exercise being

1. Cleanup of database of the obsolete code, DB is currently around 600 GB
2. Performance Benefits for recursive queries (Need proof)

In this regard, we need to know
1. Does data dictionary size (system tablespace is around 9 GB)impacts the recursive query performance??

2. If it does, then if we drop the obsolete objects (over 7000 packages and bodies, 12000 tables & indexes), will performance of the recursive queries on the Data Dictionary increase ?

3. If recursive query performance improves (margins of 2-5 %) because of the decrease in data dictionary size, will the overall query performance improve significantly?

4. Our SOURCE$, the 2 IDL tables are over 2 GB each. Does this have a performance impact, Will dropping packages and an export/import improve performance.

Additionally we ran traces on few sessions and found some of the recursive queries are using RBO hints and others are not using any RBO hints ( But as statistics are not available on SYS objects, so optimiser must be using RBO for all the recursive queries).

Stats on SYS Schema have not been gathered. We have locally managed tablespaces. Received on Fri Jan 16 2004 - 12:00:48 CST