Re: Plan stability

Notes in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 02, 2003 9:04 AM

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

Hints do not change the cost of a query - they simply dictate that Oracle should take the stated path and not consider alternatives which would otherwise be possible at that point in the 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.

(a) One would hope that a hinted query would also have some

    documentation describing the expected execution plan, and     the reaons why it was considered desirable - so the maintenance     issues should be moot.

(b) Plan stability exists to stop execution plans from changing - so

    any SQL with a plan should, by your comment above, require     it's stored outline to be put under review in case any new functionality     should be applied and the outline changed. So, again, your point     is not entirely sound.

>
> If you want to force Oracle to use a particular plan, plan stability is
> orders of magnitude easier!
>

    Only if you happen to have the licence for the 9.2 performance     tuning pack, and can use the dinky little GUI for drawing and     manipulating outlines.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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