run_stats
?
??? 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.
?
?
?