Re: Plan stability

Btw, the one good use for plan stability & stored outlines are poorly written 3rd party applications, where you can't even add a hint to code. I these cases you can compose a good execution plan yourself and set your statement to use it.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, December 04, 2003 10:14 PM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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