CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_PROFILER
Version 10.2
 
Installation Instructions
Source {ORACLE_HOME}/rdbms/admin/dbmspbp.sql
-- also review
{ORACLE_HOME}/plsql/demo/profsum.sql
First Available 8.1.5
Constants

Return values from DBMS_PROFILER functions
Name Data Type Value
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
Exceptions
Exception Name Error Code Reason
profiler_error ORA-06528 Parameter or I/O error
version_mismatch ORA-06529 Incorrect profiler version for database
System Privileges GRANT create session TO <schema_name>;
GRANT create procedure TO <schema_name>;
GRANT create sequence TO <schema_name>;
GRANT create table TO <schema_name>;
GRANT create view TO <schema_name>;
GRANT create session TO uwclass;
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create table TO uwclass;
GRANT create view TO uwclass;
 
FLUSH_DATA
Flushes the Profiler buffer to the Profiler tables

Overload 1
dbms_profiler.flush_data RETURN BINARY_INTEGER;
See PAUSE_PROFILER Demo
Overload 2 dbms_profiler.flush_data;
SELECT dbms_profiler.flush_data
FROM dual;
 
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;
/
 
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;
 
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;
 
RESUME_PROFILER
Restarts Profiler data collection

Overload 1
dbms_profiler.resume_profiler RETURN BINARY_INTEGER;
See PAUSE_PROFILER Demo
Overload 2 dbms_profiler.resume_profiler;
SELECT dbms_profiler.resume_profiler
FROM dual;
 
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 IS
 vd VARCHAR2(5);
BEGIN
  FOR i IN 1..100
  LOOP
    SELECT dummy INTO vd FROM dual;
  END LOOP;
END proc1;
/

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;
/
 
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;
TBD
Overload 2 dbms_profiler.stop_profiler;
See demo below
 
Non-Oracle Code
Preparation as SYS GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create view TO uwclass;

@? dbms\admin\profload.sql
Preparation as UWCLASS @? dbms\admin\proftab.sql
@?\plsql\demo\profrep.sql <-- not in 11g
Procedure To Empty Profiler Tables
Between Runs
CREATE OR REPLACE PROCEDURE profreset 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 c: emp or equivalent

Click Here

Load Demo File
Save in c: emp or equivalent

Click Here

 
Demo Procedure Preparations
Comma To Table Procedure Demo Tables 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;

The load_sources_import procedure
CREATE OR REPLACE PROCEDURE load_sources_import 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;
/

Comma To Table Procedure
CREATE OR REPLACE PROCEDURE c2t_demo 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
CREATE OR REPLACE PROCEDURE load_c2t_test 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
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 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
CREATE OR REPLACE PROCEDURE blended 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
set serveroutput on

-- BEGIN RUN 1: BASIC CURSOR LOOP
-- 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_sources_import;

SELECT * FROM sources_import;

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

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

-- run the procedure
exec load_sources_import

-- stop the profiler
exec dbms_profiler.stop_profiler;

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

-- save the report as run1.txt

-- Examine 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: emp\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: emp\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: emp\profsum.sql

-- save the report as run4.txt
 
Related Topics
Autotrace
DBMS_HPROF
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
Explain Plan
TKPROF
TKPROF
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [196 users online]    © 2010 psoug.org