Ash.SAMPLE_TIME >= SYSDATE -60/24/60/60), set to /
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;