Re: many seconds waiting for I/O?
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: exriscer@xxxxxxxxx, hkchital@xxxxxxxxxxxxxx, amonte <ax.mount@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 30 Jul 2007 10:27:55 -0700
To the OP,
You need to remember that the EVENT column in V$SESSION_WAIT records the
*last* instrumented wait - executing on CPU is NOT waiting. In other words,
if a session performed a 'db file sequential read' and then moved into CPU,
and continues to burn up CPU doing whatever (spinning on a latch [not 'latch
wait'], accessing a block, executing PL/SQL, etc.), then what you will see
if you look ONLY at the EVENT column is the last read, and this leads to a
mistaken assumption that the I/O is 'stuck'. A quick way to look at this is
to use the SQL below - I have highlighted decoding of the WAIT_TIME and used
that to determine if the processing is still WAITING (W) or spinning in CPU
(C).
select s.sid || ',' || s.serial# sid_serial, p.spid, s.process,
s.username || '/' || s.osuser username, s.status,
to_char(s.logon_time, 'DD-MON-YY HH24:MI:SS') logon_time,
s.last_call_et/60 last_call_et, decode(w.wait_time,0,'(W) ','(C) ') ||
w.event || ' / ' || w.p1 || ' / ' || w.p2 || ' / ' || w.p3 waiting_event,
s.machine || '/' || s.program || '/' || s.module || '/' || s.actionmachine_prog
from v$process p, v$session s, v$session_wait w
where s.paddr=p.addr and s.sid in (&List_of_Oracle_SIDs)
and w.sid = s.sid
order by s.logon_time;
This interpretation comes from the view definition of
V$ACTIVE_SESSION_HISTORY and is explained in greater detail in my 2006 paper
that deals with "backporting" 10g features to older versions....
--
John Kanagaraj <><
DB Soft Inc
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
Other related posts:
