Re: 10g System statistics - single and multi
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: kutrovsky.oracle@xxxxxxxxx
- Date: Tue, 17 May 2005 14:44:55 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
