RE: sequential read on full-table scan?

It's because:

1. A single-block read is never a 'db file scattered read'.
2. Oracle will never PIO a block that's already in the buffer cache.

My guess is that blocks 90:145799 and 90:145801 were in the database buffer cache, but 90:145800 wasn't.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khemmanivanh, Somckit
Sent: Friday, May 20, 2005 11:36 AM
To: paul.baumgartel_at_gmail.com; Oracle-L
Subject: RE: sequential read on full-table scan?

You're not alone, I saw this weirdness in a recent 10046 trace, observe:

WAIT #40: nam=3D'db file sequential read' ela=3D 53 p1=3D90 p2=3D145793 = p3=3D1
WAIT #40: nam=3D'db file sequential read' ela=3D 50 p1=3D90 p2=3D145798 = p3=3D1
WAIT #40: nam=3D'db file sequential read' ela=3D 55 p1=3D90 p2=3D145800 = p3=3D1
WAIT #40: nam=3D'db file scattered read' ela=3D 74 p1=3D90 p2=3D145805 = p3=3D2
WAIT #40: nam=3D'db file scattered read' ela=3D 88 p1=3D90 p2=3D145832 = p3=3D2
WAIT #40: nam=3D'db file sequential read' ela=3D 85 p1=3D92 p2=3D121800 = p3=3D1
WAIT #40: nam=3D'db file sequential read' ela=3D 44 p1=3D92 p2=3D121802 = p3=3D1

My MBRC is 8. Also why would it switch from sequential to scattered and back again in the middle of the trace?

Hmmm, thinking about this, could this be because of parallel? If so, you can ignore my message...

Thanks!=20

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paul Baumgartel Sent: Friday, May 20, 2005 9:24 AM
To: Oracle-L
Subject: sequential read on full-table scan?

I am tracking a process that's exhibiting what I consider odd behavior. This is 9.2.0.4.0 on Windows.

A stored procedure is executing a loop in which it performs a cursor fetch, then updates the same table based on data in the fetch:

SELECT SUM(OPTIONS_VESTED) SUM_VESTED,SUM(ACTUAL_FORFEITURE) SUM_FORFEITED FROM OE_LINEITEM_RPT WHERE GRANT_DT =3D3D :b1 AND PLAN_ID =3D3D :b2 AND PLAN_SORT =3D3D:b3 AND OPT_PRC =3D3D :b4 AND EXPIRATION_DT =3D3D :b5 AND FAS123_QUAL_CODE =3D3D :b6 AND RUNTIME_ID = =3D3D
:b7=3D

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