Hi, Tanel:
Thanks for your detailed suggestion.
Dropped partition is removed from the db and oracle won't think of it again.
There is a documented BUG :
BUGNO: 2306067
PROBLEM:
--------
ADD/COALESCE/MERGE/SPLIT partition sets the table stats to NULL in spite of
no-aggregation of index stats.
This bug seems to have severe impact on CBO statistics, but I cannot reproduce it
on my solaris 8172/linux 9204.Can someone reproduce? If yes, can you post your
detailed version and steps?
For the new partitions added, there is no data inside when it is added, new
records are inserted into this partitions(range partition).In the begin of the month,
there is a few records in the new added partition, as time goes by, the partition is
full and new partition is added. THe existing partitiones are seldom modified after
initial insert/later update.So it is difficult to decide when to analyze the new added
partition.If I analyze the partition when it is added, statistics soon get out of date
as new records are continuesly inserted into this partition.
I used dbms_stats to gather statistics:
exec dbms_stats.gather_table_stats(ownname=>'USERTEST',TABNAME=>'TESTPART',
CASCADE=>TRUE)
DDL invalid the SQLs and procedures, which will cause reparse/recompile, and need
to hold a lot of shared pool/library cache latch, but in my case, those latch are not
the main wait event, as statspack report latch section looks like that:
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- -------
cache buffers chains kcbgtcr: kslbegin 0 320,308 #######
cache buffers chains kcbrls: kslbegin 0 583 1,142
cache buffers chains kcbgcur: kslbegin 0 2 1
cache buffers chains kcbchg: kslbegin: bufs not 0 1 1
checkpoint queue latch kcbk0rrd: update recovery 0 1 0
enqueue hash chains ksqrcl 0 64 13
enqueue hash chains ksqgtl3 0 34 85
enqueue hash chains ksqcnl 0 6 6
enqueues ksqgtl2 0 54 18
enqueues ksqdel 0 46 1
enqueues ksqrcl 0 32 36
enqueues ksqgel: create enqueue 0 29 112
enqueues ksqies 0 14 8
global tx free list k2gfegte 0 4 4
global tx hash mapping K2GTBlatch 0 82 82
library cache kglpnal: child: alloc spac 0 87 48
library cache kglpnal: child: before pro 0 83 136
library cache kgldte: child 0 0 70 136
Reusing partitiones are not avaliable for us,as we partition the tables according
to primary key, which can't be reused.:(
Thanks
Regards
Zhu Chao.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 9:29 PM
> 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" <[EMAIL PROTECTED]>
> 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: [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).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tanel Poder
> 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).
>
>
--
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).
