ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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);
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
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;
/
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;
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;
/
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;
