RE: oracle can ignore hints
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 7 Mar 2004 20:32:22 -0800
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
-----------------------------------------------------------------
Other related posts:
