Oracle DBMS_HPROF hierarchical profiler PL/SQL profiling

Search the Reference Library pages:

Oracle DBMS_HPROF

Version 11.1

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmshpro.sql

First Available

11.1

Dependent Objects

DBMS_HPROF_LIB

PLITBLM

ANALYZE

Analyzes the raw profiler output and produces hierarchical profiler information in database tables

dbms_hprof.analyze(
location     IN VARCHAR2,
filename     IN VARCHAR2,
summary_mode IN BOOLEAN     DEFAULT FALSE,
trace        IN VARCHAR2    DEFAULT NULL,
skip         IN PLS_INTEGER DEFAULT 0,
collect      IN PLS_INTEGER DEFAULT NULL,
run_comment IN VARCHAR2    DEFAULT NULL)
RETURN NUMBER;

See DBMS_HPROF demo below

START_PROFILING

Start profiling at this point and collect profile information in the
specified location

dbms_hprof.start_profiling(
location IN VARCHAR2 DEFAULT NULL,
filename IN VARCHAR2 DEFAULT NULL,
max_depth IN PLS_INTEGER DEFAULT NULL);

See DBMS_HPROF demo below

STOP_PROFILING

Stop profiling

dbms_hprof.stop_profiling;

See DBMS_HPROF demo below

Demo Procedure Preparations

Load Demo File
Save in c: emp or equivalent

Click Here

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 external 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

Procedure To Empty Profiler Tables
Between Runs

-- this procedure must be created after running dbmshptab.sql or it is
-- necessary to recompile the procedure after the script has been run.

CREATE OR REPLACE PROCEDURE profreset IS
BEGIN
DELETE FROM dbmshp_function_info;
DELETE FROM dbmshp_parent_child_info;
DELETE FROM dbmshp_runs;
COMMIT;
END profreset;
/

Profiler Run

conn / as sysdba

grant execute on dbms_hprof to uwclass;

CREATE OR REPLACE DIRECTORY ctemp AS 'c: emp';

GRANT read, write ON DIRECTORY ctemp TO uwclass;

conn uwclass/uwclass

-- create profiler tables
@?/rdbms/admin/dbmshptab

set linesize 121
col object_name format a30

SELECT object_name, object_type
FROM user_objects
WHERE object_name LIKE 'DBMSHP%';

exec profreset;

DECLARE
i POSITIVE;
BEGIN
dbms_hprof.start_profiling('CTEMP', 'hprof.trc');
load_sources_import;
dbms_hprof.stop_profiling;

i := dbms_hprof.analyze(location => 'CTEMP', filename => 'hprof.trc',
run_comment => 'HPROF demo run');

dbms_output.put_line('Profiler Run #: ' || TO_CHAR(i));
END;
/

set linesize 121
col run_timestamp format a30
col run_comment format a25
col namespace format a10
col function format a25
col module format a20
col owner format a10
col type format a15

desc dbmshp_runs

SELECT runid, run_timestamp, total_elapsed_time, run_comment
FROM dbmshp_runs;

desc dbmshp_function_info

SELECT symbolid, owner, module, type, function, line#, namespace
FROM dbmshp_function_info;

SELECT function, line#, namespace, subtree_elapsed_time, function_elapsed_time, calls
FROM dbmshp_function_info
WHERE runid = 1;

desc dbmshp_parent_child_info

-- symid values reference dbms_function_info.symbolid
SELECT parentsymid, childsymid, subtree_elapsed_time, function_elapsed_time, calls
FROM dbmshp_parent_child_info
WHERE runid = 1;

Analyze the output using the PLSHPROF utility

cd c: emp

plshprof -trace -output hprof_trace.html hprof.trc

Related Topics

-----