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