Very valuable information. Thank you Randolf. Mayen "Randolf Geist" <info@xxxxxxxxxxxxxxxxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx Apr 03 2009 03:17 AM Please respond to info@xxxxxxxxxxxxxxxxxxxxx To oracle-l@xxxxxxxxxxxxx cc Subject Re: update stats on partitioned tables > I would recommend: > - gather partition stats (granularity=>'partition') > - gather table stats (granularity=>'global') > Gather partition stats for the loaded partitions, then gather global > stats. This will save the time of gathering stats on data that has > not changed. > Starting with 10.2.0.4 you can consider using DBMS_STATS.COPY_TABLE_STATS to > copy stats from a previous partition to the new partition. This would ensure > your execution plans are the same across all partitions. Another option on top of the two mentioned above is the newly introduced "APPROX_GLOBAL AND PARTITION" option in 10.2.0.4 It's a kind of backport simulation of the new incremental statistics feature introduced in 11g. The optimizer group has published a blog note about maintaining statistics on large <a href=" http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html ">partitioned tables</a> which covers these new options. Note that both copy_table_stats and APPROX_GLOBAL AND PARTITION seem to require one-off patches to be installed to work properly, as mentioned in the blog post. See also Metalink Note 7116357.8. Regards, Randolf Oracle related stuff blog: http://oracle-randolf.blogspot.com/ SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/ http://sourceforge.net/projects/sqlt-pp/ ____________________________________________________________________ Psssst! Schon vom neuen WEB.DE MultiMessenger gehört? Der kann`s mit allen: http://www.produkte.web.de/messenger/?did=3123 -- //www.freelists.org/webpage/oracle-l