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.
Purpose
Provides an interface to profile PL/SQL application code and identify performance bottlenecks. It can also collect and persistently store profiler data for use in performing comparisons.
Roll up and calculate the total time usage for all units that have been part of a run
dbms_profiler.rollup_run(run_number IN NUMBER);
CREATE OR REPLACE PROCEDURE proc1 AUTHID
DEFINER IS
vd VARCHAR2(5);
BEGIN
FOR i IN 1..100
LOOP
SELECT dummy INTO vd FROM dual;
END LOOP;
END proc1;
/
-- failure to run proftab.sql for the user
DECLARE
v_run NUMBER;
BEGIN
dbms_profiler.start_profiler(SYSDATE, 'test1', v_run);
proc1;
dbms_profiler.stop_profiler;
dbms_profiler.rollup_run(v_run);
END;
/
BEGIN dbms_profiler.start_profiler; END;
*
ERROR at line 1:
ORA-06528: Error executing PL/SQL profiler
ORA-06512: at "SYS.DBMS_PROFILER", line 132
ORA-06512: at "SYS.DBMS_PROFILER", line 141
ORA-06512: at line 1
-- run proftab
@?/rdbms/admin/proftab.sql
-- after running proftab
DECLARE
v_run NUMBER;
BEGIN
dbms_profiler.start_profiler(SYSDATE, 'test1', v_run);
proc1;
dbms_profiler.stop_profiler;
dbms_profiler.rollup_run(v_run);
END;
/ PL/SQL procedure successfully completed.
Roll up and calculate the total time usage for a specific unit that has been part of a run
dbms_profiler.rollup_unit(
run_number IN NUMBER,
unit IN NUMBER);
-- executes the following code
UPDATE plsql_profiler_units
SET total_time = (
SELECT SUM(total_time)
FROM plsql_profiler_data
WHERE runid = run_number
AND unit_number = unit);
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create view TO uwclass;
@?\rdbms\admin\profload.sql
Preparation as UWCLASS
conn uwclass/uwclass@pdbdev
@?\rdbms\admin\proftab.sql
Procedure To Empty Profiler Tables Between Runs
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE profreset AUTHID DEFINER IS
BEGIN
DELETE FROM plsql_profiler_data;
DELETE FROM plsql_profiler_units;
DELETE FROM plsql_profiler_runs;
COMMIT;
END profreset;
/
DBMS_Profiler Report. Save in c:\temp or equivalent location
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;
/
Create the Comma To Table Procedure
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE c2t_demo AUTHID DEFINER 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
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
Profiler Run
conn uwclass/uwclass@pdbdev
set serveroutput on
-- BEGIN RUN 1: BASIC CURSOR LOOP
-- clean out the profiler tables
exec profreset
PL/SQL procedure successfully completed.
-- clean out the test tables
TRUNCATE TABLE sources_import;
Table truncated.
-- run the procedure to put it into memory
exec load_sources_import;
PL/SQL procedure successfully completed.
SELECT *
FROM sources_import;
No rows selected.
-- truncate the table before starting again
TRUNCATE TABLE sources_import;
Table truncated.
-- start the profiler
exec dbms_profiler.start_profiler('A')
-- run the procedure
exec load_sources_import
PL/SQL procedure successfully completed.
-- stop the profiler
exec dbms_profiler.stop_profiler;
-- get the report
@c:\temp\profiler.sql
-- save the report as run1.txt
-- examine the profiler tables
set linesize 121
-- view raw profiler data
SELECT runid, unit_number, line#, total_occur, total_time,
min_time, max_time
FROM plsql_profiler_data;
SELECT runid, related_run, run_owner, run_date, run_comment,
run_total_time, run_system_info
FROM plsql_profiler_runs;
col unit_type format a20
col unit_name format a25
SELECT runid, unit_number, unit_type, unit_owner, unit_name,
unit_timestamp, total_time
FROM plsql_profiler_units;
SELECT dump(unit_timestamp)
FROM plsql_profiler_units;
-- BEGIN RUN 2: COMMA_TO_TABLE
-- clean out the profiler tables
exec profreset
-- clean out the test tables
TRUNCATE TABLE sources_import;
-- run the procedure to put it into memory
exec load_c2t_test;
SELECT * FROM sources_import;
-- clean out the test tables
TRUNCATE TABLE sources_import;
-- start the profiler
exec dbms_profiler.start_profiler('B')
-- run the procedure
exec load_c2t_test
-- stop the profiler
exec dbms_profiler.stop_profiler;
-- get the report
@c:\temp\profsum.sql
-- save the report as run2.txt
-- BEGIN RUN 3: ARRAY PROCESSING
-- clean out the profiler tables
exec profreset
-- clean out the test tables
TRUNCATE TABLE sources_import;
-- run the procedure to put it into memory
exec array_load;
SELECT *
FROM sources_import;
-- truncate the table before starting again
TRUNCATE TABLE sources_import;
-- start the profiler
exec dbms_profiler.start_profiler('C')
-- run the procedure
exec array_load
-- stop the profiler
exec dbms_profiler.stop_profiler;
-- get the report
@c:\temp\profsum.sql
-- save the report as run3.txt
-- BEGIN RUN 4: BLENDED PROCESSING
-- clean out the profiler tables
exec profreset
-- clean out the test tables
TRUNCATE TABLE sources_import;
-- run the procedure to put it into memory
exec blended;
SELECT * FROM sources_import;
-- truncate the table before starting again
TRUNCATE TABLE sources_import;
-- start the profiler
exec dbms_profiler.start_profiler('D')
-- run the procedure
exec blended
-- stop the profiler
exec dbms_profiler.stop_profiler;
DECLARE
k parent.part_name%TYPE := 'Transducer';
BEGIN
FOR i IN 1 .. 200000
LOOP
SELECT DECODE(k, 'Transducer', 'Rectifier',
'Rectifier', 'Capacitor',
'Capacitor', 'Knob',
'Knob', 'Chassis',
'Chassis', 'Transducer')
INTO k
FROM dual;
INSERT INTO parent VALUES (i, k);
END LOOP;
COMMIT;
END;
/
Demo Procedures
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE slow_way AUTHID CURRENT_USER IS
BEGIN
FOR r IN (SELECT * FROM parent) LOOP
r.part_num := r.part_num * 10;
INSERT INTO child
VALUES
(r.part_num, r.part_name);
END LOOP;
COMMIT;
END slow_way;
/
CREATE OR REPLACE PROCEDURE fast_way AUTHID CURRENT_USER IS
TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;
CURSOR r IS
SELECT part_num, part_name
FROM parent;
BatchSize CONSTANT POSITIVE := 1000;
BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO l_data LIMIT BatchSize;
FOR j IN 1 .. l_data.COUNT LOOP
l_data(j).part_num := l_data(j).part_num * 10;
END LOOP;
FORALL i IN 1..l_data.COUNT
INSERT INTO child VALUES l_data(i);
EXIT WHEN l_data.COUNT < BatchSize;
END LOOP;
COMMIT;
CLOSE r;
END fast_way;
/
Profiler Script #1
conn uwclass/uwclass@pdbdev
exec dbms_profiler.start_profiler('SLOW');
exec slow_way;
exec dbms_profiler.stop_profiler;
exec dbms_profiler.start_profiler('FAST');
exec fast_way;
exec dbms_profiler.stop_profiler;
Eval SLOW Run
conn uwclass/uwclass@pdbdev
SELECT runid, unit_name, unit_owner
FROM plsql_profiler_units
ORDER BY 1;
col text format a60
SELECT ds.line, p.total_occur, p.total_time Msec, ds.text
FROM dba_source ds, (
SELECT ppu.unit_owner, ppu.unit_name, ppu.unit_type, ppd.line#, ppd.total_occur,
ppd.total_time/1000000 total_time
FROM plsql_profiler_data ppd, plsql_profiler_units ppu
WHERE ppu.runid = 3
AND ppu.runid = ppd.runid
AND ppu.unit_number = ppd.unit_number) p
WHERE ds.owner = p.unit_owner(+)
AND ds.name = p.unit_name(+)
AND ds.type = p.unit_type(+)
AND ds.line = p.line#(+)
AND ds.name = 'SLOW_WAY'
AND ds.owner = 'UWCLASS'
ORDER BY 1;
LINE TOTAL_OCCUR MSEC TEXT
------- ----------- -------------- -----------------------------------------
1 0 .011535 PROCEDURE slow_way AUTHID CURRENT_USER IS
2 BEGIN
3 2002 430.588729 FOR r IN (SELECT * FROM parent) LOOP
4 200000 115.299009 r.part_num := r.part_num * 10;
5 200000 17889.007 INSERT INTO child
6 VALUES
7 (r.part_num, r.part_name);
8 END LOOP;
9 1 .83708 COMMIT;
10 1 .012322 END slow_way;
10 rows selected.
Evaluate FAST Run
conn uwclass/uwclass@pdbdev
SELECT runid, unit_name, unit_owner
FROM plsql_profiler_units
ORDER BY 1;
col text format a60
SELECT ds.line, p.total_occur, p.total_time Msec, ds.text
FROM dba_source ds, (
SELECT ppu.unit_owner, ppu.unit_name, ppu.unit_type, ppd.line#, ppd.total_occur,
ppd.total_time/1000000 total_time
FROM plsql_profiler_data ppd, plsql_profiler_units ppu
WHERE ppu.runid = 4
AND ppu.runid = ppd.runid
AND ppu.unit_number = ppd.unit_number) p
WHERE ds.owner = p.unit_owner(+)
AND ds.name = p.unit_name(+)
AND ds.type = p.unit_type(+)
AND ds.line = p.line#(+)
AND ds.name = 'FAST_WAY'
AND ds.owner = 'UWCLASS'
ORDER BY 1;
LINE TOTAL_OCCUR MSEC TEXT
------- ----------- -------------- -----------------------------------------
1 1 .132864 PROCEDURE fast_way AUTHID CURRENT_USER IS
2 TYPE myarray IS TABLE OF parent%ROWTYPE;
3 l_data myarray;
4 0 .00304 CURSOR r IS
5 1 28.317165 SELECT part_num, part_name
6 0 .011255 FROM parent;
7 1 .001778 BatchSize CONSTANT POSITIVE := 1000;
8 BEGIN
9 1 .002619 OPEN r;
10 201 0 LOOP
11 201 808.792131 FETCH r BULK COLLECT INTO l_data LIMIT BatchSize;
12 200201 118.087805 FOR j IN 1 .. l_data.COUNT LOOP
13 200000 350.545577 l_data(j).part_num := l_data(j).part_num * 10;
14 END LOOP;
15 201 1772.08412 FORALL i IN 1..l_data.COUNT
16 INSERT INTO child VALUES l_data(i);
17 200 .459526 EXIT WHEN l_data.COUNT < BatchSize;
18 END LOOP;
19 1 3.923959 COMMIT;
20 1 .148873 CLOSE r;
21 1 .099324 END fast_way;
