Profiling PLSQL Code


Profiling PL/SQL Code

Starting with version 8i, Oracle can track statistics on the execution of SQL, including procedures and functions. TOAD interfaces with the DBMS_PROFILE package, giving you an easy way to track and compare your program statistics.

If the Stop Watch button on the top toolbar, or the PL/SQL Profiling and Profiler Analysis buttons (on the Database menu) are greyed out, run the script < Oracle Home >/RDBMS/admin/profload.sql as SYS. You will also then need to run the TOAD script TOADProfiler.sql found in the < TOAD Home >/Temps directory. Be sure to exit and restart TOAD.

The Stop Watch button on the top toolbar is a toggle. Turn it on and the Profiler will ask you for a run name after the procedure has completed execution, as illustrated in Figure 4.60.

Figure 4.60. Oracle Profiler Collection.

When you want to see how your code executions compare, select Profiler Analysis from the Database menu (see Figure 4.61), and it will display all kinds of useful information about your code execution as seen in Figure 4.62. Double-clicking on any of the functions in the lower panel will further break down the timings. You can easily see what part of your code is taking the longest to execute, for example. You can easily filter out particular Unit Types and you can change the graphic to a bar chart.

Figure 4.61. Accessing profile analysis.

Figure 4.62. Profile analysis.

If you are debugging a package or procedure that appears to have performance issues, use the PL/SQL profile to help indicate exactly which called procedure or function has the performance issue.



TOAD Handbook
TOAD Handbook (2nd Edition)
ISBN: 0321649109
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net