How to improve or tune oracle scripts performance – Case History and issues from oracle applications?

Oracle performance tuning is an essential part of coding especially when the data set is growing. I had an opportunity to work with a reputed companies, dataset appx 3 TBs, to learn the predictive performance tuning for six months and later became a part of my development initiatives. I am sharing my experience, issues, suggestions how to improve the performance of oracle scripts.

More to add soon

exec DBMS_STATS.gather_index_stats(’CCA’, ‘XXCCA_EC_RELATIONSHIPS_N1′, estimate_percent => 30);

exec DBMS_STATS.gather_index_stats(’CCA’, ‘XXCCA_EC_RELATIONSHIPS_N2′, estimate_percent => 30);

exec DBMS_STATS.gather_index_stats(’CCA’, ‘XXCCA_EC_RELATIONSHIPS_U1′, estimate_percent => 30);

exec DBMS_STATS.gather_index_stats(’CCA’, ‘XXCCA_EC_RELATIONSHIPS_U2′, estimate_percent => 30);

exec DBMS_STATS.gather_index_stats(’APPS’, ‘XXCCA_EXP_DTL_N1′, estimate_percent => 30);

Plan
SELECT STATEMENT CHOOSECost: 61 Bytes: 380 Cardinality: 10
5 SORT ORDER BY Cost: 61 Bytes: 380 Cardinality: 10
4 HASH JOIN Cost: 37 Bytes: 380 Cardinality: 10
2 TABLE ACCESS BY INDEX ROWID APPS.XXCCA_EC_EXPOSURE_DETAIL Cost: 4 Bytes: 18,981 Cardinality: 999
1 INDEX RANGE SCAN NON-UNIQUE APPS.XXCFI_XEED_ATTR1_N2 Cost: 4 Cardinality: 1
3 TABLE ACCESS FULL CCA.XXCCA_EC_RELATIONSHIPS Cost: 32 Bytes: 184,528 Cardinality: 9,712