Re: Oracle Hint Behavior
- From: Michael Austin <maustin@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 30 Jan 2008 17:03:56 -0600
Mark D Powell wrote:
On Jan 30, 9:28 am, "Dereck L. Dietz" <diet...@xxxxxxxxxxxxx> 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...
.
- Follow-Ups:
- Re: Oracle Hint Behavior
- From: Anurag Varma
- Re: Oracle Hint Behavior
- References:
- Oracle Hint Behavior
- From: Dereck L. Dietz
- Re: Oracle Hint Behavior
- From: Mark D Powell
- Oracle Hint Behavior
- Prev by Date: Re: SAP BW Consultant -- REQUIREMENT !!!
- Next by Date: Re: RAC backup questions
- Previous by thread: Re: Oracle Hint Behavior
- Next by thread: Re: Oracle Hint Behavior
- Index(es):
Relevant Pages
|
