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_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
Autotrace
DBMS_MONITOR
DBMS_PROFILER
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
Explain Plan
Files of Interest
PLSHPROF
TKPROF
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [59 users online]    © 2010 psoug.org