RE: sequential read on full-table scan?
- From: Steve Rospo <srospo@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 May 2005 09:50:05 -0700 (PDT)
I've known about this but it got me thinking: When Oracle determines it
needs a multiblock read doesn't it have to calculate eight hashes
(assuming multiblock_read=8) and probe each of the lru hash chains in
order to determine if the block is in the cache or not? Isn't this a lot
of overhead? Why not just read all 8 blocks and just handle the colision
when it happens? It seems to me a single 8 block read would be more
efficient than say a 4-block read and a 3-block read.
S-
On Tue, 24 May 2005, Cary Millsap wrote:
> 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
>
--
Stephen Rospo Principal Software Architect
Vallent Corporation (formerly Watchmark-Comnitel)
Stephen.Rospo@xxxxxxxxxxx (425)564-8145
This email may contain confidential information. If you received this in
error, please notify the sender immediately by return email and delete this
message and any attachments. Thank you.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
