Re: Add/Drop partition and CBO statistics

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