/* fts.sql
     see which sessions are doing full table scans */
set lines 132
col waitevent head EVENT format a5 trunc
col username format a10
col osuser format a10 trunc
col p1 format 9999
col p3 format 999
col sid format 9999
col machine format a20 trunc
col program format a30 trunc
select decode(w.event,'db file scattered read','FTS',
                      'db file sequential read','IRS',
                      'direct path read','DPR',
                      'direct path write','DPW',
		      'direct path read (lob) ','DRL',
		      'direct path write (lob)','DWL') waitevent,
w.sid,s.username,
p1,p2,p3, s.sql_hash_value, s.status,p.spid, s.osuser,
s.module || ' - ' || nvl(s.program,p.program) program
from v$session_wait w, v$session s, v$process p
 where (w.event like 'db file s%' or w.event like 'direct path%')
and s.sid = w.sid
and s.paddr = p.addr
and w.wait_time = 0
order by 1 desc
/