RDBMS / EBS 12.2 -- too many records in OBJ$ & other dictionary objects

In one of my ACS (Advanced Custom Support) site visits; I have dealt with a performance issue. The system was an EBS 12.2 running on multiple Sparc (&Solaris) nodes , and the performance problem was faced in the database management and developer related tasks that were done using GUI tools like TOAD.

Developers were complaining due to the slow response of Toad Code Road Map, and they named the problem as "Toad Code Road Map performance issue".. However; when I analyzed it, I saw that it was a general issue related with the internal data objects, especially the OBJ$.

Following SQL (which was executed by TOAD) alone was a good representation of the problem;

SELECT DISTINCT OWNER SCHEMA1, REFERENCED_OWNER SCHEMA2 FROM SYS.DBA_DEPENDENCIES UNION SELECT DISTINCT OWNER SCHEMA1, R_OWNER SCHEMA2 FROM SYS.DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R'

So, I started my analysis by focusing on this SQL and first tried to improve its performance without touching any data.

Tried to following;

• Checked the execution plan, saw the Full table scans there; checked all the execution plans(including the historical ones) and tried to find a better one. No luck. 
• Executed SQL Advisor for that SQL. SQL Advisor recommended a SQL Profile, but again no luck, no gains.
• Tried with parallel hint (and several other hints aligned with the context).. 4 parallel, 8 parallel, 16 parallel.. Just didn't improve the performance.
• Considered creating a Materialized View and making use of Query Rewrite capability of Oracle, but again no luck due to the related tables being internal objects.. (can't use query rewrite with the internal ones)

After all these, continued my analysis with the data and concluded that the solution was there in the data. I checked the data in OBJ$, and found several similar objects that belong to different database editions.. (More than 150000 objects multiplexed 13 times) 

Remember this was an EBS 12.2, and we had online patching and that's why we had Edition Based Redefinition (EBR) there.

It seemed that excessive patching was done in this environment and no full cleanup (& no actualize all) was executed since the beginning of those patching activities. It was a big deal, because the multiplexed objects were the results of that.

Well.. Finishing my post with the action plan that I provided to customer;

Run the following;

adop phase=prepare

adop phase=actualize_all

adop phase=finalize finalize_mode=full

adop phase=cutover

adop phase=cleanup cleanup_mode=full

And then gather the dictionary and fixed object stats. (cause the number of the rows in the related object will change)

exec dbms_stats.create_stat_table('APPS','FIXED_STATS_BCK','USERS_TBS');

exec dbms_stats.export_fixed_objects_stats(stattab=>'FIXED_STATS_BCK',statown=>'APPS');

exec dbms_stats.create_stat_table('APPS','DICT_STATS_BCK','USERS_TBS');

exec dbms_stats.EXPORT_DICTIONARY_STATS(stattab=>'DICT_STATS_BCK',statown=>'APPS');

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

That's all for today. I hope you will find it useful.