oracle常用监控SQL语句

1、查询执行最慢的TOP50

SELECT *
  FROM (SELECT SA.SQL_TEXT,
               SA.SQL_FULLTEXT,
               SA.EXECUTIONS "执行次数",
               ROUND(SA.ELAPSED_TIME / 1000000, 2) "总执行时间 s",
               ROUND(SA.ELAPSED_TIME / 1000000 / SA.EXECUTIONS, 2) "平均执行时间 s",
               SA.COMMAND_TYPE,
               SA.PARSING_USER_ID "用户ID",
               U.USERNAME "用户名",
               SA.HASH_VALUE
          FROM V$SQLAREA SA
          LEFT JOIN ALL_USERS U
            ON SA.PARSING_USER_ID = U.USER_ID
         WHERE SA.EXECUTIONS > 0
         ORDER BY (SA.ELAPSED_TIME / SA.EXECUTIONS) DESC)
 WHERE (用户名 = 'NDNPRD')
   AND ROWNUM <= 50;

2、查看总消耗时间最多的前10条SQL语句

SELECT *
  FROM (SELECT V.SQL_ID,
               V.CHILD_NUMBER,
               V.SQL_TEXT,
               V.ELAPSED_TIME,
               V.CPU_TIME,
               V.DISK_READS,
               RANK() OVER(ORDER BY V.ELAPSED_TIME DESC) ELAPSED_RANK
          FROM V$SQL V) A
 WHERE ELAPSED_RANK <= 10;

3、查看CPU消耗时间最多的前10条SQL语句

SELECT *
  FROM (SELECT V.SQL_ID,
               V.CHILD_NUMBER,
               V.SQL_TEXT,
               V.ELAPSED_TIME,
               V.CPU_TIME,
               V.DISK_READS,
               RANK() OVER(ORDER BY V.CPU_TIME DESC) ELAPSED_RANK
          FROM V$SQL V) A
 WHERE ELAPSED_RANK <= 10;

4、查看消耗磁盘读取最多的前10条SQL语句

SELECT *
  FROM (SELECT V.SQL_ID,
               V.CHILD_NUMBER,
               V.SQL_TEXT,
               V.ELAPSED_TIME,
               V.CPU_TIME,
               V.DISK_READS,
               RANK() OVER(ORDER BY V.DISK_READS DESC) ELAPSED_RANK
          FROM V$SQL V) A
 WHERE ELAPSED_RANK <= 10;

5、查找前十条性能差的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 < 10;

6、查看占io较大的正在运行的session

SELECT SE.SID,
       SE.SERIAL#,
       PR.SPID,
       SE.USERNAME,
       SE.STATUS,
       SE.TERMINAL,
       SE.PROGRAM,
       SE.MODULE,
       SE.SQL_ADDRESS,
       ST.EVENT,
       ST. P1TEXT,
       SI.PHYSICAL_READS,
       SI.BLOCK_CHANGES
  FROM V$SESSION SE, V$SESSION_WAIT ST, V$SESS_IO SI, V$PROCESS PR
 WHERE ST.SID = SE.SID
   AND ST. SID = SI.SID
   AND SE.PADDR = PR.ADDR
   AND SE.SID > 6
   AND ST. WAIT_TIME = 0
   AND ST.EVENT NOT LIKE '%SQL%'
 ORDER BY PHYSICAL_READS DESC;

7、查询Oracle正在执行的sql语句及执行该语句的用户

SELECT B.USERNAME,
       B.MACHINE,
       TO_CHAR(B.SQL_EXEC_START, 'yyyy-mm-dd hh24:mi:ss') SQL_EXEC_START,
       'alter system kill session ' || '''' || B.SID || ',' || B.SERIAL# || ',@' ||
       B.INST_ID || ''';' KILL_COMMAND,
       SUBSTR(SQL_TEXT, 1, 100) SQL_TEXT,
       SQL_FULLTEXT
  FROM GV$SESSION B, GV$SQLAREA SA
 WHERE B.SQL_HASH_VALUE = HASH_VALUE
   AND B.SQL_ADDRESS = ADDRESS
   AND B.INST_ID = SA.INST_ID
   AND B.USERNAME = 'MDMPRD'
 ORDER BY TO_CHAR(B.SQL_EXEC_START, 'yyyy-mm-dd hh24:mi:ss') NULLS LAST;

8、查看oracle数据库信息

SELECT * FROM NLS_DATABASE_PARAMETERS;

9、共享池命中率：

SELECT SUM(PINHITS - RELOADS) / SUM(PINS) * 100 "hit radio"
  FROM V$LIBRARYCACHE;

10、数据字典命中率：

SELECT (1 - (SUM(GETMISSES) / SUM(GETS))) * 100 "Hit Ratio"
  FROM V$ROWCACHE;

11、缓冲区命中率：

SELECT ROUND((1 -
             (PHYSICAL.VALUE - DIRECT.VALUE - LOBS.VALUE) / LOGICAL.VALUE) * 100,
             2) "缓冲区命中率"
  FROM V$SYSSTAT PHYSICAL,
       V$SYSSTAT DIRECT,
       V$SYSSTAT LOBS,
       V$SYSSTAT LOGICAL
 WHERE PHYSICAL.NAME = 'physical reads'
   AND DIRECT.NAME = 'physical reads direct'
   AND LOBS.NAME = 'physical reads direct (lob)'
   AND LOGICAL.NAME = 'session logical reads';