FreeLists / oracle-l / Re: want to stop analyze once the database is stable

["Jared Still" <jkstill@xxxxxxxxx>]

On 4/2/06, zhu chao <zhuchao@xxxxxxxxx> wrote:
>
> Thanks, Chris for your valuable inputs!
> Yes, we are using 2 percent analyze now. It is bad, maybe. We might
> need to adjust it to some higher value, The constriant is, the tables
> are huge and 10 percent sometimes never finish, or fail with 1555.
> But maybe those huge table are rarely analyzed, as it will be  a very
> long time for those huge tables to grow another 5 percent in size. I
> do  need to audit which tables are analyzed at each time.
>
>
Investigate the use of BLOCK_SAMPLE with dbms_stats.

It can make a significant difference in the amount of time
that it takes to analyze.  10% is probably too much if you
have large tables.

Consider testing with estimate_percent=>1, block_sample=>true

block_sample may generate statistics you don't like. It is dependant
on data distrubution in the table.

HTH

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist