Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Estimate versus Compute

RE: Estimate versus Compute

From: Allen, Brandon <>
Date: Mon, 25 Jul 2005 13:53:33 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C4502361332@NT15.oneneck.corp>

The database with the results I mentioned earlier is version 8.0.6, so I'm using dbms_utility (dbms_stats didn't exist yet).

I switched from this:

EXECUTE dbms_utility.analyze_schema('BAAN','COMPUTE');

To this:

EXECUTE dbms_utility.analyze_schema(schema=>'BAAN',method=>'ESTIMATE',estimate_percent=>25,method_opt=>'FOR TABLE'); EXECUTE dbms_utility.analyze_schema(schema=>'BAAN',method=>'COMPUTE',method_opt=>'FOR ALL INDEXES');

And the runtime dropped from 38 hours to 14 hours. There is no parameter for specifying row/block sampling with dbms_utility, so I'm guessing that it is using row sampling by default - but maybe I'm wrong? Maybe it is still reading every block, but still saving a lot of time on the CPU/computation side.

-----Original Message-----
From: Wolfgang Breitling [] Sent: Monday, July 25, 2005 12:04 PM
To: Allen, Brandon
Subject: Re: Estimate versus Compute

Are you using
a) analyze or dbms_stats
b) block sampling or row sampling

The argument for estimate > 1% causing all blocks to be read anyways goes something like this:
If you are using row sampling with 25% (for the sake of argument) and you have more than 4 rows per block on average then a random sampling of 25% (1 out of every 4 rows) is likely to give you a row from every block. That argument of course doesn't hold if you are using block sampling. Then a 25% sample will read 1/4 of all blocks.

Allen, Brandon wrote:
> Mike, I don't believe that is true. I recently reduced from compute to estimate=25% and this reduced the runtime for update stats from 38 hours to 14 hours. I don't think it would make such a difference if it still had to visit every block. I think you can specify whether your samples are based on rows or blocks and Oracle will automatically compute if you specify estimate >= 50%.
> Regards,
> Brandon


Wolfgang Breitling
Centrex Consulting Corporation

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

Received on Mon Jul 25 2005 - 15:54:10 CDT

Original text of this message