Re: 11G Statistics Collection
From: onedbguru <onedbguru_at_yahoo.com>
Date: Sat, 14 Apr 2012 19:55:29 -0700 (PDT)
Message-ID: <3927941.1776.1334458529589.JavaMail.geo-discussion-forums_at_vbxy18>
On Saturday, April 14, 2012 12:58:03 PM UTC-4, Mladen Gogala wrote:
> 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
Date: Sat, 14 Apr 2012 19:55:29 -0700 (PDT)
Message-ID: <3927941.1776.1334458529589.JavaMail.geo-discussion-forums_at_vbxy18>
On Saturday, April 14, 2012 12:58:03 PM UTC-4, Mladen Gogala wrote:
> 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
In addition, for those very large partitioned tables, it is recommended to remove the GLOBAL statistics as 11g will use the aggregate of the partitions to calculate the GLOBAL stats dynamically. This can be much better for those long running stats jobs. Received on Sat Apr 14 2012 - 21:55:29 CDT