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

Leave a Reply

You must be logged in to post a comment.


Paxil Alprazolam Buy Zyban Hydrocodone Buy Biaxin Buy Xanax On Line Clonazepam Viagra Online Propecia Meridia Buy Xanax Buy Hydrocodone Online Zyban Buy Tramadol Online Buy Oxycontin Buy Zovirax Viagra Buy Vicodin Xanax Online Phentermine Seroquel Phentermine Online Oxycontin Didrex Buy Percocet Celexa Buy Codeine Bupropion Buy Generic Viagra Flexeril Buy Ambien Zithromax Buy Xanax Butalbital Buy Lipitor Buy Nexium Buy Lortab Buy Glucophage Vicodin Buy Valium Zyban Glucophage Viagra Online Buy Phentermine Lipitor Oxycontin Buy Vicodin Seroquel Flexeril Online Xanax Buy Ativan Cipro Bupropion Buy Alprazolam Buy Zithromax Buy Paxil Clonazepam Zyrtec Buy Ephedrine Percocet Buy Bontril Valium Online Darvocet Acyclovir Buy Bontril Xanax On Line Buy Biaxin Buy Hydrocodone Online Effexor Cipro Hydrocodone Lexapro Buy Adderall Buy Soma Buy Norco Zovirax Buy Paxil Buy Cipro Buy Lexapro Tramadol Online Buy Oxycontin