run_stats---sql效率測試工具(轉)

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
/*
統計工具三
需要的許可權：
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記得要設定serveroutput。
使用方法：
1)比較多段程式碼的資源耗費情況
BEGIN
PKG_STAT.init;

PKG_STAT.Mark;--通過dbms_output輸出從上次init/print到現在為止本session所消耗的資源

PKG_STAT.Mark;
....
PKG_STAT.LAST;--生成統計
END;
2)統計一段或多段不相關的程式碼的資源耗費情況
BEGIN
PKG_STAT.init; --只需要初始化一次

PKG_STAT.print1;--也可以寫成Mark(1)

PKG_STAT.print1;
....
PKG_STAT.print; --執行print則會生成上面所有mark過的統計的比較資訊，可省略
END;
*/
--初始化,1表示統計v$mystat & v$sess_io,2表示統計v$latch,3表示前兩個都統計
PROCEDURE Init(p_BitFlag INT := 1);
--建立統計點，p_IsPrint=1表示直接輸出該步的統計資訊
PROCEDURE Mark(p_IsPrint PLS_INTEGER := 0, p_Marker VARCHAR2 := NULL);
--等同於Mark(1)
PROCEDURE print1(p_Marker VARCHAR2 := NULL);
--生成所有統計資訊，用在最後一步
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統計精度為0.0001秒，32位Oracle則只會有0.001
FUNCTION getTime RETURN PLS_INTEGER IS
BEGIN
RETURN TO_CHAR(SYSTIMESTAMP, 'SSSSSFF4');
END;
--生成統計資料並賦值
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)*/ --貌似有點重複了
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統計的，資料不大準，只統計下列項
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#)設定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);
--開始記錄負責執行統計的SQL所耗費的資源
--在生成統計圖時將這部分額外耗費的資源減掉
--多次執行以獲得平均值
l_Timest := getTime;
build(l_Old);
build(l_New);
build(l_New);
--計算統計SQL的耗費時間
l_TimeOffset := (getTime - l_Timest) / 3;
--計算統計SQL的耗費的其他資源
--new-old得到的是兩次資源耗費，因為上面初始了兩次new
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);
--計算耗費時間
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) -------------';
--計算耗費資源
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;
/