Re: Plan stability

At 01:44 AM 12/2/2003, Wartiak Rastislav wrote:

>my question is about the same, but more general. How can i force Oracle to
>use my prefered way of explain plan and not use CBO's. I mean, apart from
>stored outlines, it somehow seems to complicated. I would like to say what
>order and join types it should use. But, try as I might, I many times
>cannot force Oracle to use my way, even though I know it is possible, for
>I saw this kind of explain plan for that specific query..

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

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

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

Justin Cave

> > Hi, list friends:
> > We are using partition to archive history data in our
> > production OLTP database. We get great performance gains(Far
> > less disk io), but we also hit performance trouble sometimes.
> > So I am here ask for your experience.
> >
> > We used local index on all partitioned tables.We add/drop
> > partition monthly to archive the history data.
> >
> > But the trouble is, when add/drop partition is being done
> > on the partitioned table, CBO sometimes changed SQL execution
> > path.We implemented partition 2 monthes ago, and in the first
> > time, add/drop partition went on quite smoothly, but in the
> > second time we add/drop partition, two SQL (just TWO SQL) get
> > bad execution path and server load rushed to 10 times(from 2
> > to 20 in uptime), all waiting for latch free event. It
> > severely affected our application. We are an online system
> > and we do not have scheduled time every month so we have to
> > add/drop partition while db is still running.
> >
> > So, with system still up and running, how do you add/drop
> > partition without changing the SQL execution path? We do not
> > have the time to reanalyze/dbms_stats the tables ,analyze
> > takes hours and if SQL execution path changed, during these
> > time, system is nearly unusable.
> >
> > I tried to import old
> > statistics(dbms_stats.import_table_stats), but did not fix
> > the problem.
> > So, can you share your experience on managing partitioned table?
> >
> > Regards
> >
> > Zhu Chao
> > www.cnoug.org
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>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).

Justin Cave
Distributed Database Consulting

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Justin Cave
  INET: jcave_at_cableone.net

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