关于资源:Oracle-查看最近-60s-资源消耗

指定工夫范畴为：ASH.SAMPLE_TIME >= SYSDATE – 60 / 24 / 60 / 60), 格局起因，调整为 /

COL WAIT FOR 999;
COL TOTAL FOR 999;
COL IO FOR 999;
COL CPU FOR 999;
col SCHEMANAME for a16;
COL SID FOR 9999
COL MACHINE FOR A18;
COL OSUSER FOR A15;
COL PROGRAM FOR A30;
COL EVENT FOR A32;
SELECT S.SID,
       ASH.SQL_ID,
       Q.SQL_TEXT,
       S.SCHEMANAME,
       S.EVENT,
       ASH.PROGRAM,
       SUBSTR(ASH.MACHINE,
              INSTR(ASH.MACHINE, ''|| CHR(92) ||'', -1, 1) + 1) MACHINE,
       SUBSTR(S.OSUSER, INSTR(S.OSUSER, ''|| CHR(92) ||'', -1, 1) + 1) OSUSER,
       SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 0)) "CPU",
       SUM(DECODE(ASH.SESSION_STATE, 'WAITING', 1, 0)) -
       SUM(DECODE(ASH.SESSION_STATE,
                  'WAITING',
                  DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
                  0)) "WAIT",
       SUM(DECODE(ASH.SESSION_STATE,
                  'WAITING',
                  DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
                  0)) "IO",
       SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) "TOTAL"
  FROM V$ACTIVE_SESSION_HISTORY ASH
  LEFT JOIN V$SESSION S
    ON ASH.SESSION_ID = S.SID
  LEFT JOIN V$SQL Q
    ON S.SQL_ID = Q.SQL_ID
   AND S.SQL_ADDRESS = Q.ADDRESS
   AND S.SQL_HASH_VALUE = Q.HASH_VALUE
 WHERE ASH.SAMPLE_TIME >= SYSDATE - 60 / (24 * 60 * 60)
 GROUP BY S.SID,
          S.SCHEMANAME,
          ASH.SQL_ID,
          Q.SQL_TEXT,
          S.EVENT,
          ASH.PROGRAM,
          SUBSTR(ASH.MACHINE,
                 INSTR(ASH.MACHINE, ''|| CHR(92) ||'', -1, 1) + 1),
          SUBSTR(S.OSUSER, INSTR(S.OSUSER, ''|| CHR(92) ||'', -1, 1) + 1)
HAVING SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) > = 2
 ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) DESC;