In this blog, I want to introduce you to a little known feature in Toad for Oracle called the PL/SQL Profiler. Believe it or not, the profiler has been in Toad since September 1999 (Version 6)!
So why would I want to Profile my Code?
Well, as developers write their code, they’re principally focussed on ensuring the code enables the required application functionality and does so consistently for all test cases. They also need to make sure any errors are properly handled. So they’re focussed on testing and debugging.
What they don’t often have an opportunity to validate, though, and this isn’t generally discovered until after the application goes into production is: does the code unit perform well when it runs and do I have any way of reporting on it? Also, is it likely to scale well in Production?
Imagine a large PL/SQL Package running in a Production database with many Stored Procedures and Functions. If the Package performance is poor, how do you determine (to the line of code) where the bottleneck is? You could probably make an educated guess and look for anything which makes a call to the database (another Package call, SQL statement execution, etc) but you don’t really know. And this problem will be greatly amplified in a Production environment.
This is where Toad’s PL/SQL Profiler can help…
The Oracle Database (8i and above) provides a Probe Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. The collected profiler (performance) data can be used for performance improvement efforts or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.
The profiler API is implemented as a PL/SQL package, DBMS_PROFILER, that provides services for collecting and persistently storing PL/SQL profiler data.
What do I need to do to set up the PL/SQL Profiler?
You first need to run Toad’s Server Side Objects Wizard which is located under the Database – Administer menu group. This will install 3 tables into a schema of your choice then verify that you have access to Oracle’s SYS.DBMS_PROFILER Package. If this Package is not installed, you need to do the following:
1. Login to Oracle through Toad as SYS.
2. Load the Oracle home>\RDBMS\ADMIN\PROFLOAD.SQL script into the Editor.
3. From the SQL Editor menu, select Execute as Script (or press F5).
4. Make sure that GRANT EXECUTE on the DBMS_PROFILE package has been granted to PUBLIC or to the users that will use the profiling feature.
Once this is complete, you’ll find Toad’s PL/SQL Profiler toggle button becomes active. 
How do I use the PL/SQL Profiler?
Improving application performance is an iterative process. Every iteration involves the following:
-
Exercising the application with one or more benchmark tests, with profiler data collection enabled.
-
Analyzing the profiler data, and identifying performance problems.
-
Fixing the problems.
To support this process, the PL/SQL Profiler supports the notion of a run. A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the end of the run by clicking the Toggle PL/SQL Profiling button in the main Toad toolbar.
A typical session involves:
-
Starting profiler data collection in session.
-
Executing PL/SQL code for which profiler/code coverage data is required.
-
Stopping profiler data collection.
Some PL/SQL operations, such as the very first execution of a PL/SQL unit, may involve I/O to catalogue tables to load the byte code for the PL/SQL unit being executed. Also, it may take some time executing package initialization code the first time a package procedure or function is called. To avoid timing this overhead, you should warm up the database before collecting profile data. Warming up involves running the application once without gathering profiler data.
The profiling information is stored in database tables (installed earlier by the Server Side Objects Wizard). This enables the ad-hoc querying on the data. It lets you build customizable reports (summary reports, hottest lines, code coverage data, and so on) and analysis capabilities.
How do I view the collected data and what do I do with it?
There are three levels to this collected data.
Level 1
The top level are the individual "Runs" of each Procedure executed while profiling was enabled.
The bottom half of the window lists the runs, including Run Number, Procedure, Timestamp, Comment, and Total Time to execute. You can sort on the columns by clicking on the column headers.
Double-click one item for the execution stats for that individual run.
Level 2
The top half of the window is a graph showing the percentage of time required to run each component of the Procedure.
In addition, labels on the chart show actual execution time for the Fastest, Slowest, and Average Unit or line of code.
The bottom half shows the breakdown of all the code used during the program unit execution.
Level 3
If you double-click a code unit, you will expose the lines of PL/SQL code showing the Line #, Passes (0 indicates the line did not execute, positive numbers indicate whether the line executed or, in the case of a Loop, the number of iterations made), Total, Min and Max Times (in milliseconds).
Between the top half of this window and the bottom half is the analysis toolbar which lets you alter the graph characteristics, navigate between the levels or open the Editor.
If you are lucky enough to have either Toad for Oracle Xpert or Toad Development Suite for Oracle, you will be able to not only open the PL/SQL code unit in the Editor, but actually copy a bottlenecking SQL statement into the SQL Optimizer and tune it. Then have the re-written statement returned back into the code. The SQL Optimizer will also test the SQL for Production Scalability. Great workflow!!
What about Code Coverage?
If you want to understand code coverage better, in Toad for Oracle 8.6 and above, you can have gutter indicators show you whether a line of code executed or not (Green indicates it did – Red indicates it did not). As you mouse-over a Green indicator, Toad will tell you the execution time and number of executions made.
If you are experiencing PL/SQL code degradation and not sure where to look or what to do, the chances are Toad’s PL/SQL Profiler will give you visibility of the root cause quickly and efficiently to enable you make an informed decision on your next course of action.
Have fun!! (And remember, PL/SQL Profiler comes with Toad Standard Edition)