Oracle 19cR3 DBMS

Oracle DBMS_HPROF Version 19.3

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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
CREATE_TABLES

ANALYZE

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; PRAGMA DEPRECATE(analyze, 'analyze file overload is deprecated.');

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); PRAGMA DEPRECATE(analyze, 'analyze file overload is deprecated.');

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; PRAGMA DEPRECATE(analyze, 'analyze file overload is deprecated.');

See DBMS_HPROF Demo Below

Overload 4 dbms_hprof.analyze( trace_id IN NUMBER, 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 5 dbms_hprof.analyze( trace_id IN NUMBER, 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);

See DBMS_HPROF Demo Below

CREATE_TABLES

Creates the table dbmshp_trace_data and sequence dbmshp_tracenumber sequence dbms_hprof.start_profiling(force_it IN BOOLEAN DEFAULT FALSE);

exec dbms_hprof.create_tables(TRUE);

START_PROFILING

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

Overload 1 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 DEFAULT TRUE);

See DBMS_HPROF Demo Below

Overload 2 dbms_hprof.start_profiling( max_depth IN PLS_INTEGER DEFAULT NULL, profile_uga IN BOOLEAN DEFAULT NULL, profile_pga IN BOOLEAN DEFAULT NULL, sqlmonitor IN BOOLEAN DEFAULT TRUE, run_comment IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;

See DBMS_HPROF Demo Below

STOP_PROFILING

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@pdbdev -- 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