CURRENT SESSION

# script to find redo generated by current sessions

set lines 2000

set pages 1000

col sid for 99999

col name for a09

col username for a14

col PROGRAM for a21

col MODULE for a25

select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id

from v$sesstat s join v$statname n on n.statistic# = s.statistic#

join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by

redo_mb desc;

# script to check INACTIVE sessions with HIGH DISK IO

select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,

s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

t.disk_reads > 5000

and s.status='INACTIVE'

and s.process='1234'

order by S.PROGRAM;

# script to analyze Disk IO’s

prompt SESSIONS PERFORMING HIGH I/O > 50000

select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,

s.action,s.program,lpad(t.sql_text,30) "Last SQL"

from v$session s, v$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

t.disk_reads > 10000

order by t.disk_reads desc;