RE: The CBO Lie Detector

Forgive me for reviving this retired thread. I just came across Wolfgang's "Tuning by Cardinality Feedback" (*). This is the direction I was going in (I think). From what you guys have said, I get the impression it is *not* a good idea to do this "feedback" automatically (ie, via the non-existent feedback process). Or is this strictly for job security? *grin*

• I have to make a small disclaimer that I have not fully read the paper, yet. I have only found an abstract, and am still looking for the full thing. I did not find it on the OakTable.net or hotsos, but am going through the rest of my google hits.

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Friday, May 19, 2006 9:24 AM
To: rshamsud_at_jcpenney.com
Cc: ric.van.dyke_at_hotsos.com; Schultz, Charles; oracle-l_at_freelists.org Subject: Re: The CBO Lie Detector

Quoting Riyaj Shamsudeen <rshamsud_at_jcpenney.com>:

>
> I don't think it is a good idea to detect the false information,
> without correcting some of the fundamental CBO discrepancies. In few
> cases, We have deliberately fed false information to get acceptable
> access plan and so, at least, there should be a way to override the
lie detector.

I fully agree. Statistics are aggregates, averages, and as such do not always, maybe even rarely, portray the correct picture (people have drowned in bodies of water which were on average only a few inches deep). If the CBO draws wrong conclusions from the "correct" statistics, maybe you can get the CBO to draw the right conclusions from "doctored" statistics. Sometimes two wrongs do make a right.
>
> One nagging gripe I have is that there is no way to tell CBO that
> there is a strong correlation exists between two different column
> predicates, at least that I know of. [J.Lewis has excellent
> presentation exploring this issue in detail ]. We have few such
> queries and we tried to use sql profiles in conjunction with
> cursor_sharing. Every time we hit one or other issue that we can not
> deal with it and finally resorted to feed "lies" to CBO.

The CBO is able to do that in many cases since Oracle 9i by setting dynamic sampling to >= 4 (quoting from the 9.2 Performance Tuning Guide):
"..., plus all tables that have single-table predicates that reference 2 or more columns."
See also Jeff Moss' blog at
http://oramossoracle.blogspot.com/2005/11/scd2s-and-their-affect-on-cbo- part-ii.html

--
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l