Re: Oracle Hint Behavior

On Feb 4, 5:11 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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&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 --