Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO irregularity

Re: CBO irregularity

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 07 Jun 2004 14:36:50 -0600
Message-id: <40C4D1E2.8020807@sun.com>

Cary Millsap wrote:

<snip>

> - Use informative hints. For example (from a Tom Kyte training
> course I attended), use ALL_ROWS, FIRST_ROWS(n), FIRST_ROWS, CHOOSE,
> (NO)REWRITE, DRIVING_SITE, (NO)PARALLEL, (NO)APPEND,
> CURSOR_SHARING_EXACT, DYNAMIC_SAMPLING, and CARDINALITY.

Excellent point! I need to temper my stand on hints. I do appreciate your distinction between informative and plan-restricting hints. To extend this idea, should you also make a distinction between session-enabled 'hints' (first_rows, all_rows, etc.) and non-session enabled hints (only valid in a statement)? I would consider a session-enabled hint one that can be controlled, either directly or indirectly, at the session level. What would be a good starting point for analyzing and determining the proper use of these hints? For example, in an online system "select * from emp" running under the first_rows environment is probably preferred, where the same query in the same system called by a batch application may perform better under an all_rows environment.

Definite food for thought here (at least in my opinion)

> 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.

These are the the most common hints I see in development environments. This is the foundation for my stand (and rant) on hints.

Regards,
Daniel Fink



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 - 15:34:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US