指定工夫范畴为：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;