TUNING THE PACKAGE
Author JP Vijaykumar
Written May 14th 2018
Modified May 31st 2018

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

The description of the issue:
01) This package is taking about 17 hours to complete.
02) This packages is having approximately 3000+ lines of code.
03) The run time varies, based on the arguements passed.
04) Most of the executions take 17 hours +/- 1hr.

This document mainly focusses 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:
01) Going line by line code check will take me quite long time.
02) I traced the sessions, while the package was running, few times.
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.
03) On the trace files, ran tkprof.
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.
04) From the tkprof identified the offending queries.
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.

05) 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