To find the Oracle TOP sessions with High Disk Reads
Which is the correct SQL to be used:
1.
SELECT
VS.INST_ID,
VS.SID,
VS.SERIAL#,
VS.SQL_ID,
VSA.DISK_READS,
VSA.EXECUTIONS,
ROUND(DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",
BUFFER_GETS,
SUBSTR(SQL_TEXT,1,4000)
FROM
GV$SESSION VS,
GV$SQLAREA VSA
WHERE
VS.INST_ID = VSA.INST_ID
and vs.STATUS='ACTIVE'
AND
VS.SQL_HASH_VALUE = VSA.HASH_VALUE
AND
VSA.ADDRESS=VS.SQL_ADDRESS(+)
AND
DISK_READS > 10000
ORDER BY
VS.INST_ID,
DISK_READS DESC,
EXECUTIONS DESC
[or]
2.
SELECT
VS.INST_ID,
VS.SID,
VS.SERIAL#,
VS.USERNAME,
VS.OSUSER,
VS.PROCESS,
VS.STATUS,
SIO.PHYSICAL_READS,
SIO.BLOCK_CHANGES,
VS.SQL_ID,
SUBSTR(SQL_TEXT,1,4000)
FROM
GV$SESSION VS,
GV$SESS_IO SIO,
GV$SQLAREA VSA
WHERE
VS.INST_ID=SIO.INST_ID
AND
SIO.INST_ID=VSA.INST_ID
AND
VS.SID = SIO.SID
AND
VS.STATUS='ACTIVE'
AND
VS.USERNAME IS NOT NULL
AND
VS.USERNAME <> 'SYS'
AND
VS.SQL_HASH_VALUE = VSA.HASH_VALUE
AND
VSA.ADDRESS=VS.SQL_ADDRESS(+)
AND
SIO.PHYSICAL_READS> 10000
ORDER BY
PHYSICAL_READS DESC;
Regards,
Veera