RE: Plan stability

Correct. The point is that stored outlines can be viewed as a tool for those who like the "predictability" of the RBO. When the RBO is no longer available, the best way I know of to force the CBO to use your plan is stored outlines.

Gudmundur

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of Wartiak Rastislav
> Sent: 2. desember 2003 09:39
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Plan stability
>
>
> AFAIK RBO cannot be used for partitioned tables, not talking
> about the fact that RBO might not be supported in future versions.
>
> > What is complicated about stored outlines? If you don't like those
> > you can always go back to the RBO.
> >
> > Gudmundur
> >
> >> -----Original Message-----
> >> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]
> On Behalf
> >> Of Wartiak Rastislav Sent: 2. desember 2003 08:44
> >> To: Multiple recipients of list ORACLE-L
> >> Subject: Plan stability
> >>
> >>
> >> Hi,
> >>
> >> 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..
> >>
> >> 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?
> >>
> >> Thx,
> >> rw
> >>
> >>> 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).
> >>
> >
> > --
> > 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gudmundur Bjarni Josepsson
  INET: gbj_at_index.is

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