tom的RUNSTATS测试工具

最新推荐文章于 2016-05-18 09:12:59 发布

wluckdog

最新推荐文章于 2016-05-18 09:12:59 发布

阅读量761

点赞数

分类专栏：工具

工具专栏收录该内容

8 篇文章

订阅专栏

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

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

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

create or replace view stats

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