Re: 11G Statistics Collection

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 14 Apr 2012 16:58:03 +0000 (UTC)
Message-ID: <pan.2012.04.14.16.58.03_at_gmail.com>

On Sat, 14 Apr 2012 05:32:10 -0700, vsevolod afanassiev wrote:

> There is also some mystery with sample size: quite often plan could be
> fixed simply by collecting stats with 100% sample size.
> Why 5% isn't enough for table with 100,000,000 rows

Oracle 11G has a new algorithm called "one pass distinct sampling" which is very effective in determining the number of distinct values in the table columns (NDV). However, the new algorithm is incompatible with the ESTIMATE_PERCENT setting, so with 11G it is advisable to use DBMS_STATS.AUTO_SAMPLE_SIZE and set table preferences where that takes too long.
Also, it is possible to have stats collected in parallel, which speeds things up tremendously.

-- 
http://mgogala.byethost5.com

Received on Sat Apr 14 2012 - 11:58:03 CDT