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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
