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

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