Using the Oracle PL/SQL Profiler Part 2
Understanding the Profiler Process
Profiling is initiated with the START_PROFILER program. Start this process then execute the PL/SQL routine to be profiled. When execution is done, run the STOP_PROFILER program. This will stop the profiling process and write the collected information to the profiler tables.
There are two more routines that control the collection of profiler data: PAUSE_PROFILER and RESUME_PROFILER. These routines might be useful if only certain statistics are of interest from a rather large PL/SQL program, or perhaps called subroutines are not desired to be profiled. These routines are typically imbedded in the PL/SQL code.
The FLUSH_DATA routine can also be called to periodically write the collected information to the profiler tables. This might be useful if the STOP_PROFILER routine is taking an excessive amount of time when profiling larger PL/SQL routines. This routine is typically embedded in the PL/SQL code.
When the START_PROFILER routine is started, the Oracle RDBMS collects a variety of information about the PL/SQL routine while it is being executed. The STOP_PROFILER then stops this collection process and writes the collected information to the three profiler tables.
These tables are then examined using SQL to view the results of the profiler collection.
Using the PL/SQL Profiler
There will be additional profiler information collected if the object being profiled has been compiled using the debug option.
This example will use this simple LOOPING_EXAMPLE code:
CREATE OR REPLACE PROCEDURE looping_example
IS
loop_counter NUMBER := 0;
BEGIN
FOR rec IN (SELECT *
FROM emp)
LOOP
loop_counter := loop_counter + 1;
DBMS_OUTPUT.put_line (
‘Record ‘ || loop_counter || ‘ is Employee ‘ || rec.ename
);
END LOOP;
DBMS_OUTPUT.put_line (’Procedure Looping Example is done’);
END;
To capture PL/SQL profile information, execute the following statements. The comment submitted with the START command will be populated into the RUN_COMMENT in the PLSQL_PROFILER_RUNS table, see below.
SQL> execute DBMS_PROFILER.START_PROFILER(‘User0 Looping_Example’);
SQL>
SQL> execute LOOPING_EXAMPLE:
SQL>
SQL> execute DBMS_PROFILER.STOP_PROFILER;
This example code and the PROFILER_RPT.sql SQL*Plus script (runs all 3 SQL statements in an interactive script) are available from www.DanHotka.com (downloads). You can also me for the PROFILER_RPT.sql script.
The profiler populates three tables with related information. PLSQL_PROFILER_RUNS has information about each time the profiler is started, including the comment entered when the profiler session was initiated. The PLSQL_PROFILE_UNITS contains information about the PL/SQL code executed during the run. Each procedure, function, and package will have its own line in this table. The PLSQL_PROFILE_DATA contains the executed lines of code, code execution time, and more. The following SQL is useful in extracting the profiler information.
First, find the profiler run of interest. The RUN_COMMENT column has the
select runid, run_owner, run_date, run_comment
from plsql_profiler_runs;
Please see part 3 for the last installment.