Re: Add/Drop partition and CBO statistics

Hi, Yechiel:

    It is difficult to implement. As there is hundreds of SQL querying the tables, nobody knows which SQL get other execution path after CBO get diffirent statistics.

Thanks
Zhu Chao.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 02, 2003 5: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: 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).