At 01:14 PM 12/4/2003, you wrote:
Hi Justin
Didn't know you were on the list
I'm usually about a week behind, so I don't get to participate very
often...
> A properly formed hint will
cause the CBO to consider the
> hinted path to be
> less costly than it would otherwise consider it, but hints do
> not force a
> query to use that particular plan.
>
> If you want to force Oracle to use a particular plan, plan
> stability is
> orders of magnitude easier!
Umm, but if you look at plan stability you will see that it is
implemented as hints (and query rewrite) - typically loads of them.
A
hint *does* force you to do what it says, if it is being 'ignored'
then
likely you haven't excluded alternative access paths. Of course for
any
sufficiently complex query (in my case that means 3 or more joins)
then
manually specifying an access path with hints becomes a too
difficult
problem.
I've never looked under the covers on plan stability, so now I'm get
confused. My understanding was that plan stability forced a query
to follow a particular execution plan. My understanding of hints,
however, was that they were only suggestions that the CBO could
ignore. Tom Kyte writes (second or third response down):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1951680913800
1) yes -- IF it accepts the hint, hints are just that --
hints. They are NOT directives, they are suggestions. It took
the suggestion in this case.
If plan stability is just Oracle applying a bunch of hints, and hints are
only suggestions, does that imply that the CBO can ignore plan
stability?
Justin Cave
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Justin Cave
INET: jcave_at_cableone.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Dec 05 2003 - 14:09:35 CST
