Re: Hints
Date: Mon, 15 Aug 2011 14:00:55 -0300
Message-ID: <CAJ2dSGSu+JAH+bqx8m8kHrECbZ3O0-xJiUx7hm6+qJofQvb6MQ_at_mail.gmail.com>
Ahh, but now your questions turns to query tuning. The answer may or may not be adding/removing histograms, changing the sample size or something like that.
cheers.
Alan.-
On Mon, Aug 15, 2011 at 1:37 PM, Orlando L <oralrnr_at_gmail.com> wrote:
>
> Thank you Ric and others.
>
> I have few queries that run slow, but I have to force them to use indexes.
> With the use of indexes they run much faster, with statistics present. I am
> left with the classic question of why is the optimizer not using my indexes
> and why do I have to force it.
>
>
>
>
> On Mon, Aug 15, 2011 at 10:51 AM, Ric Van Dyke <ric.van.dyke_at_hotsos.com>wrote:
>
>> Keep in mind that hints aren’t hints, they are directives. Hints will be
>> followed unless the optimizer can’t use the hint (tell it to use an index
>> that doesn’t exist for example). ****
>>
>> ** **
>>
>> There are good hints and there are bad hints.****
>>
>> ** **
>>
>> Good hints help the optimizer do something, bad hints force the optimizer
>> to do something.****
>>
>> ** **
>>
>> Examples: The cardinality hint is considered good because it can let the
>> optimizer know how many rows will come back from a table when it can’t know
>> (for example a Table that has been casted from a string). It can also “go
>> bad” if the number of rows changes over time (the number of rows cannot be
>> set dynamically as far as I know, other than dynamic SQL which has its own
>> issues). ****
>>
>> ** **
>>
>> A join hint (use_nl for example) is considered bad because it forces the
>> optimizer to pick that join every time. This might be true for the moment
>> but is it always going to be true? Same for index or full table scan hints.
>> ****
>>
>> ** **
>>
>> Hints are great for testing and should only be in production code as a
>> “last resort”. ****
>>
>> ** **
>>
>> +--+--+--+--+--+--+--+--+--+--+--+--+--+--+****
>>
>> Ric Van Dyke****
>>
>> Education Director****
>>
>> Hotsos Ltd.****
>>
>> ** **
>>
>> Hotsos Symposium March 4-8 2012****
>>
>> Make your plans to be there now!****
>>
>> [image: Description: 2012_sym_logo_invert]****
>>
>> ** **
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Orlando L
>> *Sent:* Monday, August 15, 2011 01:44
>> *To:* oracle-l_at_freelists.org
>> *Subject:* Hints****
>>
>> ** **
>>
>> Hello all,
>>
>> Are hints in queries considered bad, if so why.
>>
>> Orlando.
>>
>> ****
>>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 15 2011 - 12:00:55 CDT