Re: Oracle Hint Behavior
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 01 Feb 2008 04:02:21 GMT
Message-ID: <hRwoj.1832$Ch6.365@newssvr11.news.prodigy.net>
>> Mark D Powell wrote:
>>> On Jan 30, 9:28 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>>>> Oracle 10.2.0.3.0
>>>> Windows 2003 Server
>>>> What is the behavior of hints in Oracle 10g? I knew that prior to 10g
>>>> Oracle would treat them as only "suggestions" but I thought in 10g they
>>>> would be mostly followed.
>>>> We have a query which is doing a full table scan even with hints being used
>>>> to try to get it to use an index. I can see why it would choose a full
>>>> table scan considering the percentage of the table being returned but I'd
>>>> like to be able to explain why the hint is being ignored now.
>>>> Thanks.
>>> Hints are directives to the optimizer and if valid will be followed;
>>> however, there are operations on the SQL such as query transformation
>>> that can render what is syntaxically a valid hint invalid. There are
>>> also optimizer decisions that are made prior to the hint being
>>> considered that can render the hint invalid such as the choice to hash
>>> join.
>>> Multiple hints are often necessary to try to force a specific plan and
>>> you may need to disable the pushing of predicates or sub-queries
>>> (hints available) to get the plan you want to test.
>>> To get help for your specific SQL you should post the SQL and the
>>> explain plan. Otherwise all anyone can provide is general cases.
>>> HTH -- Mark D Powell --
>> There is an interesting way to influence the optimizer without the use
>> of hints that is not written in any books and coding it seems - well,
>> ummm, redundant.
>>
>> I have used this on many different queries where we could not get the
>> optimizer to use a particular index no matter how hard we tried.
>>
>> This is an over-simplified query, but you will get the point.
>>
>> select a.a, b.b, c.c
>> from tablea a, tableb b, tablec c
>> where b.a = a.a
>> and c.a = a.a
>> and c.a = a.a !!!<<<<NOTE added a second time
>> ......
>>
>> The optimizer sees this and says "OH!!! you really did want THAT Index..."
>>
>> Again this is an over-simplifed example. The optimizer was pulled out
>> of DEC Rdb right after Oracle acquired Rdb back in '94/'95 timeframe.
>> Working with Rdb Engineering, this is something we discovered shortly
>> before the DEC fire sale. Not too long ago, I had a colleague that was
>> having a horrendous time with performance and noticed that there was an
>> index not being used that "should have been". I offered this solution
>> and the query used the index and performance went from ~30 minutes to
>> 2-3 minutes (data warehouse 9.2.0.5 timeframe using CBO). I have more
>> recently had similar results using 10gR2.
>>
>> Yeah, it's a kludge, but it does work -- and BTW - still works in Rdb as
>> well.
>>
>> cool part is that this is all ANSI compliant and if you ever have to
>> move to another db engine (heaven forbid (cough,cough)) - it will still
>> execute unchanged. May not get the same performance, but it would still
>> run...
Date: Fri, 01 Feb 2008 04:02:21 GMT
Message-ID: <hRwoj.1832$Ch6.365@newssvr11.news.prodigy.net>
Anurag Varma wrote:
> On Jan 30, 6:03 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
>> Mark D Powell wrote:
>>> On Jan 30, 9:28 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>>>> Oracle 10.2.0.3.0
>>>> Windows 2003 Server
>>>> What is the behavior of hints in Oracle 10g? I knew that prior to 10g
>>>> Oracle would treat them as only "suggestions" but I thought in 10g they
>>>> would be mostly followed.
>>>> We have a query which is doing a full table scan even with hints being used
>>>> to try to get it to use an index. I can see why it would choose a full
>>>> table scan considering the percentage of the table being returned but I'd
>>>> like to be able to explain why the hint is being ignored now.
>>>> Thanks.
>>> Hints are directives to the optimizer and if valid will be followed;
>>> however, there are operations on the SQL such as query transformation
>>> that can render what is syntaxically a valid hint invalid. There are
>>> also optimizer decisions that are made prior to the hint being
>>> considered that can render the hint invalid such as the choice to hash
>>> join.
>>> Multiple hints are often necessary to try to force a specific plan and
>>> you may need to disable the pushing of predicates or sub-queries
>>> (hints available) to get the plan you want to test.
>>> To get help for your specific SQL you should post the SQL and the
>>> explain plan. Otherwise all anyone can provide is general cases.
>>> HTH -- Mark D Powell --
>> There is an interesting way to influence the optimizer without the use
>> of hints that is not written in any books and coding it seems - well,
>> ummm, redundant.
>>
>> I have used this on many different queries where we could not get the
>> optimizer to use a particular index no matter how hard we tried.
>>
>> This is an over-simplified query, but you will get the point.
>>
>> select a.a, b.b, c.c
>> from tablea a, tableb b, tablec c
>> where b.a = a.a
>> and c.a = a.a
>> and c.a = a.a !!!<<<<NOTE added a second time
>> ......
>>
>> The optimizer sees this and says "OH!!! you really did want THAT Index..."
>>
>> Again this is an over-simplifed example. The optimizer was pulled out
>> of DEC Rdb right after Oracle acquired Rdb back in '94/'95 timeframe.
>> Working with Rdb Engineering, this is something we discovered shortly
>> before the DEC fire sale. Not too long ago, I had a colleague that was
>> having a horrendous time with performance and noticed that there was an
>> index not being used that "should have been". I offered this solution
>> and the query used the index and performance went from ~30 minutes to
>> 2-3 minutes (data warehouse 9.2.0.5 timeframe using CBO). I have more
>> recently had similar results using 10gR2.
>>
>> Yeah, it's a kludge, but it does work -- and BTW - still works in Rdb as
>> well.
>>
>> cool part is that this is all ANSI compliant and if you ever have to
>> move to another db engine (heaven forbid (cough,cough)) - it will still
>> execute unchanged. May not get the same performance, but it would still
>> run...
> > > If you could change the sql, why not just add a hint instead of this > kludge? > Also, can you provide a working example to show this? As far as I > understand, > the optimizer will rewrite your statement to remove the redundant > line. > > Adding a hint will make your intentions more clear .. rather than > adding a > redundant line which most of DBA's will think was a typo? > > Anurag
Hints may or may not be portable across database engines... but the redundant code would function. Like I said - it is a kludge and in some cases adding the hint did not fix the problem - adding the kludge did. And no - I am no longer with the company(s) where I had access to the code that demonstrated this - and there are a LOT of factors that would come into play to cause the issue in the first place - like cardinality of the table AND index(es) come to mind... There were others, but it has been a while... Received on Thu Jan 31 2008 - 22:02:21 CST