• oracle信息统计脚本runstats的创建与验证


      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   

    View Code
    --在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_$表

    View Code
    --在scott账户下测试视图查询,发现不能使用别名查询,只能使用视图真名
    select * from SYS.v_$statname--成功
    select * from SYS.v$statname--失败

    2.2创建视图

    View Code
    --在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创建信息收集表 

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

    2.4创建runstats包

    View Code
    --创建包
    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创建包体

    View Code
    --创建包体
    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

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

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

    View Code
     --创建存储过程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;
      /

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

    View Code
     --创建存储过程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中的方法以及两个存储过程

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

    输出结果为:

    View Code
    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 过程已成功完成。

     

     

     

  • 相关阅读:
    dede list调用 内容模型 附件
    dedecms list标签调用附加表字段--绝对成功
    织梦系统站点首页、列表、文章页等页面点击数调用方法
    apache include 文件包含引用的方法 报错 [an error occurred while processing this directive]
    windows 2008 中IIS7.0以上如何设置404错误页面
    织梦Fatal error: Call to a member function GetInnerText()
    dedecms清空栏目后,新建ID不从1开始的解决方法
    js禁止
    AngularJs表单验证
    submile 安装,汉化,插件
  • 原文地址:https://www.cnblogs.com/xwdreamer/p/2548952.html
Copyright © 2020-2023  润新知