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 -----------------------------------------------------------------