RE: Estimate versus Compute

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 [mailto:breitliw_at_centrexcc.com] Sent: Monday, July 25, 2005 12:04 PM
To: Allen, Brandon
Cc: ganstadba_at_hotmail.com; mark.powell_at_eds.com; Oracle-L_at_freelists.org; klange_at_ppoone.com
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
>
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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.

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