Re: how to analyze table estimate?

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:6Nkg9.31091$g9.89506_at_newsfeeds.bigpond.com...
> 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
> >
> >
>
>

Richard's advice is, IMHO, sound as always. My 2c is that I'd turn the problem around, and specify as large a percentage as the available time-window allows.

HTH,
Paul Received on Fri Sep 13 2002 - 13:56:34 CDT