Re: Tim Gorman's "...Cost-Based Optimizer.doc"

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
  • Date: Thu, 16 Jun 2005 13:05:16 -0600


Khemmanivanh, Somckit wrote:

> Ahh, pre-fetching, I forgot about that... BUT still doesn't your point
> about highjacked read heads also hold true for prefetching?
> 
> Wouldn't some other user request highjack the read head in a
> pre-fetching scenario? It seems to me the highjacked scenario could fit
> all types of read requests...To clarify, any single read request could
> be interleaved among all the other read requests, no? 

Yes, but when the data is prefetched into the SAN cache, the read 
request is uncoupled from the disk architecture. No seeks, no rotational 
delay. The data is immediately available and the read performance is 
just the time it takes to transfer the data.

> 
> Also, some context would be appropriate, I think. We jumped from talking
> about disks (I was envisioning non-SAN) to disk reads in a SAN
> environment. I apologize, If I'm mistaken since I haven't read the
> article in question yet.
> 

Agreed, I made a jump in the conversation. I was merely commenting on a 
possible reason why scattered reads may be faster than sequential reads. 
Ordinarily they always should be slower. Here I disagree with the OP:

Marquez, Chris wrote:

 > It seems that on "NON-cached" filesystems (e.g. RAW, OCFS) that there 
would/do NOT be a great difference in "db file scattered reads" vs. "db 
file sequential reads" AVERAGE_WAITS...as every read from disk (on 
non-cached filesystem) is a *real* read from dusk...no OS buffer to 
help, no?

Both, scattered and sequential reads should - on average - incur the 
same seek and rotational delay times. But the time to transfer multiple 
blocks (for scattered reads) vs. a single block (for sequential reads) 
should always tip the scale in the latter's favour, i.e. sequential 
reads are faster.
In fact, the CBO with system statistics expects that and if mreadtm is 
less or equal to sreadtm then the optimizer does not use the new costing 
and reverts to the pre 9i costing "formulae".

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Other related posts: