General Information |
Source |
{ORACLE_HOME}/rdbms/admin/dbmshpro.sql |
First Available |
11.1 |
Dependent Objects |
|
|
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 |
|