Re: want to stop analyze once the database is stable

On 4/2/06, zhu chao <zhuchao_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l