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 (
select
OSUSER osuser,
username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
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:

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

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