In tip#45, I introduced dbms_profiler which is a handy tool to tune PL/SQL code.In this post, steps to use this code is briely mentioned.
Here, I try to explain some practical notes when I use this profiler mostly for a client to tune code.
1. The following SQL statement shows the elapsed time of each piece of code and in addition to line number, also SQL text is shown which make finding bottleneck easier.
select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
(select u.unit_name, d.TOTAL_OCCUR occured,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= &runid) p,
where p.unit_name = s.name(+) and p.line# = s.line (+)
order by p.unit_name, p.line#;
2. Anonymous block in result are parts of code which are not owned by the user who ran dbms_profiler.It is better to run profiler as user who owns PL/SQL code.