Donald K. Burleson Oracle Utilities Tips

Using dbms_profiler

Useful Scripts

To extract high-level data, including the length of a particular run, the script (profiler_runs.sql) below can be executed:

SEE CODE DEPOT

RUNID UNIT_NUMBER OBJECT_NAME TYPE SEC PCT
----- ----------- -------------------- --------------- --------- ------
1 1 <anonymous> .00 .0
1 2 <anonymous> 1.01 .0
1 3 BMC$PKKPKG PACKAGE BODY 6921.55 18.2
1 4 <anonymous> .02 .0
2 1 <anonymous> .00 .0
2 2 <anonymous> .01 .0

6 rows selected.


Note that anonymous PL/SQL blocks are also included in the profiler tables. Anonymous blocks are less useful from a tuning perspective since they cannot be tied back to a source object in user_source. Anonymous PL/SQL blocks are simply runtime source objects and do not have a corresponding dictionary object (package, procedure, function). For this reason, the anonymous blocks should be eliminated from most reports.

From the data displayed above, the next step is to focus on the lines within the package body, testproc, that are taking the longest. The script (profiler_top10_lines.sql) below displays the line numbers and their performance benchmarks of the top 10 worst performing lines of code.


select line#, total_occur,
decode (total_occur,null,0,0,0,total_time/total_occur/1000,0) as avg,
decode(total_time,null,0,total_time/1000) as total_time,
decode(min_time,null,0,min_time/1000) as min,
decode(max_time,null,0,max_time/1000) as max
from plsql_profiler_data
where runid = 1
and unit_number = 3 -- testproc
and rownum < 11 -- only show Top 10
order by total_time desc ;


LINE# TOTAL_OCCUR AVG TOTAL_TIME MIN MAX
---------- ----------- ---------- ---------- ---------- ----------
156 1 5008.457 5008.457 5008.457
27 1 721.879 721.879 721.879
2113 1 282.717 282.717 282.717
89 1 138.565 138.565 138.565
2002 1 112.863 112.863 112.863
1233 1 94.984 94.984 94.984
61 1 94.984 94.984 94.984
866 1 94.984 94.984 94.984
481 1 92.749 92.749 92.749
990 1 90.514 90.514 90.514

10 rows selected.

Taking it one step further, the query below (profiler_line_source.sql) will extract the actual source code for the top 10 worst performing lines.


select line#,
decode (a.total_occur,null,0,0,0,
a.total_time/a.total_occur/1000) as Avg,
substr(c.text,1,20) as Source
from plsql_profiler_data a, plsql_profiler_units b, user_source c
where a.runid = 1
and a.unit_number = 3
and a.runid = b.runid
and a.unit_number = b.unit_number
and b.unit_name = c.name
and a.line# = c.line
and rownum < 11
order by a.total_time desc ;

LINE# AVG SOURCE
---------- ---------- --------------------
156 5008.457 select sum(bytes) into reusable_var from dba_free_space;
27 721.879 execute immediate dml_str USING current_time
2113 282.717 select OBJ#, TYPE# from SYS.OBJ$;
89 138.565 OBJ_TYPES(BOBJ(I)) := BTYP(I);
2002 112.863 select count(*) into reusable_var from dba_objects
1233 94.984 delete from pkk_daily_activity
61 94.984 update_stats_table(33, reusable_var, null);
866 94.984 latest_executions := reusable_var - total_executions;
481 92.749 time_number := hours + round(minutes * 100/60/100,2);
990 90.514 update_stats_table(45, LOBS, null);

10 rows selected.


Notice from the output above that most of the information needed to diagnose and fix PL/SQL performance issues is provided. For lines containing SQL statements, the tuner can optimize the SQL perhaps by adding optimizer hints, eliminating full table scans, etc. Consult Chapter 5 for more details on using tkprof utility to diagnose SQL issues.

Other useful scripts that are hidden within the Oracle directory structure ($ORACLE_HOME/PLSQL/DEMO) include a few gems that help report and analyze profiler information.

• profdemo.sql -A demo script for collecting PL/SQL profiler data.

• profsum.sql - A collection of useful SQL scripts that are executed against profiler tables.

• profrep.sql – Creates views and a package (unwrapped) that populates the views based on the three underlying profiler tables
 

To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.