Re: how to analyze table estimate?

Hi Joe,

It of course depends.

One of the things it depends on is database version which unfortunately you haven't provided.

Recommendation one would be to stop using the analyze command and start using the dbms_stats package.

If you haven't got the resources or time to compute statistics, then estimating a 10% sample is reasonable. However if you have some space oddities in the way your data is distributed, you may have problems, highlighted by inappropriate execution plans being generated. In such cases, increasing the percentage may be necessary.

Note that 9i has a new dbms_stats.auto_sample_size value for the estimate_percentage parameter in dbms_stats in which Oracle determines the appropriate percentage. My understanding is that Oracle will continue to calculate statistics until it reaches a point where the statistics are no longer changing by any significant amount. It basically takes the guess work out of the equation.

Cheers

Richard
"Joe Sath" <dbadba62_at_hotmail.com> wrote in message news:e0kg9.1146$kv3.863_at_nwrddc01.gnilink.net...
> Could someone tell me how much percentage should we use to estimate
> statistics?
> ie,
> Analyze table xxx estimate statistics sample how much percent?
>
> I usually use 10 percent, is that enough?
>
> Thanks for your reply
>
>
Received on Fri Sep 13 2002 - 07:33:34 CDT