| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Cary Millsap
Sent: Monday, March 08, 2004 2:39 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dan Tow
Sent: Sunday, March 07, 2004 10:32 PM
To: oracle-l_at_freelists.org
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_at_singingsql.com
650-858-1557
www.singingsql.com
We make SQL sing!
Quoting Cary Millsap <cary.millsap_at_hotsos.com>:
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lim, Binley
> Sent: Sunday, March 07, 2004 8:44 PM
> To: 'oracle-l_at_freelists.org'
> 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_at_cybcon.com]
> > 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_at_cox.net 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_at_freelists.org
> > > >put 'unsubscribe' in the subject line.
> > > >--
> > > >Archives are at http://www.freelists.org/archives/oracle-l/
> > > >FAQ is at http://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_at_freelists.org
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > FAQ is at http://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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 08 2004 - 07:52:38 CST
 |
 |