Top segment, session or SQL query with high physical Reads in Oracle

Check Segments with high physical Reads in Oracle

Find the top 10 Segment or object for High Physical read in Oracle

set line 200 page 200
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;

Find top 10 Session which having high Physical Reads in Oracle

set linesize 120
col osuser format a10
col username format a10
col pid format 999999
Select * from (
OSUSER osuser,
ses.SID sid,
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS desc) where rownum <= 10;

Find the top 10 SQL query having high physical read

Select * from (
select disk_reads,substr(sql_text,1,4000)
from v$sqlarea
order by disk_reads desc) where rownum <= 10;

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.