Re: many seconds waiting for I/O?

From: John Kanagaraj <john.kanagaraj_at_gmail.com>
Date: Mon, 30 Jul 2007 10:27:55 -0700
Message-ID: <2ead3a60707301027i3e147528i41563f84ad6c3fdd@mail.gmail.com>

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 **

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 30 2007 - 12:27:55 CDT