Profiling your PL/SQL code with DBMS_PROFILER

The PL/SQL profiler provides information about PL/SQL code with regard to CPU usage and other resource usage information. When there is a noticeable gap between user elapsed time and SQL processing elapsed time, and there is PL/SQL code involved, the PL/SQL Profilerbecomes a very useful tool. The Profiler helps in identifying the lines of PL/SQL code which are taking longer to process. 

The profiler report consists of 
1) Top Ten profiled source lines in terms of Total Time
2) Total Time taken to process each line of your PL/SQL code.
3) Number of times each line executed in your PL/SQL code. 

Installation Procedure: 
1) Connect as SYS into SQL*Plus, and execute the below command to create the package (DBMS_PROFILER): 

SQL> @ORACLE_HOME/rdbms/admin/profload.sql; 

2) Once DBMS_PROFILER is installed, connect as application user into SQL*Plus, and create the following repository tables:

• PLSQL_PROFILER_RUNS,
• PLSQL_PROFILER_UNITS, and
• PLSQL_PROFILER_DATA.

SQL> @ORACLE_HOME/rdbms/admin/proftab.sql; 

 3) Generating a report 
 The script profiler.sql (available on Metalink, Doc Id: 243755) generates a comprehensive HTML report on the performance data extracted by the DBMS_PROFILER package. 

Useful Procedures in DBMS_PROFILER 
 In Oracle 10g, the DBMS_PROFILER has more than 15 procedures/functions, but we will restrict to the following: .




Using DBMS_Profiler: To profile a PL/SQL Library (package, procedure, function or trigger), include in its body the two calls to actually start, and complete the profiling. Use the example below on any PL/SQL Library to profile. 

BEGIN 
    DBMS_PROFILER.START_PROFILER('any comment to identify this execution'); 
    ... /* Your PL/SQL Code */ ... 
    DBMS_PROFILER.FLUSH_DATA; 
    DBMS_PROFILER.STOP_PROFILER; 
END; 
/ 

Example Usage of DBMS_PROFILER 
Create the following test procedure, execute the procedure and run the prolifer.sql script to produce the report.

create or replace procedure test_proc is 
    u_cnt number; 
    a_cnt number; 
    run_id number; 
    a number; 
    b number; 
begin 
    run_id := dbms_profiler.start_profiler(to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')); 
    dbms_output.put_line(' RUN Id: ' run_id); 
    select count(*) into u_cnt from user_objects; 
    select count(*) into a_cnt from all_objects; 
    for i in 1..10000 loop 
        a := i; 
        b := a; 
    end loop; 
    dbms_output.put_line('User count: ' u_cnt); 
    dbms_output.put_line('All count: ' a_cnt); 
    dbms_profiler.flush_data; 
    dbms_profiler.stop_profiler; 
end; 
/ 

SQL> Exec Test_Proc;
SQL> c:\profiler\profiler.sql

Below is an excerpt from the report for the above procedure: