Add/Drop partition and CBO statistics

From: zhu chao <chao_ping_at_vip.163.com>
Date: Mon, 01 Dec 2003 22:34:25 -0800
Message-ID: <F001.005D8593.20031201223425@fatcity.com>

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). Received on Tue Dec 02 2003 - 00:34:25 CST