tom的RUNSTATS测试工具

这里只是整理一下TOM的RUNSTATS检查工具包的脚本及使用示例，由于脚本内容较多，记录一下还是很必要的，便于日后使用翻看。

 

要运行RUNSTATSSS测试工具，必须有访问V$STATNAME,V$MYSTAT,V$LATCH的权限。

还必须有对SYS.V_$STATNAME,SYS.V_$MYSTAT,SYS.V_$LATCH的直接权限（不是通过角色授权）。

create or replace view stats

as

select 'STAT...'||a.NAME AS name, b.VALUE as value

from v$statname a, v$mystat b

where a.STATISTIC# = b.STATISTIC#

union all

select 'latch'||name as name, gets as value

from v$latch;

 

创建一张存储这些统计信息的小表：

create global temporary table run_stats
(
       runid varchar2(15),
       name varchar2(80),
       value int
) on commit preserve rows;

 

-- 创建 runstats 包。其中包括 3 个简单 API 调用：
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',10)||
                            lpad('Run2',10)|| lpad('Diff',10));
      for x in
      (
          select rpad(a.name,30)||
                 to_char(b.value - a.value,'9,999,999')||
                 to_char(c.value - b.value,'9,999,999')||
                 to_char(((c.value - b.value)-(b.value - a.value)),'9,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',10) || lpad('Run2',10) ||
                            lpad('Diff',10) || lpad('Pct',8));
      for x in
      (
          select to_char(run1, '9,999,999')||
                 to_char(run2, '9,999,999')||
                 to_char(diff, '9,999,999')||
                 to_char(round(run1/run2*100,2), '999.99')||'%' data
          from (
               select sum(b.value - a.value) run1,
                      sum(c.value - b.value) run2,
                      sum((c.value - b.value) - (b.value - c.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;

/

 

以上可以考虑在SYS用户下创建，之后创建runstats_pkg的public synonym即可。

 

使用实例：

使用Scott/tiger用户：

SQL> drop table heap;

 

表已丢弃。

 

SQL> drop table iot;

 

表已丢弃。

 

SQL> create table heap (dummy )

  2  as select * from dual;

 

表已创建。

 

SQL> ed

已写入文件 afiedt.buf

 

  1  create table iot(dummy primary key)

  2  organization index

  3* as select dummy from dual

SQL> /

 

表已创建。

 

SQL> analyze table heap compute statistics;

 

表已分析。

 

SQL> analyze table iot compute statistics;

 

表已分析。

 

SQL> exec runstats_pkg.rs_start;

 

PL/SQL 过程已成功完成。

 

SQL> ed

已写入文件 afiedt.buf

 

  1  declare

  2  x varchar2(1);

  3  begin

  4  for i in 1..10000 loop

  5  select dummy into x

  6  from heap;

  7  end loop;

  8* end;

SQL> /

 

PL/SQL 过程已成功完成。

 

SQL> exec runstats_pkg.rs_middle;

 

PL/SQL 过程已成功完成。

 

SQL> ed

已写入文件 afiedt.buf

 

  1  declare

  2  x varchar2(1);

  3  begin

  4  for i in 1..10000 loop

  5  select dummy into x

  6  from iot;

  7  end loop;

  8* end;

SQL> /

 

PL/SQL 过程已成功完成。

 

SQL> set serveroutput on size 1000000

SQL> exec runstats_pkg.rs_stop;

Run1 ran in7480hsecs                                                           

Run2 ran in3131hsecs                                                           

run 1 ran in 238.9%of the time                                                 

                                                                                     

Name                                Run1      Run2      Diff                   

STAT...SQL*Net roundtrips to/f         6         5        -1                   

STAT...active txn count during         3         4         1                   

STAT...calls to kcmgcs                 3         4         1                   

STAT...cleanout - number of kt         3         4         1                   

STAT...db block gets                 525       524        -1                   

STAT...opened cursors cumulati        14        13        -1                   

STAT...user calls                      9         8        -1                   

latchktm global data                   0         1         1                   

latchmessage pool operations p         0         1         1                   

latchsort extent pool                  1         2         1                   

latchsimulator lru latch               1         0        -1                   

latchsimulator hash latch              1         0        -1                   

latchsession idle bit                 22        21        -1                   

latchobject stats modification         0         1         1                   

latchkwqit: protect wakeup tim         2         1        -1                   

latchfile number translation t         0         1         1                   

STAT...parse count (failures)          1         0        -1                   

STAT...free buffer requested           5         4        -1                   

STAT...cursor authentications          1         0        -1                   

STAT...consistent gets - exami        11        12         1                   

STAT...consistent changes            491       493         2                   

STAT...db block changes            1,014     1,012        -2                    

STAT...parse time elapsed              2         0        -2                   

STAT...parse time cpu                  2         0        -2                   

STAT...immediate (CURRENT) blo         4         0        -4                    

latchchild cursor hash table          19        15        -4                   

STAT...redo entries                  505       501        -4                   

STAT...CPU used by this sessio        26        21        -5                   

STAT...CPU used when call star        26        21        -5                   

latchenqueues                         94        87        -7                   

STAT...deferred (CURRENT) bloc         0         9         9                   

latchConsistent RBA                   37        27       -10                   

latchsession timer                    25        11       -14                   

latchactive checkpoint queue l        27        10       -17                   

latchpost/wait queue                  74        54       -20                   

latchlgwr LWN SCN                     56        33       -23                   

latchmostly latch-free SCN            56        33       -23                   

latchchannel operations parent        48        23       -25                   

latchcache buffers lru chain          56        29       -27                   

latchrow cache enqueue latch         128       164        36                   

latchrow cache objects               134       170        36                   

latchredo writing                    189       112       -77                   

latchsession allocation              188       100       -88                   

latchundo global data                371       246      -125                   

STAT...redo size                  65,224    65,364       140                   

latchmessages                        332       180      -152                   

latchlibrary cache pin allocat       442       272      -170                   

latchredo allocation                 866       696      -170                   

latchdml lock allocation             391       203      -188                   

latchlibrary cache pin            20,627    20,410      -217                    

latchenqueue hash chains             662       378      -284                   

STAT...bytes received via SQL*     1,448     1,137      -311                   

latchshared pool                  10,791    10,476      -315                    

latchlibrary cache                21,176    20,748      -428                   

STAT...bytes sent via SQL*Net      1,245       693      -552                   

latchcheckpoint queue latch        1,298       502      -796                   

latchSQL memory manager workar     1,608       670      -938                   

STAT...buffer is not pinned co    10,001         1   -10,000                   

STAT...index scans kdiixs1            10    10,010    10,000                   

STAT...table scan blocks gotte    10,000         0   -10,000                   

STAT...table scans (short tabl    10,000         0   -10,000                   

STAT...table scan rows gotten     10,000         0   -10,000                   

STAT...shared hash latch upgra        10    10,010    10,000                   

STAT...no work - consistent re    10,001         1   -10,000                   

STAT...consistent gets            30,026    10,027   -19,999                   

STAT...calls to get snapshot s    30,018    10,018   -20,000                   

STAT...session logical reads      30,551    10,551   -20,000                   

latchcache buffers chains         65,336    24,057   -41,279                   

                                                                                      

Run1 latches total versus runs -- difference and pct                           

Run1      Run2      Diff     Pct                                               

125,072    79,748   159,496 156.83%                                             

 

PL/SQL 过程已成功完成。

 

SQL> spool off