RE: sequential read on full-table scan?
- From: "Cary Millsap" <Cary.Millsap@xxxxxxxxxx>
- To: <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>, <paul.baumgartel@xxxxxxxxx>, "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 May 2005 08:28:49 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Khemmanivanh, Somckit
Sent: Friday, May 20, 2005 11:36 AM
To: paul.baumgartel@xxxxxxxxx; 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
