Oracle 常用性能监控SQL语句

1.  --查看表锁    
SELECT * FROM SYS.V_$SQLAREA WHERE DISK_READS > 100;   
2.  --监控事例的等待    
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;   
3.  --回滚段的争用情况   
SELECT NAME, WAITS, GETS, WAITS / GETS "Ratio"  
  FROM V$ROLLSTAT A, V$ROLLNAME B   
 WHERE A.USN = B.USN;   
4.  --查看前台正在发出的SQL语句   
SELECT USER_NAME, SQL_TEXT 　　   
  FROM V$OPEN_CURSOR 　　   
 WHERE SID IN (SELECT SID   
                 FROM (SELECT SID, SERIAL#, USERNAME, PROGRAM 　　   
                         FROM V$SESSION 　　   
                        WHERE STATUS = 'ACTIVE'));   
5.  --数据表占用空间大小情况   
SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS   
  FROM USER_SEGMENTS   
 WHERE SEGMENT_TYPE = 'TABLE'  
 ORDER BY BYTES DESC, BLOCKS DESC;   
6.  --查看表空间碎片大小   
SELECT TABLESPACE_NAME,   
       ROUND(SQRT(MAX(BLOCKS) / SUM(BLOCKS)) *   
             (100 / SQRT(SQRT(COUNT(BLOCKS)))),   
             2) FSFI   
  FROM DBA_FREE_SPACE   
 GROUP BY TABLESPACE_NAME   
 ORDER BY 1;   
7.  --查看表空间占用磁盘情况   
SELECT B.FILE_ID 文件ID号,   
       B.TABLESPACE_NAME 表空间名,   
       B.BYTES 字节数,   
       (B.BYTES - SUM(NVL(A.BYTES, 0))) 已使用,   
       SUM(NVL(A.BYTES, 0)) 剩余空间,   
       SUM(NVL(A.BYTES, 0)) / (B.BYTES) * 100 剩余百分比   
  FROM DBA_FREE_SPACE A, DBA_DATA_FILES B   
 WHERE A.FILE_ID = B.FILE_ID   
 GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.BYTES   
 ORDER BY B.FILE_ID;   
8.  --查看session使用回滚段   
SELECT R.NAME 回滚段名,   
       S.SID,   
       S.SERIAL#,   
       S.USERNAME 用户名,   
       T.STATUS,   
       T.CR_GET,   
       T.PHY_IO,   
       T.USED_UBLK,   
       T.NOUNDO,   
       SUBSTR(S.PROGRAM, 1, 78) 操作程序   
  FROM SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$ROLLNAME R   
 WHERE T.ADDR = S.TADDR   
   AND T.XIDUSN = R.USN   
 ORDER BY T.CR_GET, T.PHY_IO;   
9.  --查看SGA区剩余可用内存   
SELECT NAME,   
       　　　　　　SGASIZE / 1024 / 1024　　　　　　　　"Allocated(M)",   
       　　　　　　BYTES / 1024　　　　　　　　　　　 "**空间(K)",   
       　　　　　　ROUND(BYTES / SGASIZE * 100, 2) 　　　"**空间百分比(%)" 　　   
  FROM (SELECT SUM(BYTES) SGASIZE FROM SYS.V_$SGASTAT) S,   
       SYS.V_$SGASTAT F 　　   
 WHERE F.NAME = 'free memory';   
10.  --监控表空间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;   
11.  --监控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;   
12.  --监控 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;   
13.  --监控 SGA **享缓存区的命中率，应该小于1%   
SELECT SUM(PINS) "Total Pins",   
       SUM(RELOADS) "Total Reloads",   
       SUM(RELOADS) / SUM(PINS) * 100 LIBCACHE   
  FROM V$LIBRARYCACHE;   
14.  --监控 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');   
15.  --监控内存和硬盘的排序比率，最好使它小于 .10   
SELECT NAME, VALUE   
  FROM V$SYSSTAT   
 WHERE NAME IN ('sorts (memory)', 'sorts (disk)');   
16.  --监控字典缓冲区   
SELECT SUM(GETS) "DICTIONARY GETS",   
       SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"  
  FROM V$ROWCACHE;   
17.  --非系统用户建在SYSTEM表空间中的表   
SELECT OWNER, TABLE_NAME   
  FROM DBA_TABLES   
 WHERE TABLESPACE_NAME IN ('SYSTEM', 'USER_DATA')   
   AND OWNER NOT IN  
       ('SYSTEM', 'SYS', 'OUTLN', 'ORDSYS', 'MDSYS', 'SCOTT', 'HOSTEAC');   
18.  --性能最差的SQL   
SELECT *   
  FROM (SELECT PARSING_USER_ID EXECUTIONS,   
               SORTS,   
               COMMAND_TYPE,   
               DISK_READS,   
               SQL_TEXT   
          FROM V$SQLAREA   
         ORDER BY DISK_READS DESC)   
 WHERE ROWNUM < 100;   
  
--用下列SQL 工具找出低效SQL ：    
SELECT EXECUTIONS,   
       DISK_READS,   
       BUFFER_GETS,   
       ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) HIT_RADIO,   
       ROUND(DISK_READS / EXECUTIONS, 2) READS_PER_RUN,   
       SQL_TEXT   
  FROM V$SQLAREA   
 WHERE EXECUTIONS > 0   
   AND BUFFER_GETS > 0   
   AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8   
 ORDER BY 4 DESC;   
  
19.  --读磁盘数超100次的sql   
SELECT * FROM SYS.V_$SQLAREA WHERE DISK_READS > 100;   
20.  --最频繁执行的sql   
SELECT * FROM SYS.V_$SQLAREA WHERE EXECUTIONS > 100;   
21.  --查询使用CPU多的用户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;   
22.  --当前每个会话使用的对象数   
SELECT A.SID, S.TERMINAL, S.PROGRAM, COUNT(A.SID)   
  FROM V$ACCESS A, V$SESSION S   
 WHERE A.OWNER <> 'SYS'  
   AND S.SID = A.SID   
 GROUP BY A.SID, S.TERMINAL, S.PROGRAM   
 ORDER BY COUNT(A.SID);