Re: Oracle Hint Behavior
- From: Mark D Powell <Mark.Powell@xxxxxxx>
- Date: Sat, 2 Feb 2008 18:17:33 -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...- Hide quoted text -
- Show quoted text -
This is kind of a side note but the cost base optimizer was introduced
with Oracle version 7 and I seem to remember that was before Oracle
bought RBD from DEC. It seems to me that Oracle hired a fellow from
Ingres to write the optimizer. That does not mean that features of
the RDB optimizer did not make it into the Oracle optimzer but I think
your timeline is off. Have you got any refereneces?
-- Mark D Powell --
.
- Follow-Ups:
- Re: Oracle Hint Behavior
- From: joel garry
- Re: Oracle Hint Behavior
- Prev by Date: Re: How to exit from pl/sql
- Next by Date: How to suppress Oracle old,new messages in SQLPLUS ?
- Previous by thread: Re: Oracle Hint Behavior
- Next by thread: Re: Oracle Hint Behavior
- Index(es):
Relevant Pages
|
