Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
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.
 
 
 
posted on 2009-04-07 23:04 decode360 阅读(258) 评论(0)  编辑  收藏 所属分类: 10.DB_Tools

只有注册用户登录后才能发表评论。


网站导航: