DBMS PROFILER

DBMS_PROFILER is a PL/SQL package, introduced with Oracle 8i, to profile (time) the run-time behaviour of PL/SQL code.

Example

-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload

-- Create a test procedure to time...
CREATE OR REPLACE PROCEDURE proc1 IS
  v_dummy CHAR;
BEGIN
   FOR i IN 1..100 LOOP
      SELECT dummy INTO v_dummy FROM dual;
   END LOOP;
END;
/

-- Do the profilling and print a report...
set line 5000 serveroutput on size 1000000
DECLARE
  v_run NUMBER;
BEGIN
  DBMS_PROFILER.START_PROFILER('test','test1',v_run);
  proc1;
  DBMS_PROFILER.STOP_PROFILER;
  DBMS_PROFILER.ROLLUP_RUN(v_run);
END;
/

-- Look at output data
SELECT runid, run_date, run_comment, run_total_time
  FROM   plsql_profiler_runs
 ORDER BY runid;

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 = 1  -- Change to run_id from the above query
 ORDER BY u.unit_number, d.line#;