Re: Oracle Hint Behavior
- From: Anurag Varma <avoracle@xxxxxxxxx>
- Date: Thu, 31 Jan 2008 07:38:55 -0800 (PST)
On Jan 30, 6:03 pm, Michael Austin <maus...@xxxxxxxxxxxxxxxxxx> wrote:
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...
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
.
- References:
- Oracle Hint Behavior
- From: Dereck L. Dietz
- Re: Oracle Hint Behavior
- From: Mark D Powell
- Re: Oracle Hint Behavior
- From: Michael Austin
- Oracle Hint Behavior
- Prev by Date: Re: Erwin: setting a property for all tables
- Next by Date: Re: Why is VARCHAR2(4000) bad ?
- Previous by thread: Re: Oracle Hint Behavior
- Next by thread: Re: Oracle Hint Behavior
- Index(es):
Relevant Pages
|
