Re: 10g System statistics - single and multi

I found that Oracle does not use the new costing formula id mreadtm is not larger than sreadtm. If sreadtm >= mreadtm it reverts back to the the cost formula which uses the adjusted dfmrc value rather than the system statistics mbrc value to estimate the cost of an FTS. That was true for Oracle9i. I have not done extensive work with 10g and things like that can change by patch release.

What you describe would be consistnt with that observation

mreadtm = sreadtm = 1 ==> cost(FTS) = #blocks/adjustedDFMRC = ~1400

mreadtm = 2, sreadtm = 1 ==> cost(fts) = #blocks/mbrc = ~1050

mreadtm = 2, sreadtm = 3 ==> cost(FTS) = #blocks/adjustedDFMRC = ~1400

Christo Kutrovsky wrote:
> Hello,
>
> What i've discovered was something very strange. When I had both
> values to 1, my cost of a FTS would be ~1400. When I set multi to 2,
> the cost would go DOWN to ~1050. When I set it to 3, it would go back
> to ~1400.
>
> What I've discovered is that when SINGLE >=3D MULTI, then oracle uses N
> * SINGLE for full table scans some formula independant from the value
> of MULTI.
>
> In a way, this makes sense, as it is not possible to have 1 read to be
> slower then MANY reads. But, this is an average value, thus it is
> quite possible (and likelly on many systems) that multi-block reads
> are faster then single block reads.
>
> However Oracle doesn't handle this case at all. I find this to be a
> severe limitation. What do you think ?
>

-- 
Regards

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