Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   profiler.sql
rem Purpose:    Profile PL/SQL code to get run-time statistics.
rem             Shows execution time for each PL/SQL line. This data can
rem             be used to improve performance.
rem Date:       02-Mar-2004
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn / as sysdba

-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload
@?/plsql/demo/profrep.sql

-- 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;
/
SHOW ERRORS

-- Do the profilling and print the 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);
  PROF_REPORT_UTILITIES.PRINT_RUN(v_run);
END;
/

Profile PL/SQL code to get run-time statistics