整理电脑的时候，发现以前写的一篇关于dbms_profiler的文章，现在贴出来，方便参考。
A BRIEF INTRODUCTION TO MY_PLSQL_PROFILER
1. Background
This document isnot intended to give out comprehensive introduction to the oracle-suppliedpackage DBMS_PROFILER. Instead, it will focus on the incentive of creating a“wrapper” package over DBMS_PROFILER and how to use the package MY_PLSQL_PROFILER.
Before touching onthe package MY_PLSQL_PROFILER, we should know some background on DBMS_PROFILER. DBMS_PROFILER is an oracle-supplied package, which providesPL/SQL developers with a powerful tool to analyze a PL/SQL unit execution anddetermine the runtime behavior. DBMS_PROFILER can help to identify theperformance bottlenecks, as well as where excess execution time is being spentin the code. The possible profiler statistics provided by DBMS_PROFILER include…
(1) Totalnumber of times each line of code was executed.
(2) Timespent executing each line of code.
(3) Minimumand maximum duration spent on a specific line of code.
(4) Linesof code actually being executed for a given scenario.
Though the packageDBMS_PROFILER is powerful, it’s not very easy to use it and view the statisticscaptured by the profiler. Because you need to join together several tables toget the results you want. Each time you want to view the results, you need to spend time writing a bunch of very similar SQL statements which is boring andtime-consuming. In order to save developers lots of valuable time and focusdevelopers on resolving the problems in their PL/SQL programs, package MY_PLSQL_PROFILER is coming into play.
MY_PLSQL_PROFILERis created on top of the package DBMS_PROFILER, the wrapper of DBMS_PROFILER inother words, which encapsulates the procedures to call the PL/SQL profiler andlook up a bunch of tables to get the profiler statistics. WithCIP_PLSQL_PROFILER, you can only issue a simple SQL statement to get theprofiler statistics you just captured, and you can also call the procedure inthe package in your host program to view the statistics, which is quiteconvenient.
2. Functionalities
By and large, two functionalities are provided by thepackage CIP_PLSQL_PROFILER, one is to capture the statistics of your PL/SQLprogram, and the other is to view the results of the statistics. Concretely,you can call the function CAPTURE_MODULE_STATS to capture thestatistics, and turn to functions GET_PROFILER_REPORT to view theresults.
Though only one version of CAPTURE_MODULE_STATS isprovided in the package, several GET_PROFILER_REPORT are implemented with the purpose to meet different requests. For example, you can just issue a simple SQL statement,like ‘SELECT * FROM table (MY_PLSQL_PROFILER.GET_PROFILER_REPORT)’, or callthe procedure in your host program to view the statistics via a cursor.
Below is the moredetailed description of each function provided by this package…
2.1. Capture Profiler Statistics
FUNCTION CAPTURE_MODULE_STATS (p_unit_name_in IN VARCHAR2,
p_module_block_inIN VARCHAR2) RETURN NUMBER;
Just as the name implies, this function is used to capturethe statistics for a given unit (module). The parameter ‘p_unit_name_in’ is thename of the unit you want to use DBMS_PROFILER to track statistics on.Generally, it will be the name of a procedure, function or package. Theparameter ‘p_module_block_in’ is the code block to call the given procedure orfunction. The return value is the run id which combined with the unit name willuniquely identify the current profiling process.
2.2. View Profiler Statistics
FUNCTION GET_PROFILER_REPORT_L (p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2,
p_result_cursor_in_outOUT SYS_REFCURSOR)
RETURN NUMBER;
FUNCTION GET_PROFILER_REPORT_L (p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2)
RETURNPLSQL_PROFILER_T;
These two functions are used to get the profiler report for the given run id and unitname. Please note that the suffix "_L" suffix in the function names,which means "Low Lever". As these two functions are directly based onthe "lower level" --- PLSQL_PROFILER_XXX tables, in contrast to two higherlevel tables -- MY_PLSQL_PROFILER_LOOKUP & MY_PLSQL_PROFILER_REPORT (willbe covered in the next section). Thereason why we bother creating two "duplicated" tables is because wewant to keep the profiler statistics history. Suppose we refine the code someday, we cannot get the historical profilerstatistics based on those "lower table" because the data in table ALL_SOURCES is changed.
These two functions are actually not recommended for use; refer to GET_REPOFILER_REPORTas the better solution.
FUNCTION GET_PROFILER_REPORT (p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2,
p_result_cursor_in_out OUT SYS_REFCURSOR)
RETURN NUMBER;
FUNCTION GET_PROFILER_REPORT (p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2)
RETURNPLSQL_PROFILER_T;
(1)GET_RROFILER_REPORT with NUMBER as the return type is recommended for use inthe program.
The return value provides the codecoverage (%) information and the out cursor point to the detailed profilerstatistics information which can be retrieved within the host program.
(2) GET_REPORT_REPORT with PLSQL_PROFILER_Tas the return type is quite easy to use in the SQL statement. For example, select* from TABLE(get_profiler_report(:runid, :unit_name));
The definition of type ‘PLSQL_PROFILER_T’ is as follows,
create or replace typePLSQL_PROFILER_T as TABLE OF PLSQL_PROFILER_O
And the definition of type ‘PLSQL_PROFILER_O’ is as follows,
create or replace typePLSQL_PROFILER_O as object
(
unit_name VARCHAR2(100),
line# NUMBER,
code_line VARCHAR2(1000),
total_time_ms NUMBER,
total_occur NUMBER,
min_time_ms NUMBER,
max_time_ms NUMBER
)
PROCEDURE GET_PROFILER_REPORT (p_result_cursor_in_out OUT SYS_REFCURSOR);
FUNCTION GET_PROFILER_REPORT RETURN PLSQL_PROFILER_T PIPELINED;
These two procedure/function is different from the above two functions in that thesetwo are used to view all the profiler statistics instead of a given run andunit’s statistics.
(1)GET_RROFILER_REPORT procedure (NOT FUNCTION) is recommended for use in theprogram.
The out cursor point to the detailed profiler statistics information which can be retrieved within the host program.
(2) GET_REPORT_REPORT with PLSQL_PROFILER_Tas the return type is quite easy to use in the SQL statement. For example, select* from TABLE(get_profiler_report);
3. Set Up Environment
As packageCIP_PLSQL_PROFILER is created on top of DBMS_PROFILER, it needs to set up theenvironment for DBMS_PROFILER beforehand.
The DBMS_PROFILER package is not automatically created during the default installation or creation of the database. To see whether the package DBMS_PROFILER is created or not, issue the following command in the SQLPLUScommand line,
SQL>desc DBMS_PROFILER
Ifyou get the error which says no such object exits, you need to create thepackage as the SYSDBA role using the script profload.sql which can be foundunder the directory $oracle_home\rdbms\admin.
The screenshot below illustrates this process,
After that, you need to create infrastructure tables used to store the profilerstatistics in the schema you are working on. Refer to script file proftab.sqlunder the directory $oracle_home\rdbms\admin to build up the followinginfrastructure tables:
(1) PLSQL_PROFILER_RUNS: Run-specific informationfor the PL/SQL profiler
(2) PLSQL_PROFILER_UNITS: Information about eachlibrary unit in a run
(3) PLSQL_PROFILER_DATA: Accumulated data from allprofiler runs
Besides,a sequence PLSQL_PROFILER_RUNNUMBER is also created to provide the run id.
The screenshot below illustrates this process,
So far, we complete setting up the environment for using package DBMS_PROFILER on which package MY_PLSQL_PROFILER is built up.
To make MY_PLSQL_PROFILER work, following objects needed to be created in current working schema,
(1) Table MY_PLSQL_PROFILER_LOOKUP: Join to MY_PLSQL_PROFILER_REPORT to get the statistics for a specific run and unit.
(2) Table MY_PLSQL_PROFILER_REPORT: Store the report statistics
(3) Sequence PLSQL_PROFILER_LOOKUP_SEQ: used as the primary key for MY_PLSQL_PROFILER_LOOKUP
(4) Type PLSQL_PROFILER_O: Used by type PLSQL_PROFILER_T
(5) TYPE PLSQL_PROFIELR_T: Used by package MY_PLSQL_PROFILER
To build up these objects in one round, run the scripts file buildup_my_plsql_profiler.sql in sqlplus,
SQL>@buildup_my_plsql_profiler.sql
The script is as below...
MY_PLSQL_PROFILER
----------------------------------------------
-- Created by yufr on 4/27/2009, 4:44:09 PM --
----------------------------------------------
prompt
prompt Droppting table MY_PLSQL_PROFILER_LOOKUP
prompt ========================================
prompt
drop table MY_PLSQL_PROFILER_LOOKUP cascade constraints purge;
prompt
prompt Creating table MY_PLSQL_PROFILER_LOOKUP
prompt ========================================
prompt
create table MY_PLSQL_PROFILER_LOOKUP
(
ID NUMBER not null,
RUN_ID NUMBER,
UNIT_NAME VARCHAR2(100),
BLOCK_TO_EXEC VARCHAR2(1000),
DATE_CREATED DATE default sysdate,
CODE_COVERAGE_PCT NUMBER
)
;
comment on column MY_PLSQL_PROFILER_LOOKUP.ID
is 'SEQ ID';
alter table MY_PLSQL_PROFILER_LOOKUP
add constraint PROFILER_LOOKUP_PK primary key (ID);
alter table MY_PLSQL_PROFILER_LOOKUP
add constraint PROFILER_LOOKUP_UK unique (RUN_ID, UNIT_NAME);
prompt
prompt Dropping table MY_PLSQL_PROFILER_REPORT
drop table MY_PLSQL_PROFILER_REPORT cascade constraints purge;
prompt ========================================
prompt
prompt Creating table MY_PLSQL_PROFILER_REPORT
prompt ========================================
prompt
create table MY_PLSQL_PROFILER_REPORT
(
REPORT_ID NUMBER not null,
LINE# NUMBER,
CODE_LINE VARCHAR2(1000),
TOTAL_TIME_MS NUMBER,
TOTAL_OCCUR NUMBER,
MIN_TIME_MS NUMBER,
MAX_TIME_MS NUMBER
)
;
alter table MY_PLSQL_PROFILER_REPORT
add constraint PROFILER_REPORT_FK foreign key (REPORT_ID)
references MY_PLSQL_PROFILER_LOOKUP (ID);
prompt
prompt Drop sequence PLSQL_PROFILER_LOOKUP_SEQ
prompt ===========================================
prompt
prompt Creating sequence PLSQL_PROFILER_LOOKUP_SEQ
prompt ===========================================
prompt
create sequence PLSQL_PROFILER_LOOKUP_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
prompt
prompt Creating type PLSQL_PROFILER_O
prompt ==============================
prompt
create or replace type PLSQL_PROFILER_O as object
(
unit_name VARCHAR2(100),
line# NUMBER,
code_line VARCHAR2(1000),
total_time_ms NUMBER,
total_occur NUMBER,
min_time_ms NUMBER,
max_time_ms NUMBER
)
/
prompt
prompt Creating type PLSQL_PROFILER_T
prompt ==============================
prompt
create or replace type PLSQL_PROFILER_T as TABLE OF PLSQL_PROFILER_O
/
prompt
prompt Creating package MY_PLSQL_PROFILER
prompt ===================================
prompt
CREATE OR REPLACE PACKAGE MY_PLSQL_PROFILER IS
-- Author : YUFR
-- Created : 4/17/2009 10:29:36 AM
-- Purpose : Capture the statistics of MY data mart pl/sql scripts
--G_CURRENT_USER CONSTANT VARCHAR2(30) := USER;
/*
==========================================================================================
The following two functions are used to get the profiler report for the given runid and unit_name.
Please note that the suffix "_L" suffix in the funcation name, which means "Low Lever". As these two
functions are directly based on the "lower level" --- PLSQL_PROFILER_XXX tables, in contrast to two
higher level tables -- MY_PLSQL_PROFILER_LOOKUP & MY_PLSQL_PROFILER_REPORT.
The reason why we bother creating two "duplicated" tables is because we want to keep the profiler history.
Suppose, if we refine the code someday, we cannot get the hisotical profiler statistics based on those
"lower table" because the data in table ALL_SOURCES is changed.
There two functions are actually not recommended for use, refere to GET_REPOFILER_REPORT as
the better solution.
==========================================================================================
*/
FUNCTION GET_PROFILER_REPORT_L(p_runid_in IN NUMBER, -- dbms_profiler's runid
p_unit_name_in IN VARCHAR2, -- the module (procedure/function) to track
p_result_cursor_in_out OUT SYS_REFCURSOR) -- result
RETURN NUMBER; -- return the code coverage
FUNCTION GET_PROFILER_REPORT_L(p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2)
RETURN PLSQL_PROFILER_T; --PIPELINED;
/*
The following two functions are used to get the profiler report for the given runid and unit_name.
These two functions are based on the higher level tables -- MY_PLSQL_PROFILER_LOOKUP and
MY_PLSQL_PROFILER_REPORT.
The reason why we bother creating two "duplicated" tables is because we want to keep the profiler history.
Suppose, if we refine the code someday, we cannot get the hisotical profiler statistics based on
those "lower table" because the data in table ALL_SOURCES is changed.
==========================================================================================
(1) GET_RROFILER_REPORT with NUMBER as the return type is recommended for use in the program.
The return value provides the code coverage (%) information and the out cursor point to the
detailed profiler statistics information which can be retrieved within the host program.
(2) GET_REPORT_REPORT with PLSQL_PROFILER_T as the return type is quite easy to use in the SQL
statement. For example,
select * from TABLE(get_profiler_report(:runid, :unit_name));
==========================================================================================
*/
FUNCTION GET_PROFILER_REPORT(p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2,
p_result_cursor_in_out OUT SYS_REFCURSOR)
RETURN NUMBER;
FUNCTION GET_PROFILER_REPORT(p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2)
RETURN PLSQL_PROFILER_T; -- PIPELINED;
/*
The following function/procedure are used to get all the historical profiler report.
These two functions are based on the higher level tables -- MY_PLSQL_PROFILER_LOOKUP and
MY_PLSQL_PROFILER_REPORT.
The reason why we bother creating two "duplicated" tables is because we want to keep the profiler history.
Suppose, if we refine the code someday, we cannot get the hisotical profiler statistics based on
those "lower table" because the data in table ALL_SOURCES is changed.
==========================================================================================
(1) GET_RROFILER_REPORT procedure (NOT FUNCTION) is recommended for use in the program.
The out cursor point to the detailed profiler statistics information which can be retrieved
within the host program.
(2) GET_REPORT_REPORT with PLSQL_PROFILER_T as the return type is quite easy to use in the SQL
statement. For example,
select * from TABLE(get_profiler_report);
==========================================================================================
*/
PROCEDURE GET_PROFILER_REPORT(p_result_cursor_in_out OUT SYS_REFCURSOR);
FUNCTION GET_PROFILER_REPORT RETURN PLSQL_PROFILER_T PIPELINED;
/*
@paramter p_unit_name_in is the name of procedure/function/package you want to make profile on
@p_module_block_in is the code block to call the procedure/function/package
Use Cases
Below is an example of how to call this procedure.
SEND_MAIL is the procedure to capture satistics on using DBMS_PROFILER.
===========================================================================================
DECLARE
v_unit_name VARCHAR(30) := 'SEND_MAIL'
v_block VARCHAR2(100) := q'[SEND_MAIL('frank.yu@test.com', 'frank.yu@test.com', 'test', 'test')]';
BEGIN
MY_DM_PROFILER.CAPTURE_MODULE_STATS(v_unit_name, v_block);
END;
============================================================================================
Use Case 1: (Profiling procedure TEST_PROFILER_PROCEDURE)
SQL> variable runid number;
SQL> exec :runid := MY_plsql_profiler.capture_module_stats('test_profiler_procedure', 'test_profiler_procedure');
Use Case 2: (Profiling function TEST_PROFILER_FUNCTION)
SQL> variable runid number;
SQL> variable block_to_exec varchar2(100);
SQL> exec :block_to_exec := 'declare l_result number; begin l_result := test_profiler_function; end';
SQL> exec :runid := MY_plsql_profiler.capture_module_stats('TEST_PROFILER_FUNCTION',:block_to_exec);
Use Case 3: (Profiling package TEST_PROFILER_PACKAGE)
SQL> variable runid number;
SQL> exec :runid := MY_plsql_profiler.capture_module_stats('test_profiler_package', 'test_profiler_package.test_profiler_procedure');
*/
FUNCTION CAPTURE_MODULE_STATS(p_unit_name_in IN VARCHAR2,
p_module_block_in IN VARCHAR2) -- module to execute
RETURN NUMBER;
END MY_PLSQL_PROFILER;
/
prompt
prompt Creating package body MY_PLSQL_PROFILER
prompt ========================================
prompt
CREATE OR REPLACE PACKAGE BODY MY_PLSQL_PROFILER IS
G_ANONYMOUSE_UNIT VARCHAR2(11) := '<anonymous>';
FUNCTION GET_PROFILER_REPORT_L
(p_runid_in IN NUMBER, -- dbms_profiler's runid
p_unit_name_in IN VARCHAR2, -- the module (package or standalone procedure/function) to track
p_result_cursor_in_out OUT SYS_REFCURSOR -- result
)
RETURN NUMBER -- return the code coverage
IS
l_return_val NUMBER;
BEGIN
IF p_result_cursor_in_out%ISOPEN THEN
CLOSE p_result_cursor_in_out;
END IF;
-- Code execution detailed report
OPEN p_result_cursor_in_out FOR
SELECT
u.unit_name,
d.line#,
s.text AS code_line,
d.total_time/1000000 AS "total_time(ms)",
d.total_occur,
d.min_time/1000000 AS "min_time(ms)",
d.max_time/1000000 AS "max_time(ms)"
FROM
plsql_profiler_data d,
plsql_profiler_units u,
all_source s
WHERE
u.runid = p_runid_in
AND u.unit_name = UPPER(p_unit_name_in)
AND d.runid = u.runid
AND d.unit_number = u.unit_number
AND d.total_occur <> 0
AND s.TYPE(+) = u.unit_type
AND s.owner(+) = u.unit_owner
AND s.name(+) = u.unit_name
AND d.line# = NVL (s.line, d.line#)
ORDER BY
d.line#;
-- Code coverage calculation
SELECT
actual_exec.cnt/total_exec.cnt * 100 AS "code(%) coverage" INTO l_return_val
FROM
(
SELECT
COUNT(d.line#) AS cnt
FROM
plsql_profiler_data d,
plsql_profiler_units u
WHERE
u.runid = p_runid_in AND
u.unit_name = NVL(UPPER(p_unit_name_in),G_ANONYMOUSE_UNIT) AND
d.runid = u.runid AND
d.unit_number = u.unit_number
) total_exec,
(
SELECT
COUNT(d.line#) AS cnt
FROM
plsql_profiler_data d,
plsql_profiler_units u
WHERE
u.runid = p_runid_in AND
u.unit_name = NVL(UPPER(p_unit_name_in),G_ANONYMOUSE_UNIT) AND
d.runid = u.runid AND
d.unit_number = u.unit_number AND
d.total_occur > 0
)actual_exec;
RETURN l_return_val;
END GET_PROFILER_REPORT_L;
FUNCTION GET_PROFILER_REPORT_L
(p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2)
RETURN PLSQL_PROFILER_T --PIPELINED
IS
CURSOR profiler(run_id NUMBER, module_name VARCHAR2) IS
SELECT
PLSQL_PROFILER_O
(
u.unit_name,
d.line#,
s.text,
d.total_time/1000000,
d.total_occur,
d.min_time/1000000,
d.max_time/1000000
)
FROM
plsql_profiler_data d,
plsql_profiler_units u,
all_source s
WHERE
u.runid = run_id
AND u.unit_name = NVL(UPPER(module_name),G_ANONYMOUSE_UNIT)
AND d.runid = u.runid
AND d.unit_number = u.unit_number
AND d.total_occur <> 0
AND s.TYPE(+) = u.unit_type
AND s.owner(+) = u.unit_owner
AND s.name(+) = u.unit_name
AND d.line# = NVL (s.line, d.line#)
ORDER BY
d.line#;
--profiler_rec profiler%ROWTYPE;
profiler_rec PLSQL_PROFILER_T := PLSQL_PROFILER_T();
BEGIN
OPEN profiler(p_runid_in, p_unit_name_in);
FETCH profiler BULK COLLECT INTO profiler_rec;
CLOSE profiler;
RETURN profiler_rec;
/*OPEN profiler(p_runid_in, p_unit_name_in);
LOOP
FETCH profiler INTO profiler_rec;
EXIT WHEN profiler%NOTFOUND;
PIPE ROW(plsql_profiler_o(profiler_rec.unit_name,
profiler_rec.line#,
profiler_rec.code_line,
profiler_rec."total_time(ms)",
profiler_rec.total_occur,
profiler_rec."min_time(ms)",
profiler_rec."max_time(ms)"));
END LOOP;
CLOSE profiler;
RETURN;*/
END GET_PROFILER_REPORT_L;
FUNCTION GET_PROFILER_REPORT(p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2,
p_result_cursor_in_out OUT SYS_REFCURSOR)
RETURN NUMBER
IS
l_return_val NUMBER;
BEGIN
IF p_result_cursor_in_out%ISOPEN THEN
CLOSE p_result_cursor_in_out;
END IF;
-- Code execution detailed report
OPEN p_result_cursor_in_out FOR
SELECT
l.unit_name,
r.line#,
r.code_line,
r.total_time_ms,
r.total_occur,
r.min_time_ms,
r.max_time_ms
FROM
MY_plsql_profiler_lookup l,
MY_plsql_profiler_report r
WHERE
l.run_id = p_runid_in AND
l.unit_name = NVL(upper(p_unit_name_in), G_ANONYMOUSE_UNIT) AND
l.id = r.report_id
ORDER BY
r.line#;
-- Code coverage calculation
SELECT
nvl(code_coverage_pct, -1) INTO l_return_val
FROM
MY_plsql_profiler_lookup
WHERE
run_id = p_runid_in AND
unit_name = upper(p_unit_name_in);
RETURN l_return_val;
END GET_PROFILER_REPORT;
FUNCTION GET_PROFILER_REPORT(p_runid_in IN NUMBER,
p_unit_name_in IN VARCHAR2)
RETURN PLSQL_PROFILER_T --PIPELINED
IS
CURSOR profiler(runid NUMBER, module_name VARCHAR2) IS
SELECT
PLSQL_PROFILER_O
(
l.unit_name,
r.line#,
r.code_line,
r.total_time_ms,
r.total_occur,
r.min_time_ms,
r.max_time_ms
)
FROM
MY_plsql_profiler_lookup l,
MY_plsql_profiler_report r
WHERE
l.run_id = runid AND
l.unit_name = NVL(upper(module_name), G_ANONYMOUSE_UNIT) AND
l.id = r.report_id
ORDER BY
r.line#;
--profiler_rec profiler%ROWTYPE;
profiler_rec PLSQL_PROFILER_T := PLSQL_PROFILER_T();
BEGIN
OPEN profiler(p_runid_in, p_unit_name_in);
FETCH profiler BULK COLLECT INTO profiler_rec;
CLOSE profiler;
RETURN profiler_rec;
/*OPEN profiler(p_runid_in, p_unit_name_in);
LOOP
FETCH profiler INTO profiler_rec;
EXIT WHEN profiler%NOTFOUND;
PIPE ROW(plsql_profiler_o(profiler_rec.unit_name,
profiler_rec.line#,
profiler_rec.code_line,
profiler_rec.total_time_ms,
profiler_rec.total_occur,
profiler_rec.min_time_ms,
profiler_rec.max_time_ms));
END LOOP;
CLOSE profiler;
RETURN;*/
END GET_PROFILER_REPORT;
PROCEDURE GET_PROFILER_REPORT(p_result_cursor_in_out OUT SYS_REFCURSOR)
IS
BEGIN
IF p_result_cursor_in_out%ISOPEN THEN
CLOSE p_result_cursor_in_out;
END IF;
-- Code execution detailed report
OPEN p_result_cursor_in_out FOR
SELECT
l.unit_name,
r.line#,
r.code_line,
r.total_time_ms,
r.total_occur,
r.min_time_ms,
r.max_time_ms
FROM
MY_plsql_profiler_lookup l,
MY_plsql_profiler_report r
WHERE
l.id = r.report_id
ORDER BY
r.report_id, l.unit_name, r.line#;
END GET_PROFILER_REPORT;
FUNCTION GET_PROFILER_REPORT RETURN PLSQL_PROFILER_T PIPELINED
IS
BEGIN
FOR profiler IN (SELECT
l.unit_name,
r.line#,
r.code_line,
r.total_time_ms,
r.total_occur,
r.min_time_ms,
r.max_time_ms
FROM
MY_plsql_profiler_lookup l,
MY_plsql_profiler_report r
WHERE
l.id = r.report_id
ORDER BY
r.report_id,l.unit_name, r.line#)
LOOP
PIPE ROW(plsql_profiler_o(profiler.unit_name,
profiler.line#,
profiler.code_line,
profiler.total_time_ms,
profiler.total_occur,
profiler.min_time_ms,
profiler.max_time_ms));
END LOOP;
RETURN;
END GET_PROFILER_REPORT;
FUNCTION CAPTURE_MODULE_STATS(p_unit_name_in IN VARCHAR2,
p_module_block_in IN VARCHAR2) RETURN NUMBER -- module to execute
IS
-- Current transaction won't impact the transaction in p_module_block_in
PRAGMA AUTONOMOUS_TRANSACTION;
l_runid NUMBER;
l_report_id NUMBER;
l_unit_name MY_plsql_profiler_lookup.unit_name%TYPE := NVL(UPPER(p_unit_name_in), G_ANONYMOUSE_UNIT);
BEGIN
-- Succeed in starting profiling
IF dbms_profiler.start_profiler(run_number => l_runid) = 0
THEN
-- Execute the module (procedure/function)
EXECUTE IMMEDIATE 'BEGIN ' || RTRIM(p_module_block_in, ';') || '; END;';
-- Succeed in both stopping profiler and flushing data profiling data
IF dbms_profiler.stop_profiler = 0 AND dbms_profiler.flush_data = 0
THEN
INSERT INTO MY_plsql_profiler_lookup(id, run_id, unit_name, block_to_exec)
VALUES(PLSQL_PROFILER_LOOKUP_SEQ.nextval,l_runid, l_unit_name, p_module_block_in)
RETURNING id INTO l_report_id;
-- Code execution detailed report
INSERT/*+ append nologging*/ INTO MY_plsql_profiler_report
(
report_id,
line#,
code_line,
total_time_ms,
total_occur,
min_time_ms,
max_time_ms
)
SELECT
l_report_id,
d.line#,
s.text AS code_line,
d.total_time/1000000 AS "total_time(ms)",
d.total_occur,
d.min_time/1000000 AS "min_time(ms)",
d.max_time/1000000 AS "max_time(ms)"
FROM
plsql_profiler_data d,
plsql_profiler_units u,
all_source s
WHERE
u.runid = l_runid
AND u.unit_name = l_unit_name
AND d.runid = u.runid
AND d.unit_number = u.unit_number
AND d.total_occur <> 0
AND s.TYPE(+) = u.unit_type
AND s.owner(+) = u.unit_owner
AND s.name(+) = u.unit_name
AND d.line# = NVL (s.line, d.line#)
ORDER BY
d.line#;
-- Code coverage calculation
UPDATE MY_plsql_profiler_lookup
SET code_coverage_pct =
(
SELECT
actual_exec.cnt/decode(total_exec.cnt, 0, NULL, total_exec.cnt) * 100 AS "code(%) coverage"
FROM
(
SELECT
COUNT(d.line#) AS cnt
FROM
plsql_profiler_data d,
plsql_profiler_units u
WHERE
u.runid = l_runid AND
u.unit_name = l_unit_name AND
d.runid = u.runid AND
d.unit_number = u.unit_number
) total_exec,
(
SELECT
COUNT(d.line#) AS cnt
FROM
plsql_profiler_data d,
plsql_profiler_units u
WHERE
u.runid = l_runid AND
u.unit_name = l_unit_name AND
d.runid = u.runid AND
d.unit_number = u.unit_number AND
d.total_occur > 0
)actual_exec
)
WHERE id = l_report_id;
COMMIT;
RETURN l_runid;
END IF;
-- If some erros occurs during the profiling, return -1
ELSE
RETURN -1;
END IF;
END CAPTURE_MODULE_STATS;
END MY_PLSQL_PROFILER;
/
4. Use Cases
This section is intended to get your feet wet before starting off using the package MY_PLSQL_PROFILER.
The following test cases are based on the procedure“TEST_PROFILER_PROCEDURE”, function “TEST_PROFILER_FUNCTION” and package“TEST_PROFILER_PACKAGE”.
-- TEST_PROFILER_PROCEDURE
Create or replace procedureTEST_PROFILER_PROCEDURE is
BEGIN
FOR i IN 1..10
LOOP
dbms_output.put_line('hello world' || i);
END LOOP;
End TEST_PROFILER_PROCEDURE;
-- TEST_PROFILER_FUNCTION
Create or replace functionTEST_PROFILER_FUNCTION return NUMBER is
Result NUMBER := 0;
Begin
FOR i IN 1..10
LOOP
dbms_output.put_line('hello world' || i);
RESULT := RESULT + i;
END LOOP;
Return (Result);
End TEST_PROFILER_FUNCTION;
-- TEST_PROFILER_PACKAGE
create or replace package TEST_PROFILER_PACKAGEis
-- Author : YUFR
-- Created : 4/20/2009 5:22:07 PM
-- Purpose : Just for test
functionTEST_PROFILER_FUNCTION return NUMBER;
procedureTEST_PROFILER_PROCEDURE;
end TEST_PROFILER_PACKAGE;
4.1. Test Case 1: Procedure (TEST_PROFILER_PROCEDURE)
SQL>variable runid number;
SQL>exec :runid := my_plsql_profiler.capture_module_stats('test_profiler_procedure','test_profiler_procedure');
PL/SQLprocedure successfully completed
runid
---------
4
SQL>select * from table(my_plsql_profiler.get_profiler_report(4, 'test_profiler_procedure'));
|
UNIT_NAME |
LINE# |
CODE_LINE |
TOTAL_TIME_MS |
TOTAL_OCCUR |
MIN_TIME_MS |
MAX_TIME_MS |
|
TEST_PROFILER_PROCEDURE |
1 |
procedure TEST_PROFILER_PROCEDURE is |
0.0055 |
1 |
0.0055 |
0.0055 |
|
TEST_PROFILER_PROCEDURE |
2 |
BEGIN |
0 |
1 |
0 |
0 |
|
TEST_PROFILER_PROCEDURE |
4 |
FOR i IN 1..10 |
0.0042 |
11 |
0.0001 |
0.0012 |
|
TEST_PROFILER_PROCEDURE |
6 |
dbms_output.put_line('hello world ' || i); |
0.1526 |
10 |
0.0011 |
0.136 |
|
TEST_PROFILER_PROCEDURE |
7 |
END LOOP; |
0 |
1 |
0 |
0 |
|
TEST_PROFILER_PROCEDURE |
9 |
end TEST_PROFILER_PROCEDURE; |
0.0004 |
1 |
0.0004 |
0.0004 |
4.2.Test Case 2: Function (TEST_RPOFILER_FUNCTION)
SQL>variable runid number;
SQL>variable block_to_exec varchar2(100);
SQL>exec :block_to_exec := 'declare l_result number; begin l_result :=test_profiler_function; end';
SQL>exec :runid := my_plsql_profiler.capture_module_stats('TEST_PROFILER_FUNCTION',:block_to_exec);
PL/SQL procedure successfully completed
runid
---------
6
SQL> select* from table(my_plsql_profiler.get_profiler_report(6, 'test_profiler_function'));
|
UNIT_NAME |
LINE# |
CODE_LINE |
TOTAL_TIME _MS |
TOTAL _OCCUR |
MIN_TIME _MS |
MAX_TIME _MS |
|
TEST_PROFILER_FUNCTION |
1 |
function TEST_PROFILER_FUNCTION return NUMBER is |
0.0039 |
1 |
0.0039 |
0.0039 |
|
TEST_PROFILER_FUNCTION |
2 |
Result NUMBER := 0; |
0.0012 |
1 |
0.0012 |
0.0012 |
|
TEST_PROFILER_FUNCTION |
3 |
begin |
0 |
1 |
0 |
0 |
|
TEST_PROFILER_FUNCTION |
5 |
FOR i IN 1..10 |
0.0037 |
11 |
0.0002 |
0.0011 |
|
TEST_PROFILER_FUNCTION |
7 |
dbms_output.put_line('hello world ' || i); |
0.2235 |
10 |
0.0013 |
0.2053 |
|
TEST_PROFILER_FUNCTION |
8 |
RESULT := RESULT + i; |
0.0082 |
10 |
0.0005 |
0.0021 |
|
TEST_PROFILER_FUNCTION |
9 |
END LOOP; |
0 |
1 |
0 |
0 |
|
TEST_PROFILER_FUNCTION |
11 |
return(Result); |
0.0002 |
1 |
0.0002 |
0.0002 |
|
TEST_PROFILER_FUNCTION |
12 |
end TEST_PROFILER_FUNCTION; |
0.0007 |
1 |
0.0007 |
0.0007 |
4.3. Test Case 3: Package (TEST_PROFILER_PACKAGE)
SQL>variable runid number;
SQL>exec :runid := my_plsql_profiler.capture_module_stats('test_profiler_package','test_profiler_package.test_profiler_procedure');
PL/SQL procedure successfully completed
runid
---------
7
SQL>select * from table(my_plsql_profiler.get_profiler_report(7, 'test_profiler_package'));
|
UNIT_NAME |
LINE# |
CODE_LINE |
TOTAL_TIME _MS |
TOTAL _OCCUR |
MIN_TIME _MS |
MAX_TIME _MS |
|
TEST_PROFILER_PACKAGE |
16 |
procedure TEST_PROFILER_PROCEDURE is |
0.0039 |
1 |
0.0039 |
0.0039 |
|
TEST_PROFILER_PACKAGE |
17 |
BEGIN |
0 |
1 |
0 |
0 |
|
TEST_PROFILER_PACKAGE |
19 |
FOR i IN 1..10 |
0.0037 |
11 |
0.0002 |
0.0009 |
|
TEST_PROFILER_PACKAGE |
21 |
dbms_output.put_line('hello world ' || i); |
0.1062 |
10 |
0.0013 |
0.0909 |
|
TEST_PROFILER_PACKAGE |
22 |
END LOOP; |
0 |
1 |
0 |
0 |
|
TEST_PROFILER_PACKAGE |
24 |
end TEST_PROFILER_PROCEDURE; |
0.0006 |
1 |
0.0006 |
0.0006 |
4.4 View all the historical statistics
SQL> select * from table(my_plsql_profiler.get_profiler_report);
5. References
Oracle’s DBMS_PROFILER: PL/SQL Performance Tuning written by Amar Kumar Padhi
