RE: Plan stability

Hi Justin

Didn't know you were on the list

> 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. For a moderately
> complicated query,
> you'd potentially need a fair number of hints to get things
> working the way
> you want. If the statistics of the table changed, though,
> your carefully
> hinted query might well decide to take another path. Even if
> things work,
> adding hints-- particularly adding multiple hints-- to a query
> significantly increases the maintenance costs as future
> developers have to
> unravel what all the hints are doing, why they're doing it,
> whether any
> hints need to be changed as a result of the modifications,
> whether future
> changes to the CBO or new Oracle functionality should cause
> the ideal plan
> to change, etc.
>
> 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.

Niall

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

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