Oracle DBMS_PROFILER

Search the Reference Library pages:

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

-----