RE: Estimate versus Compute
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Mon, 25 Jul 2005 13:53:33 -0700
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@xxxxxxxxxxxxx]
Sent: Monday, July 25, 2005 12:04 PM
To: Allen, Brandon
Cc: ganstadba@xxxxxxxxxxx; mark.powell@xxxxxxx; Oracle-L@xxxxxxxxxxxxx;
klange@xxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
