Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

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

with 5 comments

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

5 Responses

Subscribe to comments with RSS.

  1. Hello Carlos,

    Thaks for sharing that. Could you share the profiler.sql file with us?

    Thanks again.

    Bruno Paulino

    December 28, 2015 at 8:12 am

  2. Hello Carlos,

    Thanks for sharing this. Could you share the profiler.sql file with us?

    Thanks again!

    Bruno Paulino

    December 28, 2015 at 8:14 am

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: