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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s