【转帖】监控数据库性能的SQL

 1.   监控事例的等待  
          select   event,sum(decode(wait_Time,0,0,1))   "Prev",    
        sum(decode(wait_Time,0,1,0))   "Curr",count(*)   "Tot"    
          from   v$session_Wait    
          group   by   event   order   by   4;  
  2.   回滚段的争用情况  
          select   name,   waits,   gets,   waits/gets   "Ratio"    
          from   v$rollstat   a,   v$rollname   b    
          where   a.usn   =   b.usn;    
  3.   监控表空间的   I/O   比例  
          select   df.tablespace_name   name,df.file_name   "file",f.phyrds   pyr,  
          f.phyblkrd   pbr,f.phywrts   pyw,   f.phyblkwrt   pbw  
          from   v$filestat   f,   dba_data_files   df  
          where   f.file#   =   df.file_id  
          order   by   df.tablespace_name;  
  4.   监控文件系统的   I/O   比例  
          select   substr(a.file#,1,2)   "#",   substr(a.name,1,30)   "Name",    
          a.status,   a.bytes,   b.phyrds,   b.phywrts    
          from   v$datafile   a,   v$filestat   b    
          where   a.file#   =   b.file#;    
  6.   监控   SGA   的命中率  
          select   a.value   +   b.value   "logical_reads",   c.value   "phys_reads",  
          round(100   *   ((a.value+b.value)-c.value)   /   (a.value+b.value))   "BUFFER   HIT   RATIO"    
          from   v$sysstat   a,   v$sysstat   b,   v$sysstat   c  
          where   a.statistic#   =   38   and   b.statistic#   =   39    
          and   c.statistic#   =   40;    
  7.   监控   SGA   中字典缓冲区的命中率  
          select   parameter,   gets,Getmisses   ,   getmisses/(gets+getmisses)*100   "miss   ratio",  
          (1-(sum(getmisses)/   (sum(gets)+sum(getmisses))))*100   "Hit   ratio"  
          from   v$rowcache    
          where   gets+getmisses   <>0  
          group   by   parameter,   gets,   getmisses;    
  8.   监控   SGA   中共享缓存区的命中率，应该小于1%  
          select   sum(pins)   "Total   Pins",   sum(reloads)   "Total   Reloads",  
          sum(reloads)/sum(pins)   *100   libcache  
          from   v$librarycache;  
          select   sum(pinhits-reloads)/sum(pins)   "hit   radio",sum(reloads)/sum(pins)   "reload   percent"    
          from   v$librarycache;  
  10.   监控   SGA   中重做日志缓存区的命中率，应该小于1%  
          SELECT   name,   gets,   misses,   immediate_gets,   immediate_misses,  
          Decode(gets,0,0,misses/gets*100)   ratio1,  
          Decode(immediate_gets+immediate_misses,0,0,  
          immediate_misses/(immediate_gets+immediate_misses)*100)   ratio2  
          FROM   v$latch   WHERE   name   IN   ('redo   allocation',   'redo   copy');    
  11.     监控内存和硬盘的排序比率，最好使它小于   .10，增加   sort_area_size    
          SELECT   name,   value   FROM   v$sysstat   WHERE   name   IN   ('sorts   (memory)',   'sorts   (disk)');    
  13.   监控字典缓冲区  
          SELECT   (SUM(PINS   -   RELOADS))   /   SUM(PINS)   "LIB   CACHE"   FROM   V$LIBRARYCACHE;  
          SELECT   (SUM(GETS   -   GETMISSES   -   USAGE   -   FIXED))   /   SUM(GETS)   "ROW   CACHE"   FROM   V$ROWCACHE;  
          SELECT   SUM(PINS)   "EXECUTIONS",   SUM(RELOADS)   "CACHE   MISSES   WHILE   EXECUTING"   FROM   V$LIBRARYCACHE;    
          后者除以前者,此比率小于1%,接近0%为好。  
          SELECT   SUM(GETS)   "DICTIONARY   GETS",SUM(GETMISSES)   "DICTIONARY   CACHE   GET   MISSES"  
          FROM   V$ROWCACHE  
15.   监控   MTS  
          select   busy/(busy+idle)   "shared   servers   busy"   from   v$dispatcher;  
          此值大于0.5时，参数需加大  
          select   sum(wait)/sum(totalq)   "dispatcher   waits"   from   v$queue   where   type='dispatcher';  
          select   count(*)   from   v$dispatcher;  
          select   servers_highwater   from   v$mts;  
          servers_highwater接近mts_max_servers时，参数需加大  
  16.   碎片程度  
          select   tablespace_name,count(tablespace_name)   from   dba_free_space   group   by   tablespace_name    
          having   count(tablespace_name)>10;  
          alter   tablespace   name   coalesce;  
          alter   table   name   deallocate   unused;  
          create   or   replace   view   ts_blocks_v   as  
          select   tablespace_name,block_id,bytes,blocks,'free   space'   segment_name   from   dba_free_space  
          union   all  
          select   tablespace_name,block_id,bytes,blocks,segment_name   from   dba_extents;  
          select   *   from   ts_blocks_v;  
          select   tablespace_name,sum(bytes),max(bytes),count(block_id)   from   dba_free_space    
          group   by   tablespace_name;  
          查看碎片程度高的表  
          SELECT   segment_name   table_name   ,   COUNT(*)   extents  
          FROM   dba_segments   WHERE   owner   NOT   IN   ('SYS',   'SYSTEM')   GROUP   BY   segment_name  
          HAVING   COUNT(*)   =   (SELECT   MAX(   COUNT(*)   )   FROM   dba_segments   GROUP   BY   segment_name);  
   
  18、找使用CPU多的用户session  
          12是cpu   used   by   this   session  
        select    
  a.sid,spid,status,substr(a.program,1,40)   prog,a.terminal,osuser,value/60/100   value  
          from   v$session   a,v$process   b,v$sesstat   c  
          where   c.statistic#=12   and   c.sid=a.sid   and   a.paddr=b.addr   order   by   value   desc;