How can I easily analyze an Oracle package's execution for performance issues?

Question

I have a pl/sql package in an 11g R2 DB that has a fair number of related procedures & functions. I execute a top level function and it does a whole lot of stuff. It is currently processing < 10 rows per second. I would like to improve the performance, but I am not sure which of the routines to focus on.

This seems like a good job for Oracle's "PL/SQL hierarchical profiler (DBMS_HPROF)" Being the lazy person that I am I was hoping that either SQLDeveloper v3.2.20.09 or Enterprise Manager would be able do what I want with a point & click interface. I cannot find this.

Is there an "easy" way to analyze the actual PL/SQL code in a procedure/package?

I have optimized the queries in the package using the "Top Activity" section of Enterprise Manager, looking at all of the queries that were taking a long time. Now all I have is a single "Execute PL/SQL" showing up, and I need to break that down into at least the procedures & functions that are called, and what percentage of the time they are taking.

Answer 1

The PL/SQL Hierarchical Profiler, documented here, is actually very easy to run, assuming that you have the necessary privileges.

Basically you just need to run a PL/SQL block like this:

begin
   dbms_hprof.start_profiling('PLSHPROF_DIR', 'test.trc');
   your_top_level_procedure;
   dbms_hprof.stop_profiling;
end;

The plshprof utility will generate HTML reports from the raw profiler output file (test.trc in the example above).