Re: Oracle Hint Behavior

On Feb 2, 6:17 pm, Mark D Powell <Mark.Pow..._at_eds.com> 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...- 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&offset=0&result_url=redir%3Fsrc%3Dwebsearch%26requestId%3D22e14c7aeba01f07%26clickedItemRank%3D6%26userQuery%3Djoel_garry%26clickedItemURN%3Dhttp%253A%252F%252Fmy.opera.com%252Fdizwell%252Fblog%252Fshow.dml%252F40242%26invocationType%3D-%26fromPage%3Dcs404search%26amp%3BampTest%3D1&remove_url=http%3A%2F%2Fmy.opera.com%2Fdizwell%2Fblog%2Fshow.dml%2F40242