/* $Header: profiler.sql 243755.1 2007/01/16 10:55 csierra $ */
SET DOC OFF;
/*============================================================================+
| Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA |
| All rights reserved. |
+=============================================================================+
|
| FILENAME
|
| profiler.sql - Reporting PL/SQL Profiler data generated by DBMS_PROFILER
|
|
| USAGE
|
| The PL/SQL Profiler package DBMS_PROFILER generates performance data on any
| PL/SQL profiled library. This data includes execution time for every line
| of profiled line of code as well as number of times each line was executed
|
| This profiler.sql script generates an HTML report out of the tables that
| were populated by the DBMS_PROFILER package while profiling was active
|
| Connect into SQL*Plus as the application user which executed the actual
| profiler (APPS for Oracle Applications), and execute this script providing
| the run_id for the profiled transaction.
|
| SQL> START profiler.sql <p_runid>;
|
|
| DESCRIPTION
|
| The profiler.sql reports the results of the profiler stored into tables:
| PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS and PLSQL_PROFILER_DATA.
|
| This script can be used on databases with RDBMS 8.1.7 or higher and it is
| not constrained to Oracle Apps.
|
| This script profiler.sql requires one parameter:
|
| 1. p_runid: Corresponding to the runid for the profiled transaction
|
|
| NOTES
|
| This script is part of a set compressed into file PROF.zip. Latest version
| of PROF.zip can be downloaded from Note:243755.1
|
| Documentation on using the DBMS_PROFILER can be found at:
|
| http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_profil.htm#1003424
|
| Read Note:243755.1 for further details.
|
| For feedback, email author carlos.sierra@oracle.com
|
| HISTORY
|
| 2003/07/14 CSIERRA Created
| 2004/12/15 CSIERRA line# adjustments to compensate for offset reported on
| SR 4205230.995 and Bug 4044091
| 2007/01/10 CSIERRA XP (platform 7) has a different time granularity
| 2007/01/16 CSIERRA 9i does not have V$DATABASE.PLATFORM_ID
|
+============================================================================*/
REM Display of all runs for user to pick one to report
SELECT runid,
TO_CHAR(run_date,'DD-MON-RR HH24:MI:SS') run_date,
SUBSTR(run_comment,1,40) run_comment
FROM plsql_profiler_runs
ORDER BY
runid;
PRO
PRO Usage:
PRO sqlplus apps/<pwd>
PRO SQL> START profiler.sql <runid>;
PRO
DEF p_top = 10;
DEF p_runid = &1;
VAR v_runid NUMBER;
VAR v_top NUMBER;
VAR v_time NUMBER;
SET TERM OFF HEA OFF PAGES 50000 LIN 32000 NUM 14 VER OFF FEED OFF TRIMS ON RECSEP OFF SERVEROUT ON SIZE 1000000 ARRAY 100;
REM Assign parameters to bind variables
DECLARE
v_rec v$database%ROWTYPE;
BEGIN
SELECT * INTO v_rec FROM v$database;
IF v_rec.platform_id = 7 THEN
:v_time := 1000000000000;
END IF;
END;
/
BEGIN
:v_top := TO_NUMBER('&&p_top');
:v_runid := TO_NUMBER('&&p_runid');
IF NVL(:v_time, 0) = 0 THEN
:v_time := 1000000000;
END IF;
END;
/
REM Rollup of total time for library units with zero time (due to known issue)
DECLARE
CURSOR c1_units_zero_time IS
SELECT unit_number
FROM plsql_profiler_units
WHERE runid = :v_runid
AND total_time = 0;
BEGIN
FOR c1 IN c1_units_zero_time LOOP
DBMS_PROFILER.ROLLUP_UNIT(:v_runid,c1.unit_number);
END LOOP;
END;
/
REM line# adjustments to compensate for offset reported on SR 4205230.995 and Bug 4044091
DECLARE
offset NUMBER;
CURSOR c1_triggers IS
SELECT unit_owner, unit_name, unit_type, unit_number
FROM plsql_profiler_units
WHERE runid = :v_runid
AND unit_type = 'TRIGGER';
BEGIN
FOR c1 IN c1_triggers LOOP
SELECT NVL(MIN(line) - 1, -1)
INTO offset
FROM all_source
WHERE owner = c1.unit_owner
AND name = c1.unit_name
AND type = c1.unit_type
AND (UPPER(text) LIKE '%BEGIN%' OR UPPER(text) LIKE '%DECLARE%');
IF offset > 0 THEN
UPDATE plsql_profiler_data
SET line# = line# + offset
WHERE runid = :v_runid
AND unit_number = c1.unit_number;
END IF;
END LOOP;
END;
/
REM Finds the Top "p_top" most expensive lines in terms of total time and flags them on plsql_profiler_data.spare1
REM For each library unit which has one of the "p_top" lines, it records the min_line and max_line on plsql_profiler_units
DECLARE
l_rowcount NUMBER;
CURSOR c1_max_time IS
SELECT d.ROWID row_id
FROM plsql_profiler_units u,
plsql_profiler_data d
WHERE u.runid = :v_runid
--AND u.unit_owner <> 'SYS'
AND d.runid = u.runid
AND d.unit_number = u.unit_number
AND ROUND(d.total_time/:v_time,2) > 0.00
ORDER BY
d.total_time DESC;
CURSOR c2_range IS
SELECT unit_number, MIN(line#) min_line, MAX(line#) max_line
FROM plsql_profiler_data
WHERE runid = :v_runid
AND spare1 IS NOT NULL
GROUP BY
unit_number;
BEGIN
UPDATE plsql_profiler_data
SET spare1 = NULL
WHERE runid = :v_runid;
UPDATE plsql_profiler_units
SET spare1 = NULL,
spare2 = NULL
WHERE runid = :v_runid;
UPDATE plsql_profiler_units
SET unit_timestamp = NULL
WHERE runid = :v_runid
AND unit_timestamp < SYSDATE - 3652.5;
FOR c1 IN c1_max_time LOOP
l_rowcount := c1_max_time%ROWCOUNT;
IF l_rowcount = :v_top + 1 THEN
EXIT;
END IF;
UPDATE plsql_profiler_data
SET spare1 = l_rowcount
WHERE ROWID = c1.row_id;
END LOOP;
FOR c2 IN c2_range LOOP
UPDATE plsql_profiler_units
SET spare1 = c2.min_line,
spare2 = c2.max_line
WHERE runid = :v_runid
AND unit_number = c2.unit_number;
END LOOP;
END;
/
SPO profiler_&&p_runid..html;
PRO <html><head><title>profiler_&&p_runid..html</title>
PRO <style type="text/css">
PRO h1 { font-family:Arial,Helvetica,Geneva,sans-serif;font-size:16pt }
PRO h2 { font-family:Arial,Helvetica,Geneva,sans-serif;font-size:12pt }
PRO h3 { font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt }
PRO pre { font-family:Courier New,Geneva;font-size:8pt }
PRO .OraBody {background-color:#ffffff;font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt}
PRO .OraHeader {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:16pt;color:#336699}
PRO .OraHeaderSub {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:13pt;color:#336699;font-weight:bold}
PRO .OraHeaderSubSub {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:11pt;color:#336699;font-weight:bold}
PRO .OraTableTitle {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:13pt;background-color:#ffffff;color:#336699}
PRO .OraTable {background-color:#999966}
PRO .OraTableColumnHeader {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:center}
PRO .OraTableColumnHeaderNumber {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:10pt;font-weight:bold;background-color:#cccc99;color:#336699;vertical-align:bottom;text-align:center}
PRO .OraTableCellText {font-family:Monospace,Arial,Helvetica,Geneva,sans-serif;font-size:10pt;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
PRO .OraTableCellCenter {font-family:Monospace,Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text-align:center;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
PRO .OraTableCellLeft {font-family:Monospace,Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text
没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
收起资源包目录
PROF.zip (5个子文件)
prof
profiler_7.html 11KB
profgsrc.sql 3KB
profiler.sql 17KB
proftab.sql 5KB
NOTE_243755.html 11KB
共 5 条
- 1
资源评论
- 雍州无名2013-07-20是啊怎么用啊,老大说说啊
- hj207620032013-01-05三个sql脚本文件,二个html文件,到底怎么用呢,要是有个使用说明就好了
- Roceys2012-10-26找了很久了,老师给的好像跑不起来,就找这个试试。。希望能解决
- coolffe2013-01-30三个sql脚本文件,二个html文件,好像少了个profilerload.sql吧?
- angelior2019-02-25好用的。可以。
Dave
- 粉丝: 1w+
- 资源: 38
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功