Oracle 12cR2 DBMS

Oracle DBMS_HPROF Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

Purpose

Supports hierarchical profiling of PL/SQL.

AUTHID

CURRENT_USER

Dependencies

DBMS_HPROF_LIB

DBMS_STANDARD

PLITBLM

Documented

Yes

Exceptions

Error Code	Reason
Unknown	incorrect directory permission
Unknown	invalid directory object
Unknown	invalid filename
Unknown	invalid maxdepth

First Available

11gR1

Security Model

Owned by SYS with no granted

Source

{ORACLE_HOME}/rdbms/admin/dbmshpro.sql

Subprograms

ANALYZE

START_PROFILING

STOP_PROFILING

ANALYZE (two new 12.2 overloads)

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

Overload 1

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,
profile_uga IN BOOLEAN     DEFAULT NULL,
profile_pga IN BOOLEAN     DEFAULT NULL)
RETURN NUMBER;

See DBMS_HPROF Demo Below

Overload 2

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,
profile_uga IN BOOLEAN     DEFAULT NULL,
profile_pga IN BOOLEAN     DEFAULT NULL);

See DBMS_HPROF Demo Below

Overload 3

dbms_hprof.analyze(
location     IN VARCHAR2,
filename     IN VARCHAR2,
report_clob OUT CLOB,
trace        IN VARCHAR2    DEFAULT NULL,
skip         IN PLS_INTEGER DEFAULT 0,
collect      IN PLS_INTEGER DEFAULT NULL,
profile_uga IN BOOLEAN     DEFAULT NULL,
profile_pga IN BOOLEAN     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,
profile_uga IN BOOLEAN     DEFAULT NULL,
profile_pga IN BOOLEAN     DEFAULT NULL,
sqlmonitor IN BOOLEAN);

See DBMS_HPROF Demo Below

STOP_PROFILING (one new 12.2 overload)

Stop profiling
Overload 1

dbms_hprof.stop_profiling;

See DBMS_HPROF Demo Below

Overload 2

dbms_hprof.stop_profiling RETURN CLOB;

DECLARE
c CLOB;
BEGIN
c := dbms_hprof.stop_profiling;
dbms_output.put_line(c);
END;
/

Demo Procedure Preparations

Load Demo File and save in c:\temp or an equivalent directory

Click Here

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;

The load_sources_import procedure

conn uwclass/uwclass@pdbdev

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

conn uwclass/uwclass@pdbdev

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 QuotesWith Double Quotes

conn uwclass/uwclass@pdbdev

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

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

conn uwclass/uwclass@pdbdev

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 sys@pdbdev as sysdba

grant execute on dbms_hprof to uwclass;

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

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', NULL, TRUE, TRUE);
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:\temp

plshprof -trace -output hprof_trace.html hprof.trc