Using the Oracle PL/SQL Profiler Part 3
In this SQL, enter the RUNID from the prior SQL statement. Oracle will place several lines of ‘’ in the UNIT_OWNER column. This information is the overhead that Oracle incurred executing the code, not the code itself. Since I am not interested in this clutter, I coded the SQL to just show me the profiler information of interest to me.
select runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp
from plsql_profiler_units
where unit_owner <> ‘’
and runid = &rpt_runid;
select pu.unit_name, pd.line#, pd.total_occur passes, round(pd.total_time / 1000000000,5) total_time, us.text text
from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
where pd.runid = &rpt_runid
and pd.unit_number = &rpt_unitid
and pd.runid = pu.runid
and pd.unit_number = pu.unit_number
and us.name = pu.unit_name
and us.line = pd.line#
and us.type in (’PACKAGE BODY’,'PROCEDURE’,'FUNCTION’);
This code cleans up the profiler tables.
delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;
TOAD Users
Quest Software has implemented the PL/SQL Profiler into the TOAD tool. This option has been available for quite a while. It too is easy to use and the whole process is easily handled from the TOAD environment.
Starting and stopping the profiler is easily accomplished by clicking on the Toggle PL/SQL Profiler button . If this button is grayed out, then the TOAD Server Side objects need to be executed (Database Administrate TOAD Server Side Objects Wizard).
When the PL/SQL code is executed, a dialog box will popup for the start comment.
Click the Toggle PL/SQL Profiler button again to stop profiling.
The same profile tables are populated but TOAD also formats this same outout using a nice interactive wizard. Use Database Optimize Profiler Analysis menu item to access the Profiler Analysis.
The output is easily visible. Select your executed code from the list and click on the arrow button in the circle.
Summary
The PL/SQL Profiler is an essential tool when tuning PL/SQL and the SQL coded into these same routines. Without something like this profiler process, it is impossible to tell where the time is spend when tuning PL/SQL code.
Dan Hotka - Author/Instructor/Expert