RE: Plan stability

From: Wartiak Rastislav <RWartiak_at_cpoj.cz>
Date: Tue, 02 Dec 2003 01:34:26 -0800
Message-ID: <F001.005D85A6.20031202013426@fatcity.com>

> What's the objection to using stored outlines? That's the
> Oracle-provided mechanism for forcing queries to use a particular
> plan.

The problem is that I have to first analyze tables with real data for CBO to create plan I find useful (like using specific indexes etc.) and then to store it. When writing query I usually know what kind of data it will be there even before it is loaded there (and CBO cannot).

>> I tried to use hints like ordered and use_hj etc. Can someone give
>> some examples of full set of hints for some simple queries?

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

That's why I am asking. I was thinking of some simple way of telling oracle - scan this index first, do full scan of other table, use hash join then etc. If you have explain plain with 5 steps, why cannot I write these 5 steps together with query so Oracle takes it for granted that this is the best way to do it?

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wartiak Rastislav
  INET: RWartiak_at_cpoj.cz

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 Tue Dec 02 2003 - 03:34:26 CST