Oracle DBMS_PROFILER Built-In Package
Versions 8.1.5 - 19.3

Security Advisory
The DBMS_PROFILER package provides an interface for profiling PL/SQL application code and identifying performance bottlenecks. It can also be used to collect and persistently store profiler data for use in performing comparisons.

From a security standpoint what needs to be considered is whether this package could be used by attacker to gain insights into weaknesses and potential targets which we believe to be the case. The fact that a package that is clearly not intended for end-user use has EXECUTE granted to PUBLIC makes it all the more of a concern.
 
Recommended Security Rules

 NEVER
  • Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC
 WITH GREAT CARE
  • Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
  • Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO
 CAUTIONS
  • Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES
 
DBMS_PROFILER Package Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
Return values from DBMS_PROFILER functions
error_io BINARY_INTEGER 2
error_param BINARY_INTEGER 1
error_version BINARY_INTEGER -1
major_version BINARY_INTEGER 2
minor_version BINARY_INTEGER 0
success BINARY_INTEGER 0
Dependencies
DBMS_PROFILER_LIB    
Documented Yes
Exceptions
Error Code Reason
ORA-06528 profiler_error: Parameter or I/O error. If this error is returned ... run proftab.sql for the user?
ORA-06529 version_mismatch: Incorrect profiler version for database
First Available 8.1.5
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmspbp.sql
Subprograms
FLUSH_DATA RESUME_PROFILER START_PROFILER
GET_VERSION ROLLUP_RUN STOP_PROFILER
INTERNAL_VERSION_CHECK ROLLUP_UNIT Demos
PAUSE_PROFILER    
 
FLUSH_DATA
Flushes the Profiler buffer to the Profiler tables
Overload 1		 dbms_profiler.flush_data RETURN BINARY_INTEGER;
SELECT dbms_profiler.flush_data
FROM dual;

FLUSH_DATA
----------
         0
Overload 2 dbms_profiler.flush_data;
exec dbms_profiler.flush_data;

PL/SQL procedure successfully completed.
 
GET_VERSION
Returns the Profiler API version dbms_profiler.get_version(
major OUT BINARY_INTEGER,
minor OUT BINARY_INTEGER);
set serveroutput on

DECLARE
 majver BINARY_INTEGER;
 minver BINARY_INTEGER;
BEGIN
  dbms_profiler.get_version(majver, minver);
  dbms_output.put_line('Major: ' || TO_CHAR(majver));
  dbms_output.put_line('Minor: ' || TO_CHAR(minver));
END;
/
Major: 2
Minor: 0

PL/SQL procedure successfully completed.
 
INTERNAL_VERSION_CHECK
Returns the Profiler package version for compatibility verification dbms_profiler.internal_version_check RETURN BINARY_INTEGER;
SELECT dbms_profiler.internal_version_check
FROM dual;

INTERNAL_VERSION_CHECK
----------------------
                     0
 
PAUSE_PROFILER
Pauses Profiler data collection

Overload 1		 dbms_profiler.pause_profiler RETURN BINARY_INTEGER;
DECLARE
 i PLS_INTEGER;
BEGIN
  i := dbms_profiler.flush_data;
  i := dbms_profiler.pause_profiler;
  i := dbms_profiler.resume_profiler;
END;
/
Overload 2 dbms_profiler.pause_profiler;
SELECT dbms_profiler.pause_profiler
FROM dual;

PAUSE_PROFILER
--------------
             0
 
RESUME_PROFILER
Restarts Profiler data collection

Overload 1		 dbms_profiler.resume_profiler RETURN BINARY_INTEGER;
See PAUSE_PROFILER Demo Above
Overload 2 dbms_profiler.resume_profiler;
SELECT dbms_profiler.resume_profiler
FROM dual;

RESUME_PROFILER
---------------
              0
 
ROLLUP_RUN
Roll up and calculate the total time usage for all units that have been part of a run dbms_profiler.rollup_run(run_number IN NUMBER);
CREATE OR REPLACE PROCEDURE proc1 AUTHID DEFINER IS
 vd VARCHAR2(5);
BEGIN
  FOR i IN 1..100
  LOOP
    SELECT dummy INTO vd FROM dual;
  END LOOP;
END proc1;
/

-- failure to run proftab.sql for the user
DECLARE
 v_run NUMBER;
BEGIN
  dbms_profiler.start_profiler(SYSDATE, 'test1', v_run);
  proc1;
  dbms_profiler.stop_profiler;
  dbms_profiler.rollup_run(v_run);
END;
/
BEGIN dbms_profiler.start_profiler; END;
*
ERROR at line 1:
ORA-06528: Error executing PL/SQL profiler
ORA-06512: at "SYS.DBMS_PROFILER", line 132
ORA-06512: at "SYS.DBMS_PROFILER", line 141
ORA-06512: at line 1

-- run proftab
@?/rdbms/admin/proftab.sql

-- after running proftab
DECLARE
 v_run NUMBER;
BEGIN
  dbms_profiler.start_profiler(SYSDATE, 'test1', v_run);
  proc1;
  dbms_profiler.stop_profiler;
  dbms_profiler.rollup_run(v_run);
END;
/
PL/SQL procedure successfully completed.
 
ROLLUP_UNIT
Roll up and calculate the total time usage for a specific unit that has been part of a run dbms_profiler.rollup_unit(
run_number IN NUMBER,
unit       IN NUMBER);

-- executes the following code
UPDATE plsql_profiler_units
SET total_time = (
  SELECT SUM(total_time)
  FROM plsql_profiler_data
  WHERE runid = run_number
  AND unit_number = unit);
SELECT *
FROM plsql_profiler_units;

exec dbms_profiler.rollup_unit(8, 3);
 
START_PROFILER
Start Profiler data collection in the current session

Overload 1		 dbms_profiler.start_profiler(
run_comment  IN  VARCHAR2 := SYSDATE,
run_comment1 IN  VARCHAR2 := '',
run_number   OUT BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
Overload 2 dbms_profiler.start_profiler(
run_comment  IN  VARCHAR2 := SYSDATE,
run_comment1 IN  VARCHAR2 := '',
run_number   OUT BINARY_INTEGER);
TBD
Overload 3 dbms_profiler.start_profiler(
run_comment  IN VARCHAR2 := SYSDATE,
run_comment1 IN VARCHAR2 := '')
RETURN BINARY_INTEGER;
TBD
Overload 4 dbms_profiler.start_profiler(
un_comment   IN VARCHAR2 := SYSDATE,
run_comment1 IN VARCHAR2 := '');
See Demo Below
 
STOP_PROFILER
Stop Profiling

Overload 1		 dbms_profiler.stop_profiler RETURN BINARY_INTEGER;
DECLARE
 x PLS_INTEGER;
BEGIN
  x := dbms_profiler.stop_profiler;
  dbms_output.put_line(TO_CHAR(x));
END;
/
Overload 2 dbms_profiler.stop_profiler;
See Demo Below
 
Demo Preparatory Steps
Preparation as SYS conn sys@pdbdev as sysdba

GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create view TO uwclass;

@?\rdbms\admin\profload.sql
Preparation as UWCLASS conn uwclass/uwclass@pdbdev

@?\rdbms\admin\proftab.sql
Procedure To Empty Profiler Tables Between Runs conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE profreset AUTHID DEFINER IS
BEGIN
  DELETE FROM plsql_profiler_data;
  DELETE FROM plsql_profiler_units;
  DELETE FROM plsql_profiler_runs;
  COMMIT;
END profreset;
/
DBMS_Profiler Report. Save in /home/oracle or an equivalent location Download ASCII File
Load Demo File. Save in /home/oracle  or an equivalent location Download ASCII File
 
Demo 1: Preparation of Demo For Testing
Comma To Table Procedure Demo Tables conn uwclass/uwclass@pdbdev

CREATE TABLE sources_import (
sourceno  VARCHAR2(10),
sizeno    VARCHAR2(10),
status    VARCHAR2(10),
latitude  VARCHAR2(10),
longitude VARCHAR2(10),
testfor   VARCHAR2(15));

CREATE GLOBAL TEMPORARY TABLE gtt_c2t (
readline VARCHAR2(200))
ON COMMIT DELETE ROWS;
Create the load_sources_import procedure conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE load_sources_import AUTHID DEFINER IS
 ProcName   VARCHAR2(30) := 'load_sources_import';
 MyErrm     VARCHAR2(250);
 vFileName  VARCHAR2(30) := 'sources.txt';
 vLoc       VARCHAR2(20) := 'CTEMP';
 v_InHandle utl_file.file_type;
 vNewLine   VARCHAR2(100);
 vLineNo    PLS_INTEGER;

 Comma1     PLS_INTEGER;
 Comma2     PLS_INTEGER;
 Comma3     PLS_INTEGER;
 Comma4     PLS_INTEGER;
 Comma5     PLS_INTEGER;

 Fld1       sources_import.sourceno%TYPE;
 Fld2       sources_import.sizeno%TYPE;
 Fld3       sources_import.status%TYPE;
 Fld4       sources_import.latitude%TYPE;
 Fld5       sources_import.longitude%TYPE;
 Fld6       sources_import.testfor%TYPE;

 NoFileToLoad EXCEPTION;
BEGIN
  BEGIN
    v_InHandle := utl_file.fopen(vLoc, vFileName, 'r');

    vLineNo := 1;
    LOOP
      BEGIN
        utl_file.get_line(v_InHandle, vNewLine);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;

      IF vLineNo > 1 THEN
        vNewLine := TRANSLATE(vNewLine, 'A''', 'A');
        Comma1 := INSTR(vNewLine, ',', 1,1);
        Comma2 := INSTR(vNewLine, ',', 1,2);
        Comma3 := INSTR(vNewLine, ',', 1,3);
        Comma4 := INSTR(vNewLine, ',', 1,4);
        Comma5 := INSTR(vNewLine, ',', 1,5);

        Fld1 := SUBSTR(vNewLine,1,Comma1-1);
        Fld2 := SUBSTR(vNewLine, Comma1+1, Comma2-Comma1-1);
        Fld3 := SUBSTR(vNewLine, Comma2+1, Comma3-Comma2-1);
        Fld4 := SUBSTR(vNewLine, Comma3+1, Comma4-Comma3-1);
        Fld5 := SUBSTR(vNewLine, Comma4+1, Comma5-Comma4-1);
        Fld6 := SUBSTR(vNewLine,Comma5+1);

        INSERT INTO sources_import
        (sourceno, sizeno, status, latitude, longitude, testfor)
        VALUES
        (Fld1, Fld2, Fld3, Fld4, Fld5, Fld6);
      ELSE
        vLineNo := 2;
      END IF;
    END LOOP;
    COMMIT;
    utl_file.fclose(v_InHandle);
  EXCEPTION
    WHEN utl_file.invalid_mode THEN
      RAISE_APPLICATION_ERROR (-20051, 'Invalid Option');
    WHEN utl_file.invalid_path THEN
      RAISE_APPLICATION_ERROR (-20052, 'Invalid Path');
    WHEN utl_file.invalid_filehandle THEN
      RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
    WHEN utl_file.invalid_operation THEN
      RAISE_APPLICATION_ERROR (-20054, 'Invalid operation');
    WHEN utl_file.read_error THEN
      RAISE_APPLICATION_ERROR (-20055, 'Read Error');
    WHEN utl_file.internal_error THEN
      RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
    WHEN OTHERS THEN
      RAISE;
  END;
EXCEPTION
  WHEN NoFileToLoad THEN
    dbms_output.put_line('No File To Load Was Found');
  WHEN OTHERS THEN
    MyErrm := SQLERRM;
    dbms_output.put_line(MyErrm);
END load_sources_import;
/
Create the Comma To Table Procedure conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE c2t_demo AUTHID DEFINER IS
 my_table dbms_utility.uncl_array;
 cnt      BINARY_INTEGER;
 c_string VARCHAR2(250);

 CURSOR t_cur IS
 SELECT readline
 FROM gtt_c2t;

 t_rec t_cur%ROWTYPE;
BEGIN
  OPEN t_cur;
  LOOP
    FETCH t_cur INTO t_rec;
    EXIT WHEN t_cur%NOTFOUND;

    -- move the value from the cursor to the VARCHAR2 variable
    c_string := t_rec.readline;
    -- use the built-in package to break it up
    dbms_utility.comma_to_table(c_string, cnt, my_table);

    -- use TRANSLATE to remove the single and double quotes
    my_table(1) := TRANSLATE(my_table(1), '1"''', '1');
    my_table(2) := TRANSLATE(my_table(2), '1"''', '1');
    my_table(3) := TRANSLATE(my_table(3), '1"''', '1');
    my_table(4) := TRANSLATE(my_table(4), '1"''', '1');
    my_table(5) := TRANSLATE(my_table(5), '1"''', '1');
    my_table(6) := TRANSLATE(my_table(6), '1"''', '1');

    INSERT INTO sources_import
    (sourceno, sizeno, status,
    latitude, longitude, testfor)
    VALUES
    (my_table(1), my_table(2), my_table(3),
    my_table(4), my_table(5), my_table(6));
  END LOOP;
  COMMIT;
  CLOSE t_cur;
END c2t_demo;
/
First Procedure To Load Intermediary Table And Replace Single Quotes With Double Quotes conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE load_c2t_test AUTHID DEFINER IS
 vProcName  VARCHAR2(30) := 'load_t2c_test';
 ErrMsg     VARCHAR2(250);
 vFileName  VARCHAR2(30) := 'sources.txt';
 vLoc       VARCHAR2(20) := 'CTEMP';
 vNewLine   VARCHAR2(65);
 vFirstLine PLS_INTEGER := 0;
 StartTime  PLS_INTEGER;

 vInHandle  utl_file.file_type;
BEGIN
  StartTime := dbms_utility.get_time;
  vInHandle := utl_file.fopen(vLoc, vFileName, 'r');
  LOOP
    BEGIN
      utl_file.get_line(vInHandle, vNewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    -- find location of the delimiting commas
    BEGIN
      IF vFirstLine <> 1 THEN
        INSERT INTO gtt_c2t
        (readline)
        VALUES
        (vNewLine);
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;
  -- close the text file
  utl_file.fclose(vInHandle);

  DELETE FROM gtt_c2t
  WHERE readline LIKE '%SOURCENO%';

  UPDATE gtt_c2t
  SET readline = TRANSLATE(readline, 'A''', 'A"');

  c2t_demo;  -- 2nd procedure that parses record

EXCEPTION
  WHEN utl_file.invalid_mode THEN
    RAISE_APPLICATION_ERROR(-20051, 'Invalid Option');
  WHEN utl_file.invalid_path THEN
    RAISE_APPLICATION_ERROR(-20052, 'Invalid Path');
  WHEN utl_file.invalid_filehandle THEN
    RAISE_APPLICATION_ERROR(-20053, 'Invalid Filehandle');
  WHEN utl_file.invalid_operation THEN
    RAISE_APPLICATION_ERROR(-20054, 'Invalid operation');
  WHEN utl_file.read_error THEN
    RAISE_APPLICATION_ERROR(-20055, 'Read Error');
  WHEN utl_file.internal_error THEN
    RAISE_APPLICATION_ERROR(-20057, 'Internal Error');
  WHEN OTHERS THEN
    RAISE;
END load_c2t_test;
/
Procedure utilizing exteral table array processing conn uwclass/uwclass@pdbdev

CREATE TABLE ext_tab (
sourceno  CHAR(5),
sizeno    CHAR(6),
status    CHAR(3),
latitude  CHAR(10),
longitude CHAR(11),
testfor   CHAR(17))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY ctemp
ACCESS PARAMETERS
(FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(sourceno, sizeno, status, latitude, longitude, testfor))
LOCATION ('sources.txt'))
PARALLEL
REJECT LIMIT 10;

CREATE OR REPLACE PROCEDURE array_load AUTHID DEFINER IS

CURSOR acur IS
SELECT TRANSLATE(sourceno, 'A''', 'A'), 
TRANSLATE(sizeno, 'A''', 'A'),
TRANSLATE(status, 'A''', 'A'),
TRANSLATE(latitude, 'A''', 'A'),
TRANSLATE(longitude, 'A''', 'A'),
TRANSLATE(testfor, 'A''', 'A')
FROM ext_tab;

TYPE   profarray IS TABLE OF sources_import%ROWTYPE;
l_data profarray;

BEGIN
  OPEN acur;
  FETCH acur BULK COLLECT INTO l_data;

  FORALL i IN 1..l_data.COUNT
  INSERT INTO sources_import VALUES l_data(i);
  COMMIT;
  CLOSE acur;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END array_load;
/
Procedure blending UTL_FILE and array processing conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE blended AUTHID DEFINER IS
 vFileName  VARCHAR2(30) := 'sources.txt';
 vLoc       VARCHAR2(20) := 'CTEMP';
 v_InHandle utl_file.file_type;
 vNewLine   VARCHAR2(100);
 vLineNo    PLS_INTEGER;
 c1         PLS_INTEGER;
 c2         PLS_INTEGER;
 c3         PLS_INTEGER;
 c4         PLS_INTEGER;
 c5         PLS_INTEGER;

TYPE profarray IS TABLE OF sources_import%ROWTYPE
INDEX BY BINARY_INTEGER;

l_data profarray;

BEGIN
  v_InHandle := utl_file.fopen(vLoc, vFileName, 'r');
  vLineNo := 1;
  LOOP
    BEGIN
      utl_file.get_line(v_InHandle, vNewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    vNewLine := TRANSLATE(vNewLine, 'A''', 'A');
    c1 := INSTR(vNewLine, ',', 1,1);
    c2 := INSTR(vNewLine, ',', 1,2);
    c3 := INSTR(vNewLine, ',', 1,3);
    c4 := INSTR(vNewLine, ',', 1,4);
    c5 := INSTR(vNewLine, ',', 1,5);

    l_data(vLineNo).sourceno := SUBSTR(vNewLine,1,c1-1);
    l_data(vLineNo).sizeno := SUBSTR(vNewLine,c1+1,c2-c1-1);
    l_data(vLineNo).status := SUBSTR(vNewLine,c2+1,c3-c2-1);
    l_data(vLineNo).latitude := SUBSTR(vNewLine,c3+1,c4-c3-1);
    l_data(vLineNo).longitude := SUBSTR(vNewLine,c4+1,c5-c4-1);
    l_data(vLineNo).testfor := SUBSTR(vNewLine,c5+1);

    vLineNo := vLineNo+1;
  END LOOP;
  utl_file.fclose(v_InHandle);

  FORALL i IN 1..l_data.COUNT
  INSERT INTO sources_import VALUES l_data(i);
  DELETE FROM sources_import WHERE sourceno = 'SOURCENO';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END blended;
/
 
Profiling Demo
Profiler Run conn uwclass/uwclass@pdbdev

set serveroutput on

-- BEGIN RUN 1: BASIC CURSOR LOOP
-- clean out the profiler tables
exec profreset

PL/SQL procedure successfully completed.

-- clean out the test tables
TRUNCATE TABLE sources_import;

Table truncated.

-- run the procedure to put it into memory
exec load_sources_import;

PL/SQL procedure successfully completed.

SELECT * FROM sources_import;

No rows selected.

-- truncate the table before starting again
TRUNCATE TABLE sources_import;

Table truncated.

-- start the profiler
exec dbms_profiler.start_profiler('A')

-- run the procedure
exec load_sources_import

PL/SQL procedure successfully completed.

-- stop the profiler
exec dbms_profiler.stop_profiler;

-- get the report
@c:\temp\profiler.sql

-- save the report as run1.txt

-- examine the profiler tables
set linesize 121

-- view raw profiler data
SELECT runid, unit_number, line#, total_occur, total_time,
min_time, max_time
FROM plsql_profiler_data;

SELECT runid, related_run, run_owner, run_date, run_comment,
run_total_time, run_system_info
FROM plsql_profiler_runs;

col unit_type format a20
col unit_name format a25

SELECT runid, unit_number, unit_type, unit_owner, unit_name,
unit_timestamp, total_time
FROM plsql_profiler_units;

SELECT dump(unit_timestamp)
FROM plsql_profiler_units;

-- BEGIN RUN 2: COMMA_TO_TABLE
-- clean out the profiler tables
exec profreset

-- clean out the test tables
TRUNCATE TABLE sources_import;

-- run the procedure to put it into memory
exec load_c2t_test;

SELECT * FROM sources_import;

-- clean out the test tables
TRUNCATE TABLE sources_import;

-- start the profiler
exec dbms_profiler.start_profiler('B')

-- run the procedure
exec load_c2t_test

-- stop the profiler
exec dbms_profiler.stop_profiler;

-- get the report
@c:\temp\profsum.sql

-- save the report as run2.txt

-- BEGIN RUN 3: ARRAY PROCESSING

 -- clean out the profiler tables
exec profreset

-- clean out the test tables
TRUNCATE TABLE sources_import;

-- run the procedure to put it into memory
exec array_load;

SELECT * FROM sources_import;

-- truncate the table before starting again
TRUNCATE TABLE sources_import;

-- start the profiler
exec dbms_profiler.start_profiler('C')

-- run the procedure
exec array_load

-- stop the profiler
exec dbms_profiler.stop_profiler;

-- get the report
@c:\temp\profsum.sql

-- save the report as run3.txt

-- BEGIN RUN 4: BLENDED PROCESSING

 -- clean out the profiler tables
exec profreset

-- clean out the test tables
TRUNCATE TABLE sources_import;

-- run the procedure to put it into memory
exec blended;

SELECT * FROM sources_import;

-- truncate the table before starting again
TRUNCATE TABLE sources_import;

-- start the profiler
exec dbms_profiler.start_profiler('D')

-- run the procedure
exec blended

-- stop the profiler
exec dbms_profiler.stop_profiler;

-- get the report
@c:\temp\profsum.sql

-- save the report as run4.txt
 
Demo 2 Procedure
Demo Tables and Data conn uwclass/uwclass@pdbdev

CREATE TABLE parent (
part_num  NUMBER,
part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent;

DECLARE
 k parent.part_name%TYPE := 'Transducer';
BEGIN
  FOR i IN 1 .. 200000
  LOOP
    SELECT DECODE(k, 'Transducer', 'Rectifier',
    'Rectifier', 'Capacitor',
    'Capacitor', 'Knob',
    'Knob', 'Chassis',
    'Chassis', 'Transducer')
    INTO k
    FROM dual;

    INSERT INTO parent VALUES (i, k);
  END LOOP;
  COMMIT;
END;
/
Demo Procedures conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE slow_way AUTHID CURRENT_USER IS
BEGIN
  FOR r IN (SELECT * FROM parent) LOOP
    r.part_num := r.part_num * 10;
    INSERT INTO child
    VALUES
    (r.part_num, r.part_name);
  END LOOP;
  COMMIT;
END slow_way;
/

CREATE OR REPLACE PROCEDURE fast_way AUTHID CURRENT_USER IS
 TYPE myarray IS TABLE OF parent%ROWTYPE;
 l_data myarray;

 CURSOR r IS
 SELECT part_num, part_name
 FROM parent;

 BatchSize CONSTANT POSITIVE := 1000;
BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT BatchSize;

    FOR j IN 1 .. l_data.COUNT LOOP
      l_data(j).part_num := l_data(j).part_num * 10;
    END LOOP;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child VALUES l_data(i);

    EXIT WHEN l_data.COUNT < BatchSize;
  END LOOP;
  COMMIT;
  CLOSE r;
END fast_way;
/
Profiler Script #1 conn uwclass/uwclass@pdbdev

exec dbms_profiler.start_profiler('SLOW');

exec slow_way;

exec dbms_profiler.stop_profiler;

exec dbms_profiler.start_profiler('FAST');

exec fast_way;

exec dbms_profiler.stop_profiler;
Eval SLOW Run conn uwclass/uwclass@pdbdev

SELECT runid, unit_name, unit_owner
FROM plsql_profiler_units
ORDER BY 1;

col text format a60

SELECT ds.line, p.total_occur, p.total_time Msec, ds.text
FROM dba_source ds, (
  SELECT ppu.unit_owner, ppu.unit_name, ppu.unit_type, ppd.line#, ppd.total_occur,
         ppd.total_time/1000000 total_time
  FROM plsql_profiler_data ppd, plsql_profiler_units ppu
  WHERE ppu.runid = 3
  AND ppu.runid = ppd.runid
  AND ppu.unit_number = ppd.unit_number) p
WHERE ds.owner = p.unit_owner(+)
AND ds.name = p.unit_name(+)
AND ds.type = p.unit_type(+)
AND ds.line = p.line#(+)
AND ds.name = 'SLOW_WAY'
AND ds.owner = 'UWCLASS'
ORDER BY 1;

   LINE TOTAL_OCCUR           MSEC     TEXT
------- ----------- -------------- -----------------------------------------
      1           0      .011535 PROCEDURE slow_way AUTHID CURRENT_USER IS
      2                             BEGIN
      3        2002   430.588729      FOR r IN (SELECT * FROM parent) LOOP
      4      200000   115.299009        r.part_num := r.part_num * 10;
      5      200000 17889.007           INSERT INTO child
      6                                 VALUES
      7                                 (r.part_num, r.part_name);
      8                               END LOOP;
      9           1      .83708       COMMIT;
     10           1      .012322    END slow_way;

10 rows selected.
Evaluate FAST Run conn uwclass/uwclass@pdbdev

SELECT runid, unit_name, unit_owner
FROM plsql_profiler_units
ORDER BY 1;

col text format a60

SELECT ds.line, p.total_occur, p.total_time Msec, ds.text
FROM dba_source ds, (
  SELECT ppu.unit_owner, ppu.unit_name, ppu.unit_type, ppd.line#, ppd.total_occur,
         ppd.total_time/1000000 total_time
  FROM plsql_profiler_data ppd, plsql_profiler_units ppu
  WHERE ppu.runid = 4
  AND ppu.runid = ppd.runid
  AND ppu.unit_number = ppd.unit_number) p
WHERE ds.owner = p.unit_owner(+)
AND ds.name = p.unit_name(+)
AND ds.type = p.unit_type(+)
AND ds.line = p.line#(+)
AND ds.name = 'FAST_WAY'
AND ds.owner = 'UWCLASS'
ORDER BY 1;

   LINE TOTAL_OCCUR           MSEC     TEXT
------- ----------- -------------- -----------------------------------------
      1           1        .132864 PROCEDURE fast_way AUTHID CURRENT_USER IS
      2                             TYPE myarray IS TABLE OF parent%ROWTYPE;
      3                             l_data myarray;
      4           0        .00304   CURSOR r IS
      5           1      28.317165  SELECT part_num, part_name
      6           0        .011255  FROM parent;
      7           1        .001778  BatchSize CONSTANT POSITIVE := 1000;
      8                            BEGIN
      9           1        .002619   OPEN r;
     10         201       0          LOOP
     11         201     808.792131     FETCH r BULK COLLECT INTO l_data LIMIT BatchSize;
     12      200201     118.087805     FOR j IN 1 .. l_data.COUNT LOOP
     13      200000     350.545577       l_data(j).part_num := l_data(j).part_num * 10;
     14                                END LOOP;
     15         201    1772.08412      FORALL i IN 1..l_data.COUNT
     16                                INSERT INTO child VALUES l_data(i);
     17         200        .459526     EXIT WHEN l_data.COUNT < BatchSize;
     18                              END LOOP;
     19           1       3.923959   COMMIT;
     20           1        .148873   CLOSE r;
     21           1        .099324 END fast_way;

21 rows selected.

Related Topics
Autotrace
DBMS_HPROF
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
Explain Plan
Tracing