Donald K. Burleson Oracle Utilities Tips

Using dbms_profiler

Starting a Profiling Session

The profiler does not begin capturing performance information until the call to start_profiler is executed.

SQL> exec dbms_profiler.start_profiler('Test of raise procedure by Scott');

PL/SQL procedure successfully completed.

The profiler captures data on a session-by-session basis. This means that if the user SCOTT started the profiler by executing the command above, only PL/SQL objects that were executed and owned by SCOTT will be profiled, and consequently have data in the profiler tables described earlier. The SCOTT user is only used as an example; it could be any database user.

Flushing Data during a Profiling Session

The flush command enables the developer to dump statistics during program execution without stopping the profiling utility. The only other time Oracle saves data to the underlying tables is when the profiling session is stopped, as shown below:

SQL> exec dbms_profiler.flush_data();

PL/SQL procedure successfully completed.

A developer could use the flush procedure with dbms_debug and step, line by line, through a procedure, flushing performance benchmarks along the way. Or, if you have a very long running PL/SQL program, flushing data can be very useful in the performance tuning process.

Stopping a Profiling Session

Stopping a profiler execution is done after an adequate period of time of gathering performance benchmarks – determined by the developer. Once the developer stops the profiler, all the remaining (unflushed) data is loaded into the profiler tables.

SQL> exec dbms_profiler.stop_profiler();

PL/SQL procedure successfully completed.

The dbms_profiler package also provides procedures that suspend and resume profiling (pause_profiler(), resume_profiler()).

Now that the profiler has stopped, the data is available for diagnostics from within Oracle, and we can begin working with it.

Working with Captured Profiler Data

The profiler utility populates three tables with information, plsql_profiler_runs, plsql_profiler_units, and plsql_profiler_data. Each “run” is initiated by a user and contains zero or more “units”. Each unit contains “data” about its execution – the guts of the performance data benchmarks.

The performance information for a line in a unit needs to be tied back to the line source in user_source. Once that join is made, the developer will have all of the information that they need to optimize, enhance, and tune their application code, as well as the SQL.

To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.