RE: System Statistics and the CBO

From: Karen Morton <Karen.Morton_at_hotsos.com>
Date: Fri, 17 Jun 2005 12:09:13 -0500
Message-ID: <26237991.1119028153202.JavaMail.root@hotsos01.hotsos.com>

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_at_greenshield.ca; oracle-l_at_freelists.org 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 
-- 
http://www.freelists.org/webpage/oracle-l 
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 17 2005 - 13:15:12 CDT