I have a PL/SQL block of code – I wonder where most of the time is being spent to execute it? Well guess what? The PL/SQL team has given us an interface to answer this exact question.
The PL/SQL Hierarchical Profiler does 3 things of note:
- Reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls
- Accounts for SQL and PL/SQL execution times separately
- Makes me feel like an idiot whenever I try to pronounce ‘hierarchical’
Did you know: SQL Developer makes using the profiler very easy!
For the profiler to be available, you’ll need :
- a Database Directory to write generated some reports
- a few local (to your schema!) tables
SQL Developer will take care of these pre-requisites for you if you ask for a Profiler run and they’re missing from your system. You’ll just need a high-level priv account to go out and create the DB Directory – be ready to ask a DBA for help in other words.
Ok, so pre-requisites accounted for, and I’m ready to start my profiler run.
When it’s done, I’ll see where the “suck” is, i.e. where I need to go to have the biggest impact on performance when it comes to tuning my SQL or PL/SQL.
Hit this button in the Code Editor – Next to Debug.
Give your profiler run a name.
So it should be obvious by now, but if it’s not – we’re about to EXECUTE this block of code, and the profiler is going to record everything going on in terms of execution time.
Once the profiler run has finished, we can head over to the Profiles panel in the code editor.
We can see that ADD_EMP2() also called something called DO_NOTHING_COMMENTS(), and THAT called something DBMS_LOCK(), which took….5,000,402 μs.
Wait, what’s that? It’s a fancy way of saying that one call took 5 seconds, whereas the entire run took only 5.1 seconds, so something weird was happening there.
We can also see that there were some ‘static sql executions’ as well. Tell me more!
If I click on one of those SQL_ID’s, then we can look at it’s associated plan in v$sql_plan –
Of course, you could also open up our RTSM Report and see if the Run was still in there…but, that requires the Tuning Pack license for your database.
Remember how we started this post with the promise to track SQL and PL/SQL run times separately? That’s what we’ll see on the Namespace tab –
And finally we have the module overview – which is a flattened view of the Call Hierarchy:
This is a somewhat NEW feature.
It was introduced in 11gR1 – you can read about it new New Features Guide.
Remember how I said up above you needed a Database Directory? Guess what’s in there? That’s right, some HTML reports.
One last thing, a bug.
I found after running a profile capture, I had to close the editor and re-open to see the actual reports. I hope to have this fixed in time for version 19.1.