RE: System Statistics and the CBO
- From: "Karen Morton" <Karen.Morton@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 17 Jun 2005 12:09:13 -0500
If I really don't want to see the CPU cost stuff in explain plan output or if I
want the optimizer to ignore system stats, I've been playing around with
changing the _optimizer_cost_model parameter to IO vs. choose. I'm still
trying to understand all the implications of how the optimizer "choose"s cpu
vs. io in its decision making but if you just want to see how the optimizer
acts with and without considering system stats, it's worth a test or two to set
the parameter to io and test the query and then set it to cpu (or choose) and
test again...then compare and see if you learn anything. I know messing with
hidden parameters isn't your long term answer, but it may give you a good way
to test differences in behavior.
If the optimizer is reverting back to old IO costing model when mreadtim <
sreadtim, you should be able to confirm this if you influence the cost model
parameter.
Just a thought....
Karen Morton
Hotsos Enterprises, Ltd.
www.hotsos.com
----- Original Message -----
From: Wolfgang Breitling
Sent: Fri Jun 17 10:10:08 CDT 2005
To: Mladen Gogala
Cc: Patty.Charlebois@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: System Statistics and the CBO
Mladen,
That's something I was wondering myself. My statement below may very
well be a bit too broad. I should rephrase it to
"From all I know, the CBO reverts back to the prior IO costing model if
mreadtim < sreadtim".
The cpu costing part of the new CBO cost algorithm may still be used.
On the other hand I am not sure how much trust to put into what
DBMS_XPLAN.DISPLAY is telling me. I have had cases where suddenly it
reported CPU cost percentages without system stats being present.
Finally, my experiences are mostly with 9iR2 and things like that could
work slightly, or not so slightly, differently in 10g.
Mladen Gogala wrote:
> Wolfgang Breitling wrote:
>
>>
> Wolfgang, when I do SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) it tells me
> that "CPU costing is ON, despite
> the fact that I fiddled with sys.aux_stats$ and changed sreadtime to be
> mreadtime+1. Is DBMS_XPLAN lying to me? The
> version is 10.1.0.4 on Linux.
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
