oracle信息统计脚本runstats

转自：http://www.cnblogs.com/xwdreamer/archive/2012/06/14/2548952.html

　runstats是《Oracle Database 9i/10g/11g编程艺术：深入数据库体系结构》作者编写的一个工具，能对做同一件事情的两个不同方法进行比较，得出孰优孰劣的结果。我们只需要提供两个不同的方法，余下的事情都由runstats负责。runstats只负责测量3个要素：

1. 墙上时钟或耗时时间：知道墙上时钟或耗时时间很有用，不过这不是最重要的信息。
2. 系统统计结果：会并排地i显示每个方法做某件事（如执行一个解析调用）的次数，并展示出两者之差
3. 闩定（latching）：这是这个报告的关键输出。

　　要使用runstats，需要能访问几个V$视图，并创建一个表来存储统计结果，还要创建runstats包。为此，需要访问4个V$表（就是那些神奇的动态性能表）：V$STATNAME、V$MYSTAT和V$LATCH和V$TIMER。这四个表其实是别名，真正对象的名称应为V_$STATNAME、V_$MYSTAT、  V_$LATCH、  V_$TIMER，并且都是在sys账户下。如果其他账户要访问这四张表， 需要进行授权。我们需要再scott下进行操作，因此需要将这四张表的select权限授予给scott账户。下面进行具体操作。

1在sys账户下

1.1将V_$表的查询权限授权给scott   

--在sys账户下授权视图查询权限给scott   
grant SELECT on SYS.v_$statname to "SCOTT" ;
grant SELECT on SYS.v_$mystat to "SCOTT" ;
grant SELECT on SYS.v_$latch to "SCOTT" ;
grant SELECT on SYS.v_$timer to "SCOTT" ;

 

2在scott账户下

2.1查询V_$表

--在scott账户下测试视图查询，
select * from SYS.v_$statname
select * from SYS.v$statname

 

2.2创建视图

--在scott账户下创建视图
create or replace view stats 
as select 'STAT...' || a.name name, b.value 
      from SYS.v_$statname a, SYS.v_$mystat b 
     where a.statistic# = b.statistic# 
    union all 
    select 'LATCH.' || name,  gets 
      from SYS.v_$latch 
    union all 
    select 'STAT...Elapsed Time', hsecs from SYS.v_$timer; 

 

2.3创建信息收集表 

--创建信息收集表 (事务级临时表)
create global temporary table run_stats 
( runid varchar2(15), 
  name varchar2(80), 
  value int ) 
on commit preserve rows;

 2.4创建runstats包

--创建包
create or replace package runstats_pkg 
    as 
        procedure rs_start; 
        procedure rs_middle; 
        procedure rs_stop( p_difference_threshold in number default 0 ); 
    end; 
    / 

 

2.5创建包体

--创建包体
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_cpu_time; 
   end; 
 
   procedure rs_middle 
   is 
   begin 
       g_run1 := (dbms_utility.get_cpu_time-g_start); 
  
       insert into run_stats 
       select 'after 1', stats.* from stats; 
       g_start := dbms_utility.get_cpu_time; 
  
   end; 

   procedure rs_stop(p_difference_threshold in number default 0) 
   is 
   begin 
       g_run2 := (dbms_utility.get_cpu_time-g_start); 
  
       dbms_output.put_line 
       ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' ); 
       dbms_output.put_line 
       ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' ); 
           if ( g_run2 <> 0 ) 
           then 
       dbms_output.put_line 
       ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) || 
         '% of the time' ); 
           end if; 
       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 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/decode( run2, 0,  
                             to_number(0), 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; 
  / 

 

3.使用runstats

3.1创建表T

--创建表
  create table t(x int);

 

3.2创建存储过程proc1，使用了一条带绑定变量的SQL语句

--创建存储过程proc1
  create or replace procedure proc1
  as
  begin
      for i in 1 .. 10000
      loop
          execute immediate
          'insert into t values(:x)' using i;
      end loop;
  end;
  /
EXECUTE IMMEDIATE代替了以前Oracle中DBMS_SQL package包。
解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块。

 

3.3创建存储过程proc2，分别为要插入的每一行构造一条独立的SQL语句

--创建存储过程proc2
  create or replace procedure proc2
  as
  begin
      for i in 1 .. 10000
      loop
          execute immediate
          'insert into t values('||i||')';
      end loop;
  end;
  /

 

3.4使dbms_output.put_line 生效

要使用dbms_output.put_line ，则必须在sqlplus中显式声明：

set serverout on

 

比如：

SQL> set serverout on
SQL> exec dbms_output.put_line('asda');
asda--输出结果

PL/SQL procedure successfully completed

 

3.5执行runstats中的方法以及两个存储过程

exec runstats_pkg.rs_start;
  exec proc1;
  exec runstats_pkg.rs_middle;
  exec proc2;
  exec runstats_pkg.rs_stop(10000);

 

输出结果为：

Run1 ran in 26 cpu hsecs
Run2 ran in 267 cpu hsecs
run 1 ran in 9.74% of the time

Name                                  Run1        Run2        Diff
STAT...parse count (total)              15      10,016      10,001
STAT...session cursor cache hi      10,003           1     -10,002
STAT...consistent gets from ca          39      10,054      10,015
STAT...consistent gets from ca          70      10,087      10,017
STAT...consistent gets                  70      10,087      10,017
STAT...db block gets                10,424      30,369      19,945
STAT...db block gets from cach      10,424      30,369      19,945
STAT...db block gets from cach          65      20,039      19,974
LATCH.cache buffers chains          51,209      71,216      20,007
LATCH.enqueue hash chains               60      20,122      20,062
LATCH.enqueues                          44      20,109      20,065
STAT...session logical reads        10,494      40,456      29,962
STAT...recursive calls              10,131      40,144      30,013
LATCH.kks stats                          3      33,343      33,340
STAT...session uga memory max      123,452      72,940     -50,512
LATCH.shared pool simulator             80      83,641      83,561
STAT...session pga memory           65,536     196,608     131,072
STAT...session uga memory                0     196,392     196,392
LATCH.row cache objects                228     210,126     209,898
LATCH.shared pool                   20,151     339,848     319,697

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
73,042     780,963     707,921      9.35%

PL/SQL 过程已成功完成。