How can I easily analyze the execution of an Oracle package for performance problems?

Question

I have a pl / sql package in an 11g R2 database that contains many related procedures and functions. I perform a top level function and it does a lot of things. It currently processes <10 rows per second. I would like to improve performance, but I'm not sure which of the routines to focus on.

This seems like a good job for Oracle “PLI hierarchical PL / SQL profiler” (DBMS_HPROF) “Being the lazy person that I am, I was hoping that SQLDeveloper v3.2.20.09 or Enterprise Manager would be able to do what I want with dot and click interface.I can not find this.

Is there a “simple” way to parse the actual PL / SQL code in a procedure / batch?

I optimized the queries in the package using the "Top Activity" section of Enterprise Manager, considering all the queries that took a lot of time. Now I have the only "Execute PL / SQL" that appears, and I need to break it down, at least into the called procedures and functions and by what percentage of the time they take.

Answer 1

The hierarchical PL / SQL profiler documented here actually runs very easily, assuming you have the necessary privileges.

Basically, you just need to run the PL / SQL block as follows:

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).