Re: update stats on partitioned tables
- From: Mayen.Shah@xxxxxxxxxx
- To: info@xxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 3 Apr 2009 03:40:34 -0400
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
Other related posts:
