Re: ANALYZE and ESTIMATE vs. COMPUTE

Tad Davis wrote:
>
> Does anyone have suggestions or recommendations about how best to specify
> percentages for the ANALYZE command?
>
> I'm trying to figure out whether to use ESTIMATE vs. COMPUTE in a set of
> ANALYZE statements, and if ESTIMATE, what percentage to specify. (This is
> for Oracle 7.1.) In the course of checking the manuals, I found the
> following statements:
>
> SQL Language Reference Manual (Dec 1992) - p 4-91:
>
> "SAMPLE specifies the amount of data from the analyzed object Oracle
> samples to estimate statistics. If you omit this parameter, Oracle samples
> 1064 rows."
>
> Application Developer's Guide (Dec 1992) - p 5-11:
>
> "Estimation is often much faster than computation, especially for such
> large tables, because estimation never reads more than 1064 rows from the
> table."
>
> As stated, the ADG description makes it appear that specifying large
> percentages or numbers of rows > 1064 is useless. I can't help but wonder
> if some qualifying phrase has dropped out: for example, "...estimation (by
> default) never reads more than 1064 rows..."
>
> In any case, I'm looking for a rule of thumb. I would prefer to use
> percentages rather than a number of rows, because the tables are
> constantly growing, and I want to make sure the sample size is large
> enough to be valid. On the other hand, some of the tables are > 6,000,000
> rows, and I want the analyze to run with reasonable dispatch. If I
> specify 20% of 6,000,000 rows, ANALYZE may read 1,200,000 rows, and I may
> be well into the area of diminishing returns.
>
> I would also like to use a standard percentage across the board so I don't
> have to keep tweaking it.
>
> Suggestions? Comments? Advice? Bitter experience?
>
> --
> Tad Davis 215-898-7864 voice
> (davist_at_isc.upenn.edu) 215-898-0386 fax

Tad,

        I'm familiar with 7.2 and 7.3, but not 7.1 However, if you have a powerful enough server with enough and fast-enough CPUs and RAM, you may want
to try COMPUTE first before playing with the ESTIMATE percentages. I suspect
you may do the trial-and-error method to arrive at a satisfactory balance between speed and validity.

                Tom Received on Thu Jan 23 1997 - 00:00:00 CST