RE: Plan stability

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