Re: update stats on partitioned tables
Date: Fri, 3 Apr 2009 03:40:34 -0400
Message-ID: <OF140E2630.59FA287A-ON8525758D.002A1815-8525758D.002A2AB1_at_lazard.com>
Very valuable information.
Thank you Randolf.
Mayen
"Randolf Geist" <info_at_sqltools-plusplus.org>
Sent by: oracle-l-bounce_at_freelists.org
Apr 03 2009 03:17 AM
Please respond to
info_at_sqltools-plusplus.org
To
oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 03 2009 - 02:40:34 CDT