Re: Add/Drop partition and CBO statistics

Hi!

When you delete a partition, naturally it's statistics get deleted as well. Now when you add another partition, it is initially without statistics, which means CBO has either to use default statistics or depending on optimizer_dynamic_sampling parameters value to read few blocks from your partition and extrapolate stats from there. This may lead to bad execution plans.

The correct way would be to analyze your partition after adding it, but if you can't do it because don't want to cause additional load, you might want to use dbms_stats to manually set statistics to your new partition. You should first get the statistics from somewhere, possibly from a similar partition. Then you can do the real analyzing later on if you need.

Note that global table statistics aren't recalculated when you drop a partition. (Well, the documentation claims that if you set _minimal_stats_aggregation to false, then global stats are recalculated, but I haven't managed to get it working).

Part of your problem is probably that ALTER TABLE DROP/ADD partition command you use to modify partitions will make all of table's dependent objects (views, pl/sql) invalid, they require recompiling what means library cache locks & latching. Also, all dependent SQL in library cache is invalidated as well, meaning hard parses -> library cache/shared pool latching.

In some cases this issue can be relieved by recycling partitions and using alter table truncate partition for emptying partitions, this doesn't at least invalidate dependent database objects, but in your case it might not help.

Tanel.

• 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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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