RE: Plan stability

Unless the 3rd-party product generates the SQL dynamically, causing anywhere from 1 to 12000 items to be included in an "IN" clause. <sigh>

Plan stability requires statement stability, doesn't it? If I'm wrong, I'd sure like to figure out how to bypass the ORDERED hint in those statements using stored outlines!

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

-----Original Message-----
Sent: Thursday, December 04, 2003 3:19 PM To: Multiple recipients of list ORACLE-L

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: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).