RE: Urgent: db file sequential read

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Wed, 31 Mar 2004 09:58:53 -0800
Message-ID: <35CFD500D7BDCE43B9030BBA5979DC181D9445@ussccem13.hds.com>

Hi Stefan,

Not many realize this, but v$session_wait records and displays the _last_ *instrumented* wait event. Thus, if a session performed a dbfsr and then launched into a series of LIOs that do not encounter an *instrumented* wait event (such as 'latch free'), then you might infer that the session is 'hanging' on the db file event. This will be evident from looking at the SEQ# column in v$session_wait - if this doesn't change between the times you look at it, then your session is suffering from this phenonmenon.

What you should do it to set up and run Jonathan's most excellent script (see archives from a few weeks back) that snapshots v$sesstat and reports the difference. Look for increases in 'consistent reads' and gets - if so, excessive LIOs are most probably your culprit.

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Good advice: Be quick to listen, slow to speak and slow to become angry. Jas 1:19

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Jahnke
>Sent: Wednesday, March 31, 2004 9:34 AM
>To: Oracle DBA List (E-Mail)
>Subject: Urgent: db file sequential read - wait
>
>
>Hi list
>
>I have a session waiting forever on a "db file sequential
>read" wait event
>during a mass delete.
>The statement looks like this:

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 31 2004 - 11:55:02 CST

RE: Urgent: db file sequential read - wait