Hi All,
Orcl Ver: 11g
I ran the DBMS_PROFILER. But it isn't generating the report properly.
Here's what I'm doing,
BEGIN
DBMS_PROFILER.START_PROFILER('Start of Shanks run');
proc1;
proc2;
proc3;
proc4;
DBMS_PROFILER.STOP_PROFILER();
END;
Sample procedures,
create or replace PROCEDURE proc1 AS
BEGIN
FOR i IN (SELECT level from dual connect by level < 10)
LOOP
DBMS\_LOCK.SLEEP( 5 );
END LOOP;
END;
create or replace PROCEDURE proc2 AS
BEGIN
FOR i IN (SELECT level from dual connect by level < 10)
LOOP
DBMS\_LOCK.SLEEP( 2 );
END LOOP;
END;
create or replace PROCEDURE proc3 AS
BEGIN
FOR i IN (SELECT level from dual connect by level < 10)
LOOP
DBMS\_LOCK.SLEEP( 10 );
END LOOP;
END;
create or replace PROCEDURE proc4 AS
BEGIN
FOR i IN (SELECT level from dual connect by level < 10)
LOOP
NULL;
END LOOP;
END;
Generated report,
Only the profiler_runs info is displayed and rest of the info is missing.
But I could see the data in all the 3 tables for runid = 8.
This is the file I'm running to generate the report.
/* $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 = 30;
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-align:left;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
PRO .OraTableCellNumber {font-family:Monospace,Arial,Helvetica,Geneva,sans-serif;font-size:10pt;text-align:right;background-color:#f7f7e7;color:#000000;vertical-align:baseline}
PRO </style></head><body class="OraBody">
PRO
PRO <h1 class="OraHeader">profiler.sql - PL/SQL Profiler
SET DEF '~';
PRO <font size="1"><i>(MetaLink Note:<a target="_blank" href="http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=243755.1">243755.1</a>)</i></font></h1>
SET DEF ON;
PRO
PRO <h2 class="OraTableTitle">Profiled Run &&p_runid (plsql_profiler_runs)</h2>
PRO <table class="OraTable" border="0" cellspacing="1">
PRO <tr>
PRO <th class="OraTableColumnHeaderNumber">Run</th>
PRO <th class="OraTableColumnHeader">Date</th>
PRO <th class="OraTableColumnHeaderNumber">Total Time<sup>1</sup></th>
PRO <th class="OraTableColumnHeader">Comment</th>
PRO </tr>
SELECT '<tr>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(runid)||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(run\_date,'DD-MON-RR HH24:MI:SS')||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(ROUND(run\_total\_time/:v\_time,2),'FM9999999999990.00')||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||run\_comment||'\</td>'||CHR(10)||
'\</tr>'
FROM plsql_profiler_runs
WHERE runid = :v_runid;
PRO </table>
PRO <font size="1">Note 1: Total Time is in seconds</font>
PRO <h2 class="OraTableTitle">Profiled PL/SQL Libraries (plsql_profiler_units)</h2>
PRO <table class="OraTable" border="0" cellspacing="1">
PRO <tr>
PRO <th class="OraTableColumnHeaderNumber">Unit</th>
PRO <th class="OraTableColumnHeader">Owner</th>
PRO <th class="OraTableColumnHeader">Name</th>
PRO <th class="OraTableColumnHeader">Type</th>
PRO <th class="OraTableColumnHeader">Timestamp</th>
PRO <th class="OraTableColumnHeaderNumber">Total Time<sup>1</sup></th>
PRO <th class="OraTableColumnHeader">Text Header</th>
PRO </tr>
SET DEF '~';
SELECT '<tr>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(u.unit\_number)||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||u.unit\_owner||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||
DECODE(u.spare1,NULL,u.unit\_name,
'\<a href="#UNIT\_'||TO\_CHAR(u.unit\_number)||'">'||u.unit\_name||'\</a>')||
'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||u.unit\_type||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||
DECODE(u.unit\_timestamp,NULL,NULL,TO\_CHAR(u.unit\_timestamp,'DD-MON-RR HH24:MI:SS'))||
'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(ROUND(u.total\_time/:v\_time,2),'FM9999999999990.00')||'\</td>'||CHR(10)||
'\<td class="OraTableCellLeft">'||
( SELECT REPLACE(REPLACE(s.text,'>','>'),'\<','<')
FROM all\_source s
WHERE s.owner = u.unit\_owner
AND s.name = u.unit\_name
AND s.type = u.unit\_type
AND s.line \< 51
AND UPPER(s.text) LIKE ('%$%HEADER%')
AND ROWNUM = 1 )||'\</td>'||CHR(10)||
'\</tr>'
FROM plsql_profiler_units u
WHERE u.runid = :v_runid
AND ( u.unit_type <> 'ANONYMOUS BLOCK'
OR ( u.unit\_type = 'ANONYMOUS BLOCK'
AND ROUND(u.total\_time/:v\_time,2) > 0.00 ))
ORDER BY
u.unit\_number;
SET DEF ON;
PRO </table>
PRO <font size="1">Note 1: Total Time is in seconds</font>
PRO <h2 class="OraTableTitle">Top &&p_top profiled source lines in terms of Total Time (plsql_profiler_data)</h2>
PRO <table class="OraTable" border="0" cellspacing="1">
PRO <tr>
PRO <th class="OraTableColumnHeaderNumber">Top</th>
PRO <th class="OraTableColumnHeaderNumber">Total Time<sup>1</sup></th>
PRO <th class="OraTableColumnHeaderNumber">Times Executed</th>
PRO <th class="OraTableColumnHeaderNumber">Min Time<sup>2</sup></th>
PRO <th class="OraTableColumnHeaderNumber">Max Time<sup>2</sup></th>
PRO <th class="OraTableColumnHeaderNumber">Unit</th>
PRO <th class="OraTableColumnHeader">Owner</th>
PRO <th class="OraTableColumnHeader">Name</th>
PRO <th class="OraTableColumnHeader">Type</th>
PRO <th class="OraTableColumnHeaderNumber">Line</th>
PRO <th class="OraTableColumnHeader">Text</th>
PRO </tr>
SET DEF '~';
SELECT '<tr>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(d.spare1)||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(ROUND(d.total\_time/:v\_time,2),'FM9999999999990.00')||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(d.total\_occur)||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(ROUND(d.min\_time/:v\_time,2),'FM9999999999990.00')||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(ROUND(d.max\_time/:v\_time,2),'FM9999999999990.00')||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||TO\_CHAR(d.unit\_number)||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||u.unit\_owner||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||u.unit\_name||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||u.unit\_type||'\</td>'||CHR(10)||
'\<td class="OraTableCellCenter">'||
DECODE(u.unit\_type,'ANONYMOUS BLOCK',TO\_CHAR(d.line#),
'\<a href="#TOP\_'||TO\_CHAR(d.spare1)||'">'||
TO\_CHAR(d.line#)||'\</a>')||
'\</td>'||CHR(10)||
'\<td class="OraTableCellLeft">'||
( SELECT REPLACE(REPLACE(s.text,'>','>'),'\<','<')
FROM all\_source s
WHERE s.owner = u.unit\_owner
AND s.name = u.unit\_name
AND s.type = u.unit\_type
AND s.line = d.line# )||'\</td>'||CHR(10)||
'\</tr>'
FROM plsql_profiler_data d,
plsql\_profiler\_units u
WHERE d.runid = :v_runid
AND d.spare1 IS NOT NULL
AND u.runid = d.runid
AND u.unit_number = d.unit_number
ORDER BY
d.total\_time DESC;
SET DEF ON;
PRO </table>
PRO <font size="1">Note 1: Total Time is in seconds</font><br>
PRO <font size="1">Note 2: Min and Max Time for one execution of this line (in seconds)</font>
SET DEF '~';
DECLARE
l_total_time NUMBER;
l_total_occur NUMBER;
l_anchor VARCHAR2(100);
CURSOR c1_units IS
SELECT unit\_number,
unit\_owner,
unit\_name,
unit\_type,
spare1,
spare2
FROM plsql\_profiler\_units
WHERE runid = :v\_runid
AND spare1 IS NOT NULL
ORDER BY
unit\_number;
CURSOR c2_source
( c_owner VARCHAR2,
c\_name VARCHAR2,
c\_type VARCHAR2,
c\_line\_min NUMBER,
c\_line\_max NUMBER ) IS
SELECT line,
SUBSTR(REPLACE(REPLACE(text,'>','>'),'\<','<'),1,220) text
FROM all\_source
WHERE owner = c\_owner
AND name = c\_name
AND type = c\_type
AND line BETWEEN c\_line\_min - 50
AND c\_line\_max + 50
ORDER BY
line;
CURSOR c3_data
( c_unit_number NUMBER,
c\_line NUMBER ) IS
SELECT spare1,
total\_time,
total\_occur
FROM plsql\_profiler\_data
WHERE runid = :v\_runid
AND unit\_number = c\_unit\_number
AND line# = c\_line; -- -4 carlos
BEGIN
FOR c1 IN c1_units LOOP
IF c1.unit\_type \<> 'ANONYMOUS BLOCK' THEN
DBMS\_OUTPUT.PUT\_LINE(
'\<h2 class="OraTableTitle">'||
'\<a name="UNIT\_'||TO\_CHAR(c1.unit\_number)||'">\</a>'||
'Unit:'||TO\_CHAR(c1.unit\_number)||' '||
c1.unit\_owner||'.'||c1.unit\_name||' '||
'(all\_source)\</h2>');
DBMS\_OUTPUT.PUT\_LINE('\<table class="OraTable" border="0" cellspacing="1">');
DBMS\_OUTPUT.PUT\_LINE('\<tr>');
DBMS\_OUTPUT.PUT\_LINE('\<th class="OraTableColumnHeaderNumber">Line\</th>');
DBMS\_OUTPUT.PUT\_LINE('\<th class="OraTableColumnHeaderNumber">Total Time\<sup>1\</sup>\</th>');
DBMS\_OUTPUT.PUT\_LINE('\<th class="OraTableColumnHeaderNumber">Times Executed\</th>');
DBMS\_OUTPUT.PUT\_LINE('\<th class="OraTableColumnHeader">Text\</th>');
DBMS\_OUTPUT.PUT\_LINE('\</tr>');
FOR c2 IN c2\_source(c1.unit\_owner, c1.unit\_name, c1.unit\_type, c1.spare1, c1.spare2) LOOP
l\_total\_time := NULL;
l\_total\_occur := NULL;
l\_anchor := NULL;
FOR c3 IN c3\_data(c1.unit\_number, c2.line) LOOP
l\_total\_time := c3.total\_time;
l\_total\_occur := c3.total\_occur;
IF c3.spare1 IS NOT NULL THEN
l\_anchor := '\<a name="TOP\_'||TO\_CHAR(c3.spare1)||'">\<sup>T'||TO\_CHAR(c3.spare1)||'\</sup>\</a>';
END IF;
END LOOP;
DBMS\_OUTPUT.PUT\_LINE('\<tr>');
DBMS\_OUTPUT.PUT\_LINE('\<td class="OraTableCellCenter">'||TO\_CHAR(c2.line)||l\_anchor||'\</td>');
DBMS\_OUTPUT.PUT\_LINE('\<td class="OraTableCellCenter">'||TO\_CHAR(ROUND(l\_total\_time/:v\_time,2),'FM9999999999990.00')||'\</td>');
DBMS\_OUTPUT.PUT\_LINE('\<td class="OraTableCellCenter">'||TO\_CHAR(l\_total\_occur)||'\</td>');
DBMS\_OUTPUT.PUT\_LINE(SUBSTR('\<td class="OraTableCellLeft">'||
REPLACE(LPAD(LTRIM(c2.text), LENGTH(c2.text), '\`'), '\`', ' ')||
'\</td>',1,255));
DBMS\_OUTPUT.PUT\_LINE('\</tr>');
END LOOP;
DBMS\_OUTPUT.PUT\_LINE('\</table>');
DBMS\_OUTPUT.PUT\_LINE('\<font size="1">Note 1: Total Time is in seconds\</font>\<br>');
DBMS\_OUTPUT.PUT\_LINE('\<font size="1">Note Tn: Top "n" Line in terms of Total Time\</font>');
END IF;
END LOOP;
END;
/
SET DEF ON;
PRO
PRO </body></html>
SPO OFF;
ROLLBACK;
UNDEF 1 p_top p_runid;
CLE COL;
SET TERM ON HEA ON PAGES 24 LIN 80 NUM 10 VER ON FEED 6 TRIMS OFF RECSEP WR SERVEROUT OFF ARRAY 15 DOC ON;
Can you please help me with it.
Thx,
Shan.