runstats脚本分析不同的sql资源使用

最新推荐文章于 2024-11-05 21:55:19 发布

原创最新推荐文章于 2024-11-05 21:55:19 发布 · 795 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

oracle tuning

RunStats是一个用于比较两种不同方法执行同一任务效率的工具。通过记录墙上时钟时间、系统统计结果及闩定次数等关键指标，帮助用户评估两种方法的性能差异。本文详细介绍了RunStats的安装步骤和使用方法。

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

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

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

下面是安装方法:

1.设置权限

在你需要执行runstats包的用户赋予相关权限

   
    
     grant SELECT on SYS.v_$statname to test ;
    
    
     grant SELECT on SYS.v_$mystat to test ;
    
    
     grant SELECT on SYS.v_$latch to test ;
    
    
     grant SELECT on SYS.v_$timer to test ;

2.创建视图

    
     
      --在test账户下创建视图
     
     
      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;

3.创建收集信息的表

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

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; 
    
    
         /

4.创建runstats包体

   
    
     --创建包体
    
    
     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; 
    
    
       /

5.测试runstats脚本

runstats脚本分为三步

第一.执行runstats_pkg.rs_start开始调用,然后执行你第一段sql

第二.执行完成第一段sql后面执行runstats_pkg.rs_middle,然后执行你第二段的sql

第三.执行rs_stop,完成之后就会打印出你第一段sql和第二段sql的资源使用统计信息.

下面开始测试:

第一步:

   
    
     SQL> set serveroutput on 
    
    
     SQL> exec runstats_pkg.rs_start
    
    
      
    
    
     PL/SQL procedure successfully completed.
    
    
      
    
    
     SQL> select count(*) from tobj;
    
    
      
    
    
       COUNT(*)
    
    
     ----------
    
    
          14921

第二步:

   
    
     SQL> exec runstats_pkg.rs_middle
    
    
      
    
    
     PL/SQL procedure successfully completed.
    
    
      
    
    
     SQL> select /*+ full(tobj) */ count(*) from tobj;
    
    
      
    
    
       COUNT(*)
    
    
     ----------
    
    
          14921

第三步:

   
    
     SQL> exec runstats_pkg.rs_stop
    
    
     Run1 ran in 1 cpu hsecs
    
    
     Run2 ran in 2 cpu hsecs
    
    
     run 1 ran in 50% of the time
    
    
     	
    
    
     Name				      Run1	  Run2	      Diff
    
    
     STAT...Requests to/from client		 6	     7		 1
    
    
     LATCH.threshold alerts latch		 0	     1		 1
    
    
     STAT...physical write total IO		 0	     1		 1
    
    
     STAT...db block gets direct		 0	     1		 1
    
    
     STAT...physical writes			 0	     1		 1
    
    
     STAT...physical writes direct		 0	     1		 1
    
    
     STAT...physical write IO reque		 0	     1		 1
    
    
     STAT...physical writes non che		 0	     1		 1
    
    
     STAT...physical writes direct		 0	     1		 1
    
    
     STAT...index scans kdiixs1		 5	     6		 1
    
    
     STAT...sql area purged			 0	     1		 1
    
    
     STAT...sql area evicted 		 0	     1		 1
    
    
     STAT...cursor authentications		 2	     1		-1
    
    
     STAT...parse count (failures)		 0	     1		 1
    
    
     LATCH.session switching 		 2	     1		-1
    
    
     LATCH.ksuosstats global area		 3	     2		-1
    
    
     LATCH.object stats modificatio		 0	     1		 1
    
    
     LATCH.enqueues				 1	     2		 1
    
    
     LATCH.ksv allocation latch		 1	     0		-1
    
    
     LATCH.queued dump request		 0	     1		 1
    
    
     LATCH.KMG MMAN ready and start		 7	     8		 1
    
    
     LATCH.cache buffers lru chain		 1	     2		 1
    
    
     LATCH.active checkpoint queue		 7	     8		 1
    
    
     LATCH.begin backup scn array		 0	     1		 1
    
    
     LATCH.dml lock allocation		 1	     0		-1
    
    
     LATCH.KTF sga latch			 0	     1		 1
    
    
     LATCH.Change Notification Hash		 7	     8		 1
    
    
     LATCH.sort extent pool			 1	     0		-1
    
    
     LATCH.deferred cleanup latch		 1	     0		-1
    
    
     LATCH.shared pool sim alloc		 2	     1		-1
    
    
     LATCH.session timer			 7	     8		 1
    
    
     LATCH.cp sga latch			 1	     0		-1
    
    
     LATCH.ncodef allocation latch		 1	     0		-1
    
    
     LATCH.ASM network state latch		 1	     0		-1
    
    
     STAT...DB time				 3	     4		 1
    
    
     STAT...recursive cpu usage		 3	     1		-2
    
    
     STAT...free buffer requested		10	    12		 2
    
    
     STAT...calls to kcmgas			 1	     3		 2
    
    
     STAT...active txn count during		 0	     2		 2
    
    
     STAT...cleanout - number of kt		 0	     2		 2
    
    
     STAT...lob writes unaligned		 0	     2		 2
    
    
     STAT...session cursor cache co		 2	     0		-2
    
    
     STAT...parse time elapsed		 1	     3		 2
    
    
     STAT...SQL*Net roundtrips to/f		 5	     7		 2
    
    
     LATCH.mostly latch-free SCN		20	    22		 2
    
    
     LATCH.lgwr LWN SCN			20	    22		 2
    
    
     LATCH.Consistent RBA			20	    22		 2
    
    
     LATCH.object queue header heap		15	    17		 2
    
    
     LATCH.LGWR NS Write			 2	     0		-2
    
    
     LATCH.managed standby latch		 2	     0		-2
    
    
     LATCH.alert log latch			 2	     0		-2
    
    
     LATCH.shared pool simulator		 8	     6		-2
    
    
     LATCH.PL/SQL warning settings		 3	     5		 2
    
    
     STAT...lob writes			 0	     3		 3
    
    
     STAT...user calls			 7	    10		 3
    
    
     STAT...index fetch by key		10	     7		-3
    
    
     STAT...HSC Heap Segment Block		16	    19		 3
    
    
     LATCH.session allocation		 4	     1		-3
    
    
     LATCH.session idle bit			18	    21		 3
    
    
     LATCH.active service list		10	    13		 3
    
    
     LATCH.simulator hash latch		22	    25		 3
    
    
     STAT...enqueue requests 		11	    14		 3
    
    
     STAT...enqueue releases 		11	    14		 3
    
    
     LATCH.FAL Queue 			 3	     0		-3
    
    
     LATCH.archive control			 3	     0		-3
    
    
     LATCH.loader state object free		 0	     4		 4
    
    
     LATCH.row cache objects 	       243	   247		 4
    
    
     STAT...consistent changes		64	    60		-4
    
    
     STAT...table scans (short tabl		 0	     4		 4
    
    
     LATCH.tablespace key chain		 1	     6		 5
    
    
     STAT...non-idle wait count		 5	    10		 5
    
    
     LATCH.undo global data			 8	    13		 5
    
    
     STAT...calls to get snapshot s		23	    29		 6
    
    
     LATCH.shared pool		       263	   269		 6
    
    
     LATCH.ASM db client latch		42	    48		 6
    
    
     LATCH.space background task la		30	    36		 6
    
    
     LATCH.redo allocation			61	    67		 6
    
    
     LATCH.archive process latch		 6	     0		-6
    
    
     STAT...session cursor cache hi		11	    18		 7
    
    
     STAT...deferred (CURRENT) bloc		 1	    10		 9
    
    
     STAT...rows fetched via callba		 9	     0		-9
    
    
     STAT...execute count			16	    25		 9
    
    
     STAT...calls to kcmgcs			25	    35		10
    
    
     STAT...parse count (total)		16	    26		10
    
    
     LATCH.DML lock allocation		 3	    13		10
    
    
     LATCH.OS process allocation		40	    50		10
    
    
     STAT...opened cursors cumulati		13	    23		10
    
    
     STAT...commit cleanouts 		 1	    12		11
    
    
     LATCH.redo writing			97	   108		11
    
    
     STAT...commit cleanouts succes		 1	    12		11
    
    
     STAT...consistent gets - exami		34	    22	       -12
    
    
     LATCH.object queue header oper		22	    34		12
    
    
     STAT...recursive calls			89	   106		17
    
    
     STAT...table fetch by rowid		31	    10	       -21
    
    
     STAT...db block gets from cach		 1	    22		21
    
    
     STAT...redo entries			18	    43		25
    
    
     STAT...db block changes 		84	   111		27
    
    
     LATCH.enqueue hash chains		80	   111		31
    
    
     LATCH.channel operations paren	       241	   277		36
    
    
     STAT...db block gets from cach		65	   105		40
    
    
     STAT...db block gets			65	   106		41
    
    
     STAT...buffer is not pinned co		73	    31	       -42
    
    
     LATCH.messages			       316	   362		46
    
    
     LATCH.checkpoint queue latch	       729	   833	       104
    
    
     STAT...consistent gets from ca	       104	   257	       153
    
    
     STAT...consistent gets		       104	   257	       153
    
    
     STAT...no work - consistent re		61	   215	       154
    
    
     STAT...consistent gets from ca		70	   235	       165
    
    
     STAT...session logical reads	       169	   363	       194
    
    
     STAT...table scan blocks gotte		 0	   199	       199
    
    
     LATCH.SQL memory manager worka	     1,410	 1,611	       201
    
    
     STAT...Elapsed Time		     2,053	 2,497	       444
    
    
     LATCH.cache buffers chains	       453	   971	       518
    
    
     STAT...bytes sent via SQL*Net	     1,285	 1,845	       560
    
    
     STAT...bytes received via SQL*	     1,560	 2,351	       791
    
    
     STAT...undo change vector size	     4,148	 5,708	     1,560
    
    
     STAT...physical write bytes		 0	 8,192	     8,192
    
    
     STAT...cell physical IO interc		 0	 8,192	     8,192
    
    
     STAT...physical write total by		 0	 8,192	     8,192
    
    
     STAT...redo size for direct wr		 0	 8,228	     8,228
    
    
     STAT...table scan rows gotten		 0	14,929	    14,929
    
    
     STAT...redo size		     5,768	21,416	    15,648
    
    
     STAT...session pga memory max		 0     917,504	   917,504
    
    
     STAT...session pga memory		 0   1,114,112	 1,114,112
    
    
     STAT...logical read bytes from	 1,384,448   2,965,504	 1,581,056
    
    
     

    
    
     Run1 latches total versus runs -- difference and pct
Run1	    Run2	Diff	   Pct
4,418	    5,436	1,018	  81.27%

PL/SQL procedure successfully completed.

标签

#oracle #runstats