Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

New profiler.sql script to nicely report top time consumers out of PL/SQL Profiler (DBMS_PROFILER) data

leave a comment »

PL/SQL Profiler has been around for more than a decade. With DBMS_PROFILER we can “profile” the execution of PL/SQL code. This is great when the poor performance happens in PL/SQL code and not necessarily due to SQL statements from those PL/SQL libraries. In such cases Trace/TKPROF are not the right tools for the job since they do not include PL/SQL commands.

Once we use PL/SQL Profiler in our application, we get as output some data in 3 Tables. This “profile” data is populated by the PL/SQL engine at the end of the “profile” run, or as we flush this “profile” data from memory.

If you are using PL/SQL Profiler and you want to produce a nice and concise HTML report that shows which are the top 5 code lines in terms of their execution time, then you may want to use the new version of the profiler.sql script out of MOS Doc ID: 243755.1. This document has been around for a decade or so, but I just re-wrote the profiler.sql script. This new and very improved version is available as of today.

Also, within the same document (note), you can find an output sample. This sample would give you some idea how it would look if used with your application libraries.

Written by Carlos Sierra

May 10, 2012 at 4:27 pm

Posted in PL/SQL

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 937 other followers

%d bloggers like this: