Sessions/Segments/Datafile with HIGH Physical Reads/Writes,I/O

Sessions with High physical Reads

set linesize 120
col os_user format a10
col username format a15

col pid format 9999999999
PROMPT SESSIONS SORTED BY PHYSICAL READS
PROMPT
select
OSUSER os_user,username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
and username is not null
and status=’ACTIVE’
order by PHYSICAL_READS;

 Segments with high Physical Reads

set pagesize 200

setlinesize 120

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;

Segments with high Physical Writes

set pagesize 200

setlinesize 120

col segment_name format a20

col owner format a10

select segment_name,object_type,total_physical_writes

from ( select owner||'.'||object_name as segment_name,object_type,

value as total_physical_writes

from v$segment_statistics

where statistic_name in ('physical writes')

order by total_physical_writes desc)

where rownum <=10;

Datafiles with highest I/O activity

col name format a40

set linesize 140

select * from (

select name,phyrds, phywrts,readtim,writetim

from v$filestat a, v$datafile b

where a.file#=b.file#

order by readtim desc) where rownum <6;