DBMS_PROFILER

 

DBMS_PROFILER package, introduced in Oracle8i, provides a means to collect profiling data of plsql program units for performance improvement.

To use dbms_profiler package, we need to create the database tables which will store the profiler data. These tables can be created by running proftab.sql script which will be present in  /rdbms/admin folder in the oracle installation folder.

This script creates:

1. A sequence plsql_profiler_runnumber
2. table plsql_profiler_data
3. plsql_profiler_units
4. plsql_profiler_runs

Lets do the initial setup:

 

   1:  CONN SYS/PASSWORD AS SYSDBA 

   2:  @/home/oracle/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/proftab.sql 

   3:  CREATE PUBLIC SYNONYM plsql_profiler_runs FOR plsql_profiler_runs; 

   4:  CREATE PUBLIC SYNONYM plsql_profiler_units FOR plsql_profiler_units; 

   5:  CREATE PUBLIC SYNONYM plsql_profiler_data FOR plsql_profiler_data; 

   6:  CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR plsql_profiler_runnumber; 

   7:  GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC; 

   8:  GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC; 

   9:  GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC; 

  10:  GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC; 

The sets involved in profiling a plsql unit using dbms_profiling is:

1. Start the profiling session using dbms_profiler.start_profiling
   
2. Run the plsql unit to be profiled
   
3. Stop the profiling session
   
4. After this query the profiler tables we created earlier to check for which subprograms or line took maximum time to execute. Analyze why it took this long and modify as appropriate.

An Example:

   1:  create or replace procedure annual_salary 

   2:  as 

   3:  l_annual_salary NUMBER; 

   4:  begin 

   5:  for i in ( select employee_id, salary from employees) 

   6:  loop 

   7:  l_annual_salary := i.salary * 12; 

   8:  end loop; 

   9:  end annual_salary; 

  10:   /

  11:  DECLARE 

  12:    l_result  BINARY_INTEGER; 

  13:  BEGIN 

  14:    l_result := DBMS_PROFILER.start_profiler(run_comment => 'annual_salary' ); 

  15:    annual_salary; 

  16:    l_result := DBMS_PROFILER.stop_profiler; 

  17:  END; 

  18:  / 

Run this query 

SELECT runid,  run_date,  run_comment,  run_total_time 

FROM   plsql_profiler_runs 

where run_comment = 'annual_salary' 

ORDER BY runid;

This gave below output:

RUNID RUN_DATE  RUN_COMMENT                                        RUN_TOTAL_TIME
------ --------- -------------------------------------------------- --------------
     4 02-SEP-12 annual_salary                                           50000000

To analyze which subprogram or line in the plsql unit profiled took how much time : 

SELECT u.runid, u.unit_number, u.unit_type, u.unit_owner, u.unit_name, d.line#, d.total_occur,

d.total_time, d.min_time, d.max_time 

FROM plsql_profiler_units u JOIN plsql_profiler_data d ON u.runid = d.runid 

AND u.unit_number = d.unit_number

WHERE u.runid = 4 ORDER BY u.unit_number, d.line#; 

This produces below output:

RUNID UNIT_NUMBER UNIT_TYPE            UNIT_OWNER     UNIT_NAME            LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
------ ----------- -------------------- -------------- --------------- ---------- ----------- ---------- ---------- ----------
     4           1 ANONYMOUS BLOCK      <anonymous>    <anonymous>              1           0          0          0          0
     4           1 ANONYMOUS BLOCK      <anonymous>    <anonymous>              3           0          0          0          0
     4           1 ANONYMOUS BLOCK      <anonymous>    <anonymous>              4           0     233173     233173     233173
     4           1 ANONYMOUS BLOCK      <anonymous>    <anonymous>              5           2      82399      10519      71880
     4           1 ANONYMOUS BLOCK      <anonymous>    <anonymous>              6           1      14025      14025      14025
     4           1 ANONYMOUS BLOCK      <anonymous>    <anonymous>              7           0          0          0          0
     4           2 PROCEDURE            HR             ANNUAL_SALARY            1           1      29804       8765      21038
     4           2 PROCEDURE            HR             ANNUAL_SALARY            4           1          0          0          0
     4           2 PROCEDURE            HR             ANNUAL_SALARY            6         109   16819193       7012     675853
     4           2 PROCEDURE            HR             ANNUAL_SALARY            8         107    1495468       8765     120969
     4           2 PROCEDURE            HR             ANNUAL_SALARY            9           1          0          0          0

RUNID UNIT_NUMBER UNIT_TYPE            UNIT_OWNER     UNIT_NAME            LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
------ ----------- -------------------- -------------- --------------- ---------- ----------- ---------- ---------- ----------
     4           2 PROCEDURE            HR             ANNUAL_SALARY           10           1      15778      15778      15778
12 rows selected.

The lines 6 and 8 has taken maximun time.  

Using dbms_profiler, one can tune their plsql units iteratively.

Regards,

Vijay