Tuning the Package

Recently I tuned the peformance of a oracle pl/sql package.

The description of the issue:

1. This package is taking about 17 hours to complete.
2. This packages is having approximately 3000+ lines of code.
3. The run time varies, based on the arguements passed.
4. Most of the executions take 17 hours +/- 1hr.

This document mainly focuses on how to identify the sqls(inside a procedure/package) that are consuming lots of time, while tuning packages/procedures with thousands of lines of code. A detailed discussion on sql query performance tuning methods and approach is beyond the scope of this document. My best reference to deal with performance tuning issues is "Expert One-on-One Oracle: Thomas Kyte".

Tried some tricks and added hints on some of the sqls, tested the package. Not much reduction in elapsed time, though the overall run time decrease by few hours.

My strategy:

alter session set tracefile_identifier='May14_2018'; 
 alter session set tracefile_identifier='May31_2018';

You may enable the level of tracing as may be required in your case/environment.

ls -1tr *May14_2018.trc |while read FILE; do tkprof $FILE $FILE.log sys=no waits=yes explain=apps/Ram05141 sort=fchela;done;
 ls -1tr *May31_2018.trc |while read FILE; do tkprof $FILE $FILE.log sys=no waits=yes explain=apps/Babu0531 sort=fchela;done;

Pls change options for generating tkprof on the trace files, with options of your choice.

This is the output, from one of the tkprof output files.
In the tkprof output, each and every query will be analyzed and its’ elapsed times break-up is given.

grep total <tkprof.out file name> #produced the following output:(one tkprof output file contains this info)
 total 45289 31344.54 31371.81 61413 1661777096 0 15096
 total 30207 3.09 6.53 5136 429320 0 15103
 total 30207 4.46 5.02 328 95402 0 15097
 total 30193 3.81 4.24 193 412099 0 15096
 total 30201 0.91 1.10 908 50046 0 15098
 total 15105 0.22 0.32 1252 1260 0 15103
 total 30195 1.50 1.33 3 45343 0 15097
 total 30195 0.73 0.73 0 50039 0 15097
 total 30193 0.20 0.25 0 0 0 15096
 total 4 0.00 0.00 1 4 0 1
 total 5 0.00 0.00 3 6 0 2
 total 5 0.00 0.00 1 6 0 2
 total 3 0.00 0.00 0 5 0 0
 total 3 0.00 0.00 0 0 0 1
 total 4 0.00 0.00 0 2 0 1
 total 2 0.00 0.00 0 0 0 0
 total 2 0.00 0.00 0 0 0 1
 total 1 0.00 0.00 0 0 0 0
 total 105702 49.09 47.04 5457 54204 1280260 105701
 total 105702 9.07 9.48 0 35 105765 0
 total 15104 2.58 2.86 17 413057 61827 15097
 total 15104 2.94 3.61 831 367945 153964 15097
 total 15097 6.38 5.91 193 1565 157473 15096
 total 15097 3.28 2.93 96 980 63089 15096
 total 15097 1132.45 1134.69 435 35914058 36670 35595
 total 5 0.00 0.00 0 0 0 1
 total 774922 32594.00 32625.22 78535 1699738953 2526743 447155

I found the offending sql that is taking 31371.81 elapsed seconds.
90% of the package’s run time was consumed by this sql.

5. A a major portion of my "searching for the needle in a hay stack" is completed.

As it violates the sensitive/proprietory data laws, I can not share the query's details here. A somewhat similar partial example:

select ...
from t1 where 
..
..
and col1 = (select max(col1) from t2 
 where t1.col2 = t2.col2 and t1.col3 = t2.col3 and t1.col4=t2.col4)
and ...
...

This sql, is used inside a cursor loop in the package. Table t2 is having approximately 300 million rows. I changed the query as follows:

with t2 as (select col2,col3,col4,max(col1) max_col1 
 from t2 group by col2,col3,col4)
select ....
from t1,t2
where 
...
...
and (t1.col1=t2.max_col1 and t1.col2=t2.col2 and 
 t1.col3=t2.col3 and t1.col4=t2.col4)
and ..
..
..

I also used index hints, parallel hints in the query to reduce the run time. Re-written 80% of the query, changing the joins and sub-selects. The package completed under 35 mts, the business is happy. There is further scope to tune the package. But it requires more time to hunt the inefficiently written sqls all over the package and tune them for optimal performance. with heavy code changes, it will be a tough task to re-compile the package and test thouroughly, before porting it to production.

My favourite quote:
“If you want me to give you a two-hour presentation, I am ready today. If you want only a five-minute speech, it will take me two weeks to prepare.”
–Mark Twain

PS: Tracing will help in identifying the offending sql queries, being executed. In the event, all the sql queries inside my package/procedure are equqlly taking same amount of time and performing equally bad, then it is a long haul, good luck. Luckily, in the above package all the dml operations are performing optimally without any contention.

Happy tuning.

Reference:
http://www.toadworld.com/platforms/oracle/w/wiki/11064.tuning-view-s-performance.aspx 
http://www.databasejournal.com/features/oracle/article.php/3700201