Re: Oracle Hint Behavior
- From: Mark D Powell <Mark.Powell@xxxxxxx>
- Date: Wed, 6 Feb 2008 15:37:44 -0800 (PST)
On Feb 4, 5:11 pm, joel garry <joel-ga...@xxxxxxxx> wrote:
On Feb 2, 6:17 pm, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
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 ---
But remember how long it took O8 to come out - O7 1994, 08 1997? And
there were some big differences with 7.2 --> 7.3, too. Pure
speculation on my part, but to me the time lag of purchasing rdb,
delaying and then releasing O8 seems to fit my biases, at least. At
the time, I felt Larry got the deal of the century, $100M for all that
intellectual property and human capital plus cash flow of the same
magnitude.
jg
--
@home.com is bogus.http://websearch.cs.com/cs/boomframe.jsp?query=joel_garry&page=1&offs...- Hide quoted text -
- Show quoted text -
I do not deny that some logic found in the RDB optimizer may well have
found its way into the Oracle CBO, but the CBO existed before Oracle
bought RDB so it is not the original basis. After buying RDB Oracle
incorporated versions of several RDB features into Oracle and it seems
that RDB also got a couple of Oracle features added to it. It would
be foolish of Oracle not to make full use of the intellectual property
purchased and it should go both ways.
I wonder it the new in memory managment of undo in 11g came directly
or indirectly from Times Ten being that it is a an in memory database
manager. I just read about the feature today. It did not save much
clock time in the authors testing (5.5% or so) but it did cut CPU
usage by around 25% compared to traditional undo management using
segments only. The feature is on my default and not available with
RAC.
-- Mark D Powell --
.
- References:
- Re: Oracle Hint Behavior
- From: Mark D Powell
- Re: Oracle Hint Behavior
- From: joel garry
- Re: Oracle Hint Behavior
- Prev by Date: Re: long running select min(timestamp) query
- Next by Date: Re: Online Survey about web based IT training.... iPod raffle!!!
- Previous by thread: Re: Oracle Hint Behavior
- Next by thread: Re: Windows 64-bit
- Index(es):
Relevant Pages
|
