Tom的一个比较SQL性能的小工具，就是在《9i10g编程艺术》里面第一章放的工具，今天重新看了一下，发现还是挺有用的，贴上来以后SQL tuning中可以使用一下。
-----------------------------------------------------------------------------------------------
set echo on
drop table run_stats;
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
grant select any table to ops$tkyte;
create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer;
delete from run_stats;
commit;
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;
sql_demo1;
exec runStats_pkg.rs_middle;
sql_demo2;
exec runStats_pkg.rs_stop;
*/
EXAMPLE:
SQL> set serveroutput on size 999999
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 5444 hsecs
Run2 ran in 9134 hsecs
run 1 ran in 59.6% of the time
Name Run1 Run2 Diff
LATCH.Consistent RBA 3 4 1
LATCH.FOB s.o list latch 1 0 -1
LATCH.FOB s.o list latch 1 0 -1
LATCH.dml lock allocation 3 2 -1
LATCH.ktm global data 1 0 -1
LATCH.object stats modificatio 2 1 -1
LATCH.sort extent pool 2 1 -1
STAT...change write time 0 1 1
STAT...enqueue requests 6 5 -1
STAT...table fetch continued r 3 2 -1
STAT...opened cursors current 2 3 1
STAT...db block changes 1,003 1,002 -1
LATCH.transaction branch alloc 1 2 1
LATCH.session switching 1 2 1
LATCH.ncodef allocation latch 1 2 1
LATCH.ktm global data 1 0 -1
LATCH.archive process latch 2 1 -1
LATCH.Consistent RBA 3 1 -2
LATCH.enqueues 87 85 -2
STAT...parse time elapsed 4 2 -2
STAT...opened cursors cumulati 25 23 -2
STAT...cursor authentications 0 2 2
LATCH.post/wait queue 6 8 2
LATCH.object stats modificatio 2 0 -2
LATCH.dml lock allocation 3 1 -2
LATCH.archive control 2 0 -2
LATCH.archive process latch 2 0 -2
STAT...enqueue releases 5 2 -3
STAT...table fetch continued r 3 0 -3
STAT...parse time elapsed 4 7 3
STAT...parse count (total) 31 34 3
LATCH.enqueue hash chains 86 82 -4
LATCH.post/wait queue 6 2 -4
STAT...CPU used by this sessio 15 19 4
STAT...enqueue requests 6 2 -4
STAT...CPU used when call star 15 19 4
LATCH.simulator lru latch 8 3 -5
STAT...parse time cpu 2 7 5
STAT...index scans kdiixs1 23 18 -5
LATCH.library cache load lock 12 6 -6
STAT...sorts (memory) 11 5 -6
STAT...shared hash latch upgra 24 18 -6
STAT...parse count (hard) 3 9 6
STAT...db block gets 520 514 -6
LATCH.simulator lru latch 8 2 -6
LATCH.session allocation 10 4 -6
STAT...active txn count during 1 8 7
STAT...cluster key scans 7 0 -7
STAT...cluster key scans 7 0 -7
STAT...cleanout - number of kt 1 8 7
LATCH.library cache load lock 12 4 -8
STAT...workarea executions - o 18 10 -8
LATCH.session allocation 10 1 -9
STAT...recursive cpu usage 3 12 9
STAT...execute count 36 45 9
STAT...calls to kcmgcs 3 12 9
LATCH.session timer 20 11 -9
LATCH.active checkpoint queue 19 9 -10
STAT...cluster key scan block 10 0 -10
STAT...cluster key scan block 10 0 -10
LATCH.mostly latch-free SCN 18 8 -10
LATCH.lgwr LWN SCN 18 8 -10
LATCH.active checkpoint queue 19 30 11
LATCH.mostly latch-free SCN 18 29 11
LATCH.lgwr LWN SCN 18 29 11
STAT...CPU used by this sessio 15 3 -12
STAT...index scans kdiixs1 23 11 -12
STAT...table scans (short tabl 0 12 12
STAT...CPU used when call star 15 3 -12
STAT...calls to get snapshot s 45 57 12
LATCH.session timer 20 33 13
STAT...shared hash latch upgra 24 11 -13
LATCH.redo allocation 512 497 -15
STAT...parse count (total) 31 16 -15
STAT...opened cursors cumulati 25 9 -16
LATCH.channel operations paren 37 18 -19
STAT...execute count 36 16 -20
LATCH.channel operations paren 37 58 21
STAT...rows fetched via callba 28 6 -22
STAT...sorts (memory) 11 33 22
STAT...rows fetched via callba 28 3 -25
STAT...sorts (rows) 2,631 2,605 -26
STAT...calls to get snapshot s 45 18 -27
LATCH.library cache pin alloca 192 224 32
STAT...index fetch by key 38 6 -32
LATCH.multiblock read objects 32 0 -32
LATCH.multiblock read objects 32 0 -32
LATCH.redo writing 66 31 -35
STAT...index fetch by key 38 3 -35
LATCH.redo writing 66 103 37
STAT...workarea executions - o 18 59 41
LATCH.undo global data 58 9 -49
LATCH.child cursor hash table 21 73 52
LATCH.undo global data 58 1 -57
STAT...recursive calls 541 482 -59
STAT...consistent gets - exami 102 42 -60
STAT...table fetch by rowid 97 37 -60
STAT...buffer is pinned count 65 4 -61
LATCH.enqueue hash chains 86 24 -62
LATCH.enqueues 87 24 -63
STAT...buffer is pinned count 65 2 -63
LATCH.row cache enqueue latch 86 22 -64
LATCH.messages 123 188 65
LATCH.messages 123 56 -67
LATCH.row cache objects 95 23 -72
STAT...table fetch by rowid 97 21 -76
LATCH.library cache pin alloca 192 115 -77
STAT...consistent gets - exami 102 20 -82
STAT...prefetched blocks 111 0 -111
STAT...prefetched blocks 111 0 -111
STAT...free buffer requested 145 32 -113
LATCH.library cache pin 302 176 -126
STAT...physical reads 137 5 -132
STAT...free buffer requested 145 12 -133
STAT...physical reads 137 2 -135
LATCH.library cache pin 302 449 147
STAT...redo size 60,884 60,728 -156
LATCH.shared pool 492 271 -221
LATCH.cache buffers lru chain 267 31 -236
LATCH.library cache 630 379 -251
LATCH.cache buffers lru chain 267 11 -256
LATCH.simulator hash latch 14 274 260
LATCH.library cache 630 930 300
LATCH.shared pool 492 877 385
LATCH.simulator hash latch 14 405 391
STAT...recursive calls 541 138 -403
LATCH.checkpoint queue latch 900 432 -468
LATCH.checkpoint queue latch 900 1,424 524
LATCH.SQL memory manager worka 1,206 603 -603
LATCH.SQL memory manager worka 1,206 1,943 737
STAT...rollback changes - undo 0 976 976
STAT...session logical reads 2,672 4,294 1,622
STAT...table scan blocks gotte 1,931 158 -1,773
STAT...user calls 1,814 31 -1,783
STAT...SQL*Net roundtrips to/f 1,812 23 -1,789
STAT...SQL*Net roundtrips to/f 1,812 9 -1,803
STAT...user calls 1,814 11 -1,803
STAT...no work - consistent re 2,022 201 -1,821
STAT...consistent gets 2,152 290 -1,862
STAT...buffer is not pinned co 2,125 242 -1,883
LATCH.row cache objects 95 1,990 1,895
LATCH.row cache enqueue latch 86 1,988 1,902
STAT...table scan blocks gotte 1,931 8 -1,923
STAT...no work - consistent re 2,022 31 -1,991
STAT...buffer is not pinned co 2,125 54 -2,071
STAT...consistent gets 2,152 66 -2,086
STAT...session logical reads 2,672 580 -2,092
STAT...redo entries 488 2,928 2,440
LATCH.redo allocation 512 2,961 2,449
STAT...consistent changes 510 2,988 2,478
STAT...Elapsed Time 5,455 2,723 -2,732
STAT...db block gets 520 4,004 3,484
LATCH.session idle bit 3,633 64 -3,569
LATCH.session idle bit 3,633 22 -3,611
STAT...Elapsed Time 5,455 9,142 3,687
LATCH.cache buffers chains 6,960 2,637 -4,323
STAT...db block changes 1,003 5,933 4,930
STAT...table scan rows gotten 27,144 20,595 -6,549
LATCH.cache buffers chains 6,960 18,442 11,482
STAT...sorts (rows) 2,631 17,408 14,777
STAT...bytes received via SQL* 20,387 2,997 -17,390
STAT...bytes received via SQL* 20,387 569 -19,818
STAT...table scan rows gotten 27,144 99 -27,045
STAT...session pga memory -33,564 0 33,564
STAT...session uga memory 0 65,464 65,464
STAT...session uga memory max 0 130,928 130,928
STAT...session pga memory -33,564 131,072 164,636
STAT...redo size 60,884 317,228 256,344
STAT...bytes sent via SQL*Net 1,028,572 18,702 -1,009,870
STAT...bytes sent via SQL*Net 1,028,572 4,785 -1,023,787
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
31,860 38,176 6,316 83.46%
PL/SQL procedure successfully completed.
-The End-