Re: Optimizer Stats collection for Datawarehouses
Date: Thu, 19 Nov 2009 12:10:13 +0100 (CET)
Message-ID: <63847.213.162.65.111.1258629013.bloek_at_pwebmail6.utanet.at>
Hi Sanjay,
In case the partition and loading schema are equal the statistics should be calculated by the application (as Nuno pointed) - i.e. create partition, load and analyze in one step. If the partitioning and loading schema differ highly (e.g. monthly partition populated in real time) the same is valid as for OLTP objects (monitoring or periodic gathering).
> when new partition created and utiliZed can experience some performance
> slowness on the first day of the month
One possible solution is to set some precalculated statistics after the
creation of the empty partition. The idea behind is: the CBO is probably
less confused if it things that all partitions are full filled. One
catastrophic scenario of projection of the statistics from empty partition
to filled partition is documented under
http://www.jlcomp.demon.co.uk/faq/bind_peek.html
Additionally:
Staging objects (used few times only) are not analyzed and processed with
dynamic sampling
Regards,
Jaromir
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 19 2009 - 05:10:13 CST