create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/

create or replace package body runstats_pkg
as

g_start number;
g_run1 number;
g_run2 number;

procedure rs_start
is
begin
delete from run_stats;

insert into run_stats
select 'before', stats.* from stats;

g_start := dbms_utility.get_time;
end;

procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);

insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;

end;

procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);

dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
dbms_output.put_line( chr(9) );

insert into run_stats
select 'after 2', stats.* from stats;

dbms_output.put_line
( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, '999,999,999' ) ||
to_char( c.value-b.value, '999,999,999' ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
-- and (c.value-a.value) > 0
and abs( (c.value-b.value) - (b.value-a.value) )
> p_difference_threshold
order by abs( (c.value-b.value)-(b.value-a.value))
) loop
dbms_output.put_line( x.data );
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line
( 'Run1 latches total versus runs -- difference and pct' );
dbms_output.put_line
( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

for x in
( select to_char( run1, '999,999,999' ) ||
to_char( run2, '999,999,999' ) ||
to_char( diff, '999,999,999' ) ||
to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
sum( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and a.name like 'LATCH%'
)
) loop
dbms_output.put_line( x.data );
end loop;
end;

end;
/

/*
exec runStats_pkg.rs_start;
exec runStats_pkg.rs_middle;
exec runStats_pkg.rs_stop;
*/

and thats it. Here is an example of this at work: The issue of parsing...

/************************************************/

CREATE OR REPLACE PACKAGE PKG_STAT IS
/*
統(tǒng)計(jì)工具三
需要的權(quán)限：
grant select on v_$mystat to public;
grant select on v_$statname to public;
grant select on v_$sess_io to public;
grant select on v_$latch to public;
通過dbms_output輸出，所以SQL*PLUS記得要設(shè)置serveroutput。
使用方法：
1)比較多段代碼的資源耗費(fèi)情況
BEGIN
PKG_STAT.init;
<PL/SQL block>
PKG_STAT.Mark;--通過dbms_output輸出從上次init/print到現(xiàn)在為止本session所消耗的資源
<PL/SQL block>
PKG_STAT.Mark;
....
PKG_STAT.LAST;--生成統(tǒng)計(jì)
END;
2)統(tǒng)計(jì)一段或多段不相關(guān)的代碼的資源耗費(fèi)情況
BEGIN
PKG_STAT.init; --只需要初始化一次
<PL/SQL block>
PKG_STAT.print1;--也可以寫成Mark(1)
<PL/SQL block>
PKG_STAT.print1;
....
PKG_STAT.print; --執(zhí)行print則會(huì)生成上面所有mark過的統(tǒng)計(jì)的比較信息，可省略
END;
*/
--初始化,1表示統(tǒng)計(jì)v$mystat & v$sess_io,2表示統(tǒng)計(jì)v$latch,3表示前兩個(gè)都統(tǒng)計(jì)
PROCEDURE Init(p_BitFlag INT := 1);
--建立統(tǒng)計(jì)點(diǎn)，p_IsPrint=1表示直接輸出該步的統(tǒng)計(jì)信息
PROCEDURE Mark(p_IsPrint PLS_INTEGER := 0, p_Marker VARCHAR2 := NULL);
--等同于Mark(1)
PROCEDURE print1(p_Marker VARCHAR2 := NULL);
--生成所有統(tǒng)計(jì)信息，用在最后一步
PROCEDURE print;
--用在最后一步,等同于Mark+print
PROCEDURE LAST;
END PKG_STAT;
/
CREATE OR REPLACE PACKAGE BODY PKG_STAT IS
TYPE t IS RECORD(
ID INT,
NAME VARCHAR2(50),
VALUE INT);
TYPE t1 IS TABLE OF t INDEX BY PLS_INTEGER;
TYPE tShape IS TABLE OF T INDEX BY PLS_INTEGER;
TYPE T2 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE tStats IS TABLE OF T2 INDEX BY PLS_INTEGER;
TYPE tMsg IS TABLE OF VARCHAR2(400) INDEX BY VARCHAR2(50);
l_BaseSet t1;
l_Old tShape;
l_New tShape;
l_Stats tStats;
l_Index PLS_INTEGER;
l_Pattern VARCHAR2(2000);
l_Timest PLS_INTEGER;
l_BitFlag PLS_INTEGER;
l_TimeOffset PLS_INTEGER;
--64位Oracle統(tǒng)計(jì)精度為0.0001秒，32位Oracle則只會(huì)有0.001
FUNCTION getTime RETURN PLS_INTEGER IS
BEGIN
RETURN TO_CHAR(SYSTIMESTAMP, 'SSSSSFF4');
END;
--生成統(tǒng)計(jì)數(shù)據(jù)并賦值
PROCEDURE build(tResult OUT NOCOPY tShape) IS
BEGIN
SELECT /*+cache(a)*/
STATISTIC# ID, NULL, VALUE BULK COLLECT
INTO tResult
FROM v$mystat a
WHERE l_Pattern LIKE '%,' || STATISTIC# || ',%'
AND BitAnd(l_BitFlag, 1) > 0
UNION ALL
SELECT /*+cache(a)*/
500 + ROWNUM,
NULL,
decode(ROWNUM,
1,
block_gets,
2,
consistent_gets,
3,
physical_reads,
4,
block_changes,
consistent_changes)
FROM v$sess_io a, v$mystat b
WHERE a.sid = b.SID
AND ROWNUM <= 5
AND BitAnd(l_BitFlag, 1) > 0
UNION ALL
SELECT /*+cache(a)*/
600 + LATCH#, NULL, Gets
FROM v$latch a
WHERE l_Pattern LIKE '%,' || (600 + latch#) || ',%'
AND BitAnd(l_BitFlag, 2) > 0
ORDER BY 1;
END;

PROCEDURE Init(p_BitFlag INT := 1) IS
v_InitData t1;
BEGIN
dbms_output.enable(327670);
SELECT /*+cache(a)*/
a.STATISTIC#, ' STAT:' || NAME, 0 BULK COLLECT
INTO v_InitData
FROM v$mystat a, v$statname b
WHERE a.STATISTIC# = b.STATISTIC#
AND NAME IN
('consistent gets', 'consistent gets from cache',
'CPU used by this session', 'db block gets',
'db block gets from cache', 'index fast full scans (full)',
'parse count (hard)', 'parse count (total)', 'physical reads',
'physical writes', 'recursive calls', 'recursive cpu usage',
'redo size', 'redo writes', 'sorts (disk)', 'sorts (memory)',
'table fetch by rowid', 'table scan blocks gotten',
'table scans (long tables)', 'table scans (rowid ranges)',
'table scans (short tables)')
AND BitAnd(p_BitFlag, 1) > 0
UNION ALL
SELECT /*+cache(a)*/ --貌似有點(diǎn)重復(fù)了
500 + ROWNUM,
' I/O:' || decode(ROWNUM,
1,
'block_gets',
2,
'consistent_gets',
3,
'physical_reads',
4,
'block_changes',
'consistent_changes'),
0
FROM v$sess_io a, v$mystat b
WHERE a.sid = b.SID
AND ROWNUM <= 5
AND BitAnd(p_BitFlag, 1) > 0
UNION ALL
SELECT /*+cache(a)*/ --latch是不基于session統(tǒng)計(jì)的，數(shù)據(jù)不大準(zhǔn)，只統(tǒng)計(jì)下列項(xiàng)
600 + LATCH#, 'LATCH:' || NAME, 0
FROM v$latch a
WHERE NAME IN
('In memory undo latch', 'cache buffer handles',
'cache buffers chains', 'cache buffers lru chain',
'checkpoint queue latch', 'enqueues', 'library cache',
'library cache lock', 'library cache pin',
'object queue header heap', 'object queue header operation',
'parallel query alloc buffer', 'redo allocation', 'redo copy',
'redo writing', 'row cache objects', 'session allocation',
'shared pool', 'undo global data')
AND BitAnd(p_BitFlag, 2) > 0;
l_Pattern := ',';
l_Index := 0;
l_BitFlag := p_BitFlag;
l_BaseSet.DELETE;
--按記錄集的ID(statistic#,600+lath#)設(shè)置Key
FOR i IN 1 .. v_InitData.COUNT LOOP
l_Pattern := l_Pattern || v_InitData(i).ID || ',';
l_BaseSet(v_InitData(i).ID) := v_InitData(i);
END LOOP;
l_Stats.DELETE;
build(l_Old);
--開始記錄負(fù)責(zé)執(zhí)行統(tǒng)計(jì)的SQL所耗費(fèi)的資源
--在生成統(tǒng)計(jì)圖時(shí)將這部分額外耗費(fèi)的資源減掉
--多次執(zhí)行以獲得平均值
l_Timest := getTime;
build(l_Old);
build(l_New);
build(l_New);
--計(jì)算統(tǒng)計(jì)SQL的耗費(fèi)時(shí)間
l_TimeOffset := (getTime - l_Timest) / 3;
--計(jì)算統(tǒng)計(jì)SQL的耗費(fèi)的其他資源
--new-old得到的是兩次資源耗費(fèi)，因?yàn)樯厦娉跏剂藘纱蝞ew
FOR i IN 1 .. l_New.COUNT LOOP
l_BaseSet(l_New(i).ID).VALUE := l_New(i).VALUE - l_Old(i).VALUE;
END LOOP;
build(l_Old);
l_Timest := getTime;
END;

PROCEDURE doPrint(p_Set tMsg) IS
v_Key VARCHAR2(50);
BEGIN
--打印輸出
v_Key := p_Set.FIRST;
FOR j IN 1 .. p_Set.COUNT LOOP
dbms_output.put_line(p_Set(v_Key));
v_Key := p_Set.NEXT(v_Key);
END LOOP;
END;
--該過程適用于中間
PROCEDURE Mark(p_IsPrint PLS_INTEGER := 0, p_Marker VARCHAR2 := NULL) IS
v_Set tMsg;
v_ID INT;
v_Value VARCHAR2(20);
v_Base t;
BEGIN
IF l_Timest IS NULL THEN
Init;
RETURN;
END IF;
l_Index := l_Index + 1;
build(l_New);
--計(jì)算耗費(fèi)時(shí)間
l_Stats(l_Index)(0) := (getTime - l_Timest - l_TimeOffset) / 10000;
v_Set(' ') := '------------- Step ' || Nvl(p_Marker, l_Index) || '(' ||
l_Stats(l_Index) (0) || ' secs) -------------';
--計(jì)算耗費(fèi)資源
FOR i IN 1 .. l_New.COUNT LOOP
v_ID := l_New(i).Id;
v_Base := l_BaseSet(v_ID);
l_Stats(l_Index)(v_ID) := Greatest(l_New(i).VALUE - l_Old(i)
.VALUE - v_Base.VALUE,
0);
v_Value := rpad(l_Stats(l_Index) (v_ID), 9);
v_Set(v_Base.NAME) := v_Value || ' : ' || v_Base.NAME;
END LOOP;

IF p_IsPrint = 1 THEN
doPrint(v_Set);
END IF;

build(l_Old);
l_Timest := getTime;
END;

PROCEDURE print IS
v_Set tMsg;
v_Key VARCHAR2(50);
i PLS_INTEGER;
BEGIN
IF l_Stats.COUNT < 1 THEN
RETURN;
END IF;
l_BaseSet(0).NAME := ' #elapsed seconds';
i := 0;
FOR idx IN 1 .. l_Stats(1).COUNT LOOP
v_Key := l_BaseSet(i).NAME;
v_Set(v_Key) := '';
IF idx = 1 THEN
v_Set(' ') := '';
END IF;
FOR j IN 1 .. l_Stats.COUNT LOOP
v_Set(v_Key) := v_Set(v_Key) || rpad(l_Stats(j) (i), 10);
IF idx = 1 THEN
v_Set(' ') := v_Set(' ') || rpad('Step ' || j, 10, '-');
END IF;
END LOOP;
IF idx = 1 THEN
v_Set(' ') := v_Set(' ') || rpad('---Name', 35, '-');
END IF;
v_Set(v_Key) := v_Set(v_Key) || ': ' || v_Key;
i := l_stats(1).NEXT(i);
END LOOP;
l_Stats.DELETE;
l_Timest := NULL;
doPrint(v_Set);
END;

PROCEDURE print1(p_Marker VARCHAR2 := NULL) IS
BEGIN
Mark(1, p_Marker);
END;

PROCEDURE LAST IS
BEGIN
Mark;
print;
END;
END PKG_STAT;
/