Oracle DBMS_PROFILER
This is a basic setup of DBMS_PROFILER for analysis of PL/SQL execution. The example shows the performance of Oracle PL/SQL LOOP versus Bulk Collect (PL/SQL bulk collect greater than 13 times faster than the PL/SQL loop).

Clean Up Prior Data
DELETE FROM plsql_profiler_data;
DELETE FROM plsql_profiler_units;
DELETE FROM plsql_profiler_runs;
COMMIT;

DBMS_PROFILER Setup
DECLARE
 o_run_num  BINARY_INTEGER;
 TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 v_loop     t_tab;
 v_bulk     t_tab;

 PROCEDURE p_loop IS
 BEGIN
    FOR idx IN 1..1000 LOOP
      SELECT idx INTO v_loop(idx) FROM dual;
    END LOOP;
 END p_loop;

 PROCEDURE p_bulk IS
 BEGIN
    SELECT level BULK COLLECT INTO v_bulk
    FROM  DUAL CONNECT BY level < 1001;  
 END p_bulk;

BEGIN
  dbms_profiler.start_profiler(run_comment => 'P_Loop',
                               run_number => o_run_num);
  p_loop;
  dbms_profiler.stop_profiler;
  dbms_profiler.rollup_run(run_number => o_run_num);
  DBMS_OUTPUT.PUT_LINE('P_Loop RunID = '||o_run_num);

  dbms_profiler.start_profiler(run_comment => 'P_Bulk',
                               run_number => o_run_num);
  p_bulk;
  dbms_profiler.stop_profiler;
  dbms_profiler.rollup_run(run_number => o_run_num);
  DBMS_OUTPUT.PUT_LINE('P_Bulk RunID = '||o_run_num);
END;
/
DBMS_PROFILER exec

Analysis of PL/SQL - Loop Vs Bulk Collect
SELECT  Runid, Run_Comment, Run_Total_Time,
  To_Char(Run_Date,'mm/dd/yyyy hh24:mi:ss') Run_Date
FROM  Plsql_Profiler_Runs
ORDER BY Runid;

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

  1433