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;
<PL/SQL block>
PKG_STAT.Mark;--通过dbms_output输出从上次init/print到现在为止本session所消耗的资源
<PL/SQL block>
PKG_STAT.Mark;
....
PKG_STAT.LAST;--生成统计
END;
2)统计一段或多段不相关的代码的资源耗费情况
BEGIN
PKG_STAT.init; --只需要初始化一次
<PL/SQL block>
PKG_STAT.print1;--也可以写成Mark(1)
<PL/SQL block>
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;
/
