Re: Add/Drop partition and CBO statistics

I'm do not think stored outline/hints is the answer. Part of the problem is the partitions are of different sizes and the optimizer needs to know and handle them (partitions) differently. In Oracle7 partitioned views, each partition was explained separately, but this was gone with Oracle8 onwards.

If the problem is with CBO statistics, then the answer is to estimate as high a percentage as you can, for the new partitions. If this was the problem, then I would have expected more "scattered/sequential reads", not all waiting for "latch free". Oracle9 gets more annoying to interpret given the "null event" bugs, but this appears to have been fixed in 9.2.0.3. The version wasn't mentioned in this post, but "all waiting for latch free" is not a good sign. Call Oracle Support and participate in a dump'ing exercise is my suggestion.

BTW, local indexes are the only way to go -- I've never understood the point of global indexes on partitioned tables -- maybe someone else can?

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

> If we are talking about two sql only, maybe you can use stored outline.
>
> Yechiel Adar
> Mehish
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, December 02, 2003 8:34 AM
>
>
> > 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
> > --
> > Author: zhu chao
> > INET: chao_ping_at_vip.163.com
> >
> > 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: Yechiel Adar
> INET: adar76_at_inter.net.il
>
> 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: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

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