Oracle PL/SQL engine provides us with pretty cool tools for profiling our code. There are two profilers (that I know of): DBMS_PROFILER и DBMS_HPROF. H is for Hierarchical here, while DBMS_PROFILER is ‘flat’ (does not consider function stack).

I’m gonna cover the DBMS_PROFILER but working with the DBMS_HPROF is pretty much the same.

Environment

Setting up the environment is easy. Run the built-in script from $ORACLE_HOME/rdbms/admin/proftab.sql. It’s gonna create a few tables and a sequence.

The profiler will populate them so let’s take a look at what they are.

PLSQL_PROFILER_RUNS

Every time you start a profiler it creates a ‘run’. Every run has an id, and it’s basically a scope for all the data the profiler is gathering.

Thanks to this you can keep your previous metrics when running the profiler again after you’ve made an edit and see what changed between runs. You can even give meaningful names to runs. Cool, huh?

PLSQL_PROFILER_UNITS

Every PL/SQL unit that was called during a run will show up here. That can be a package, a function/procedure or an anonymous block.

PLSQL_PROFILER_DATA

This is the meat of our profiling session: all the metrics go here. How many times was a unit called, how much time it took, etc.

Profiling

You can only profile your own session, so you need to be able to run the code you’re going to profile. To start profiling just wrap your code in this.

BEGIN
DBMS_PROFILER.START_PROFILER;

... your code ...

DBMS_PROFILER.STOP_PROFILER;

END;

START_PROFILER accepts an optional argument that you can use to identify a specific run later to see how your results change from run to run.

Interpreting results

Some SQL IDEs come with built-in tools to view the profiling results (ahem, PL/SQL Developer, ahem). But if you don’t use those (god I hope not, it’s 2014). Here’s a helpful query that you can adapt to your task.

SELECT U.UNIT_OWNER||'.'||UNIT_NAME                       UNIT,            -- PL/SQL block
       D.LINE#,                                                            -- Line no
       D.TOTAL_OCCUR,                                                      -- How many times?
       D.TOTAL_TIME,                                                       -- Total time
       ROUND(D.TOTAL_TIME/D.TOTAL_OCCUR,2)                AVG_TIME,        -- Average time
       ROUND(D.TOTAL_TIME/R.RUN_TOTAL_TIME,2)*100 ||'%'   PART_IN_OVERALL, -- Percentage of total time
       D.MAX_TIME,                                                         -- Max time
       D.MIN_TIME,                                                         -- Min time
       U.UNIT_TYPE,                                                        -- Unit type
       R.RUNID,                                                            -- ID of a run
       U.UNIT_NUMBER                                                       -- ID of a unit
FROM   PLSQL_PROFILER_DATA  D, -- Code blocks
       PLSQL_PROFILER_UNITS U, -- Packages and procedures
       PLSQL_PROFILER_RUNS  R  -- Runs
WHERE  U.UNIT_NUMBER = D.UNIT_NUMBER
AND    R.RUNID = D.RUNID
AND    D.TOTAL_OCCUR > 0
AND    D.TOTAL_TIME > 0
ORDER BY D.TOTAL_TIME/D.TOTAL_OCCUR DESC,  -- average execution time of a code block
         D.TOTAL_TIME               DESC   -- total execution time of a code block