How do you trace performance issues and bottlenecks in your PL/SQL applications?
Since 8i the Oracle database has been equiped with the profiling tool
dbms_profiler. It is quite simple to use: start the profiler, execute the code, stop the profiler. The profiler analyzes the execution of PL/SQL statements and stores the results for later usage. As expected, Oracle does not provide tools to analyze the data. You can do that by querying the three profiling tables yourself and have a hard time interpreting the data. Fortunately other tools like TOAD (and also PL/SQL Developer) can support you with this analysis, e.g. by presenting the data in a nice down-drillable graphic and by displaying the code along with the data. This article gives an excellent introduction.
Figure 1: The TOAD profiler analysis. The top part presents the relative time per line. The bottom part presents the absolute time alongside the code. A pie chart is also available
You can also profile your code from outside TOAD, e.g. with SQL+ or JDeveloper. But when you want to review the results in TOAD you’ll need to add some data to the profiling tables that TOAD requires (like totals and source code). The same article provides all the necessary information (along with pl/sql code) on how to do that.
Running from JDeveloper is also quite easy:
_ Navigate in the DB Connections Navigator to the procedure (or function) you’d like to profile.
_ Run this procedure (with a right click).
_ The ‘run PL/SQL’ window will open:
_ Edit the anonymous block to your liking (add the start and stop profiler methods and the toad specific SQL code).
_ Then really run your code.
_ View the analysis in TOAD (You may have to reopen the profiler analysis window).
Before using, a few preparations are needed:
Install the dbms_profiler package (9i and before; 10g has it installed by default) with public access.
Install the profiler tables (plsql_profiler_runs, plsql_profiler_data, plsql_profiler_units) and provide public access. This can be done easily with the TOAD server-side objects wizard.