RE: CBO irregularity

From: Michael Thomas <mhthomas_at_yahoo.com>
Date: Mon, 7 Jun 2004 14:52:48 -0700 (PDT)
Message-ID: <20040607215248.86993.qmail@web50602.mail.yahoo.com>

Hi,

Thanks, this is really good information on CBO.

Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> It's what CBO is built to do: take into account a
...

>
> By (b), I mean things like:
>
>
>
> - Don't use plan-restricting hints, except
> in test situations where
> you're trying to cause bad performance. For example,
> don't use hints like
> RULE, ORDERED, USE_NL, INDEX, USE_HASH, FULL,
> AND_EQUAL, etc. in production.
>
>

...

I came across a *humorous* Oracle Metalink note 73489.1 today with some funny statistics. But, it reminded me of "Dan Tow's" book and method to insure the correct driving table is used for queries. The note describes CBO limits based on permutations on the total number of tables in a query (a *rare case* in reference to your posting). The CBO has valid limitations and it is confusing to absorb all the cases.

The topic of SQL tuning using the Oracle CBO can be complex. And, I personally think most people can be misled by lists of SQL tuning silver bullets, hints, parameters, whatever you call them. :-)

As you know I liked the Hotsos OP101 SQL Tuning course. It helped me focus on the bigger picture while tuning queries. The test harness *big picture* helps categorizes performance into timings, CPU work, waits and latches, etc. How many people understand that the fastest running SQL is not necessarily the best?

I wish the focus was first on *big picture* information in discussions like CBO irregularity. I'm looking forward to other comments on this thread.

Regards,

Mike Thomas

Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 07 2004 - 16:50:09 CDT