My favourite feature in Toad has always been the PL/SQL Profiler. I love the way it can profile your code executions is integrated with the editor. It is such an easy and visual feature to use.
So if you have Foglight for Oracle Performance Investigator make sure you also have a copy of Toad for Oracle to dig down and understand where the resource consuming parts of your code are.
Take this example:
I've got a stored procedure with a very high average SQL response time.
If I drill down the dimensions I can see the SQL statements making up the time:
I could also pull up the execution plan of the problematic SQL:
But what isn't clear here is what parameters are being passed to the procedure and what the code coverage looks like. Enter THE PROFILER:
Switch on the feature highlighted in yellow. If you haven't used this feature before there is a set-up wizard.
Execute the procedure and you will be prompted to enter the parameters.
There is a profiler tab here if you want to change the filename or add comments, but what I'm mainly interested in are the parameters I'm passing. I want to see how each line of code performs depending on what I pass in, perhaps the threshold parameter has a significant effect on performance.
Click ok and the procedure will execute, repeat with different parameters and once you have done enough testing open Profiler Analysis:
I can see all the runs and the corresponding execution times, some as low as 7 secs, others upto 91 secs. You can expand each run to see the lines of code:
So even though I have three select statements, line 28 is where all the time is being taken. We can open the procedure in the editor and access the profiler tab to see cover coverage as well:
I've chosen a simple example, but imagine a large procedure with lots of PL/SQL Control Structures and you can really see the time saving value of this feature.
Use this link to see a great video on PL/SQL Profiler
