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)
- adop phase=prepare
- adop phase=actualize_all
- adop phase=finalize finalize_mode=full
- adop phase=cutover
- adop phase=cleanup cleanup_mode=full
- 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;
Kaynak; Erman Arslan Blog