mystat脚本获得oracle统计结果

mystat.sql和mystat2.sql可以根据v$mystat视图中的信息得出执行各个语句的各项统计结果

mystat.sql只是获得统计结果的开始值:

  
  

   
   

    
    set echo off
   
   
   
   

    
    set verify off
   
   
   
   

    
    column value new_val V
   
   
   
   

    
    define S="&1"
   
   
   
   

    
     
   
   
   
   

    
    set autotrace off
   
   
   
   

    
    select a.name,b.value from v$statname a,v$mystat b
   
   
   
   

    
    where a.statistic#=b.statistic#
   
   
   
   

    
    and lower(a.name) like '%'||lower('&S')||'%'
   
   
   
   

    
    /
   
   
   
   

    
    set echo on;
   
   
  
  

mystat2.sql用于报告差值:

  
  

   
   

    
    set echo off
   
   
   
   

    
    set verify off
   
   
   
   

    
    select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff
   
   
   
   

    
    from v$statname a,v$mystat b
   
   
   
   

    
    where a.statistic#=b.statistic#
   
   
   
   

    
    and lower(a.name) like '%'||lower('&S')||'%'
   
   
   
   

    
    /
   
   
   
   

    
    set echo on
   
   
  
  

例如要查看某个update产生的redo数,可以使用一下命令:

   
   

    
    

     
     SQL> @mystat.sql "redo size"
    
    
    
    

     
     SQL> set echo off
    
    
    
    

     
      
    
    
    
    

     
     NAME								      VALUE
    
    
    
    

     
     ---------------------------------------------------------------- ----------
    
    
    
    

     
     redo size							    4272908
    
    
    
    

     
     redo size for lost write detection					  0
    
    
    
    

     
     redo size for direct writes					       8228
    
    
    
    

     
      
    
    
    
    

     
     SQL> update tobj set created=sysdate;
    
    
    
    

     
      
    
    
    
    

     
     14921 rows updated.
    
    
    
    

     
      
    
    
    
    

     
     SQL> commit;
    
    
    
    

     
      
    
    
    
    

     
     Commit complete.
    
    
    
    

     
      
    
    
    
    

     
     SQL> @mystat2
    
    
    
    

     
     SQL> set echo off
    
    
    
    

     
      
    
    
    
    

     
     NAME									  V DIFF
    
    
    
    

     
     ---------------------------------------------------------------- ---------- ----------------
    
    
    
    

     
     redo size							    8548796	   8,540,568
    
    
    
    

     
     redo size for lost write detection					  0	      -8,228
    
    
    
    

     
     redo size for direct writes					      16456	       8,228