SQL QUERY TO FIND TOP IO SESSIONS ORACLE
Below query can be used to find the top sessions which has high IOPS on the database
col SID format 99999
col EVENT format a20
col PROGRAM format a20
col BLOCK_GETS format 99999
col CONSISTENT_GETS format 99999
col PHYSICAL_READS format 99999
col USERNAME format a10
col process format a10
col serial# format 99999
col pid format 99999
col spid format 99999
select * from (select s.sid,s.serial#,s.EVENT,s.PROGRAM,nvl(s.USERNAME,'oracle') as USERNAME,si.BLOCK_GETS,si.CONSISTENT_GETS,si.PHYSICAL_READS
from v$session s
inner join v$sess_io si on s.sid=si.sid
order by si.PHYSICAL_READS desc) where rownum < 6;