Re: RE: oracle can ignore hints
- From: <ryan.gaffuri@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 9 Mar 2004 13:30:40 -0500
so in your opinion hints cannot be ignored?
>
> From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
> Date: 2004/03/09 Tue AM 11:02:24 EST
> To: <oracle-l@xxxxxxxxxxxxx>
> Subject: RE: oracle can ignore hints
>
> For what it's worth, after spending time with Jonathan Lewis and Connor
> McDonald in the past two days, I am deeply and thoroughly convinced that
> hints are directives, not suggestions.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of R Zijlstra
> Sent: Monday, March 08, 2004 7:55 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: oracle can ignore hints
>
> Excellent indeed...
> As far as I now understand, Oracle can use a hint or it can use it not.
> The
> 'hinting behaviour' is not predictable, but it doesn't seem to hinder
> 'normal (?)' execution of sql.
>
> Might it be possible, that hints are somehow tied in with a marketing
> policy
> when Oracle needed something to talk about??
>
> Rob Zijlstra
> -----------------------------
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Cary Millsap
> Sent: Monday, March 08, 2004 2:39 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: oracle can ignore hints
>
> Excellent!
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dan Tow
> Sent: Sunday, March 07, 2004 10:32 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: oracle can ignore hints
>
> Actually, I was told by reliable sources (and this is consistent with
> events
> I've observed) that it *tends* to be the later-created index, but in
> reality it
> is just whichever index the optimizer happens to find first, which is
> not
> strictly predictable, so apparently you cannot strictly count on the tie
>
> continuing to break the same way it has in the past, or in any way you
> can
> control, as long as there is a tie. This of course means that awkward
> things
> can sometimes happen at awkward times, say a few hours before your
> end-of-
> quarter. I've seen this happen, though it appears to be very rare.
>
> Tie breaking on the CBO is much more sensibly predictable, favoring the
> lower-
> sorted index based on the alphabetical order of the index names. Ties
> should
> also be rarer in the CBO, though they are not as rare as you might
> imagine,
> owing to rounding.
>
> Dan Tow
> dantow@xxxxxxxxxxxxxx
> 650-858-1557
> www.singingsql.com
> We make SQL sing!
>
>
> Quoting Cary Millsap <cary.millsap@xxxxxxxxxx>:
>
> > It's "later."
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > * Nullius in verba *
> >
> > Upcoming events:
> > - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lim, Binley
> > Sent: Sunday, March 07, 2004 8:44 PM
> > To: 'oracle-l@xxxxxxxxxxxxx'
> > Subject: RE: oracle can ignore hints
> >
> >
> > IIRC, RBO had that thing where in the event of an index-tie (2 indexes
> > qualify equally according to the rules), the index with a later
> creation
> > date(or is it earlier?) is picked.
> >
> > > -----Original Message-----
> > > From: Jared Still [SMTP:jkstill@xxxxxxxxxx]
> > > Sent: Saturday, March 06, 2004 3:58 AM
> > > To: Oracle-L Freelists
> > > Subject: Re: oracle can ignore hints
> > >
> > > I have had that exact same experience.
> > >
> > > Two Sun servers, different number of CPU's and RAM,
> > > same versions of Oracle 7 down to the patch level,
> > > data exported from one database into the other.
> > >
> > > Strictly RBO, two very different execution paths.
> > >
> > > Very frustrating, wish I could recall the solution we used.
> > >
> > > Jared
> > >
> > >
> > >
> > >
> > > On Fri, 2004-03-05 at 00:41, Kresimir Fabijanic wrote:
> > > > Hi Ryan
> > > >
> > > > What version of Oracle and OS are you running? What hint did you
> > try to
> > >
> > > > use? How complex was query?
> > > >
> > > > I have seen a case (OK it was 7.3.4.0 in 1999 on Sun's OS) where
> two
> >
> > > > databases - same version, same OS same Oracle and OS patches on
> both
> > -
> > > > hardware configuration was slightly different (not 100% sure but
> > there
> > > > were differences in RAM (1G vs 2G or 4G) and number of CPUs - (2
> vs
> > 4)
> > > > and some disk layout differences) both databases running RULE
> > (software
> > > > vendor requested [Keystone - if you have ever heard of it]) -
> > identical
> > > > statement (cut and paste between two terminal sessions (and back))
>
> > > > generating very different execution plans.
> > > >
> > > > I tried to verify few times and results were consistently
> different.
> >
> > > > Nothing would surprise me any more.
> > > >
> > > > Kind regards
> > > >
> > > > Kresimir Fabijanic
> > > >
> > > > ryan.gaffuri@xxxxxxx wrote:
> > > >
> > > > >We had a discussion on here regarding hints. some of you stated
> > that
> > > Oracle cannot ignore a proper hint. I have a TAR open and asked
> about
> > > this. Here is the response.
> > > > >
> > > > >
> > > > >
> > > > >"A hint simply adds weight to a cost estimation, it can still be
> > > overridden by the CBO if the values aren't seen as useful."
> > > > >
> > > > >
> > > > >----------------------------------------------------------------
> > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > >----------------------------------------------------------------
> > > > >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > > >put 'unsubscribe' in the subject line.
> > > > >--
> > > > >Archives are at //www.freelists.org/archives/oracle-l/
> > > > >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > > >-----------------------------------------------------------------
> > > > >
> > > > >
> > > > >
> > > >
> > > > ----------------------------------------------------------------
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > ----------------------------------------------------------------
> > > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at //www.freelists.org/archives/oracle-l/
> > > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > > -----------------------------------------------------------------
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> >
> > This communication is confidential and may contain privileged
> material.
> > If you are not the intended recipient you must not use, disclose, copy
> > or retain it.
> > If you have received it in error please immediately notify me by
> return
> > email
> > and delete the emails.
> > Thank you.
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
