Using Oracle Database 11g Hierarchical Profiler in SQL Developer

Oracle SQL Developer 1.5 exposes a few new Oracle 11g database features. These include the Flashback and the PL/SQL Hierarchical Profiler. For details on either of these feature it's best to dive in the the 11g documentation, available online here. The PL/SQL Hierarchical Profiler..."identifies hot spots and performance tuning opportunities... It reports the dynamic execution program profile organized by subprogram calls..."

I have recorded a brief demo to illustrate using the PL/SQL Hierarchical Profiler in SQL Developer.

Not only do you need Oracle 11g, you also need to have access to a set of tables and a new package called DBMS_HPROF. SQL Developer takes control of setting this up and so you need only acknowledge the steps being taken. If you don't want SQL Developer to create the required profiler tables, review the 11g documentation and ensure you set this up before hand.

Once you have created a profile you can review the detail. The reports provided include details such as:

• Number of calls to the subprogram
• Time spent in the subprogram itself
• All subprograms that a given subprogram called (children)
  

Have fun!