Oracle DBMS_HPROF
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
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,
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);
See DBMS_HPROF Demo Below
 
STOP_PROFILING
Stop profiling dbms_hprof.stop_profiling;
See DBMS_HPROF Demo Below
 
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

Related Topics
Autotrace
DBMS_MONITOR
DBMS_PROFILER
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
Explain Plan
Files of Interest
My Oracle Support Doc ID 763944.1
Packages
PLSHPROF
TKPROF

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved