Re: The CBO Lie Detector

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 19 May 2006 08:50:42 -0500
Message-ID: <446DCD32.30407@jcpenney.com>

('binary' encoding is not supported, stored as-is)

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.

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.

Thanks

Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA (ver 7.0 - 9i)
Allocation & Assortment planning systems JCPenney

Ric Van Dyke wrote:
>
> No I don't think it's doing that.... yet.
>
>
>
> There was (and I assume still is, I left oracle almost a year ago) a
> buzz inside Oracle land about doing that, but I don't think it has
> happened quite yet. Lots of folks recognize that doing that Oracle
> could "learn" to make better and better plans. Implementation of this
> idea has apparently been more difficult then many folks thought. So,
> as far as I know, it's still not there.
>
>
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> Cell 248-705-0624
>
> -----------------------
>
> Hotsos Symposium March 4-8, 2007. Be there.
>
> ------------------------------------------------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Schultz, Charles
> *Sent:* Friday, May 19, 2006 9:15 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* The CBO Lie Detector
>
>
>
> After reading Jonathan Lewis's books, Tom Kyte's posts and Joze
> Senegacnik's presentations (and*/ so/* many others out there who have
> contributed), one message (among others) has been beaten into my
> head;* give the CBO all the facts, and the CBO will make the best
> choice*. The corollary is that if you lie to the CBO, it may (or may
> not) come up with a sub-optimal plan.
>
> So with all the automated features and collections in 10g, could not
> the engine determine if it was being lied to by comparing the
> estimated costs & cardinality to the actual end values? With all this
> talk about "self-tuning", I would almost expect the database to come
> back and say "Hey, you fed me false information!". At the very least.
> The next step would be for the engine to go back and correct that
> false information.
>
> Maybe the database already does this and I am missing it somehow. If
> so, please correct me.
>
> *charles schultz*
> *oracle dba*
> *aits - adsd*
> *university** of illinois*
>
>
>

--





The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

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

Received on Fri May 19 2006 - 08:50:42 CDT