Re: update stats on partitioned tables

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Fri, 03 Apr 2009 09:17:25 +0200
Message-Id: <576972592_at_web.de>

> 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

Received on Fri Apr 03 2009 - 02:17:25 CDT