07 May 2008

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!


DomBrooks said...

Thanks for the article - very apposite given that I was talking about hprof at work yesterday.

The only thing I would say is that the SQL Developer hooks into the hierarchical profiler don't seem to work with public synonyms, i.e I like a single schema owning the hprof tables & sequence and the SQL Developer execution profiles & profile button don't seem to work unless each schema has it's own private synonyms to the tables, whereas via a sql command line the public synonyms work ok.

It's really nice to see SQL Developer heading in the right direction and quickly.

Really like the hooks into the profiler, the debugger and the refactoring.

It's becoming a very serious option as an IDE and one that doesn't crash every day unlike some others.

Sue said...

Thanks for the feedback Dom.

In terms of your comment, I've passed it on to the developer.


Ofir Manor said...

Hi Sue,
this is a great demo! thanks.
Could you blog a bit about the CVS integration? I think it would be great to have a step by step example of setup and usage, like this one.

Ben Prusinski said...

Hi Sue,

Thank you for sharing this wonderful tip on the PL/SQL hierarchical profiler in SQL Developer. Each day I learn something new and fantastic in the tool!

Ben Prusinski

Gary Myers said...

Viewlet has been relocated to