SQL分析诊断总汇

SQL诊断总汇：

• 查询个用户正在使用的SQL

• SELECT S.USERNAME, Q.SQL_TEXT
  FROM V$SESSION S, V$SQLTEXT Q
  WHERE S.SQL_ADDRESS = Q.ADDRESS AND S.SQL_HASH_VALUE = Q.HASH_VALUE

• 查询被锁住的用户所用的SQL

• SELECT S.USERNAME USERNAME,
           S.SID SID,
           S.SERIAL# SERIAL,
           Q.SQL_TEXT SQL_TEXT
    FROM V$SESSION S, V$SQLTEXT Q, V$LOCK L
   WHERE S.LOCKWAIT IS NOT NULL
           AND S.SQL_ADDRESS = Q.ADDRESS
           AND S.LOCKWAIT = L.KADDR
           AND S.SQL_HASH_VALUE = Q.HASH_VALUE

• 查询高消耗的SQL

• SELECT S.BUFFER_GETS,
             S.DISK_READS,
             S.EXECUTIONS,
             DECODE(S.EXECUTIONS, 0, 0, S.BUFFER_GETS/S.EXECUTIONS) B_E,
             S.SQL_TEXT
      FROM V$SQL S
  ORDER BY S.DISK_READS DESC

• 查询运行了很久的SQL

• SELECT S.USERNAME,
           S.SID,
           S.OPNAME,
           ROUND(S.SOFAR * 100 / S.TOTALWORK, 0) || '%' AS PROGRESS,
           S.TIME_REMAINING,
           Q.SQL_TEXT
    FROM V$SESSION_LONGOPS S, V$SQL Q
    WHERE S.TIME_REMAINING <> 0
    AND S.SQL_ADDRESS = Q.ADDRESS
    AND S.SQL_HASH_VALUE = Q.HASH_VALUE

• 查询CPU消耗最高的SQL

• SELECT P.PID,
           S.SID,
           S.USERNAME,
           S.OSUSER,
           P.SERIAL#,
           P.TERMINAL,
           P.PROGRAM,
           P.BACKGROUND,
           S.STATUS,
           RTRIM(SUBSTR(Q.SQL_TEXT, 1, 80)) SQL
    FROM V$SQLAREA Q, V$SESSION S, V$PROCESS P
    WHERE P.ADDR = S.PADDR
    AND S.SQL_ADDRESS = Q.ADDRESS(+)
    AND P.SPID LIKE '%&OS_P_ID%'

• DISK READ 最高的SQL

• SELECT DISK_READS, SQL_TEXT
    FROM ( SELECT *
                FROM V$SQLAREA
            ORDER BY DISK_READS DESC)
   WHERE ROWNUM <= 5

• 查询10条性能最差的SQL

• SELECT *
    FROM ( SELECT Q.PARSING_USER_ID,Q.SORTS, Q.COMMAND_TYPE,Q.DISK_READS, Q.SQL_TEXT
                FROM V$SQLAREA Q
            ORDER BY DISK_READS DESC)
   WHERE ROWNUM <= 10