Re: Cost Based Analysis

Ken Whitaker wrote:
>
> What is the difference between
>
> analyze table XXXX ESTIMATE statistics;
> analyze table XXXX COMPUTE statistics;
 

> Anyone noticed anything, I tend to tell folks to use the estimate as it
> runs faster; why would the
> compute be better to use.
>
> Platform Solaris 2.5.1 / Oracle 7.3.2.3 / 50,000 rows - 2mil row tables
>
> Thanks,
> kwhitake_at_moon.jic.com

Very good question. However, it varies with different versions of ORACLE. Under most of the 7.1.x versions estimate was buggy. How buggy was it. Well it would put invalid values in the tab$/dba_tables fields which would throw off the cost based optimizer. Some bad values were 32 for number of distinct values, high value, low value, ... I have experienced also problems with computing statistics (yes not estimating) on very large tables (> 25 million rows or 10 Gb). So we were faced with estimating and manually updating the tab$ columns that were corrupted by calculating the values ourselves. Of course this is not condoned by ORACLE but my question to them was "didn't somebody check the code before you released it?".

So, in summary: Estimate may be as good as computing for large tables provided you sample a sufficient large number of rows (statistics tells us that 30% is good), and you check that statistics to see if they make sense. I usually compute whenever possible. In the above example there was also a problem that ORACLE didn't manage its space well and used over 15 Gb to compute statistics on 10 Gb (who knows why).

If you need some help viewing statistics then check out our utils at http://dbghome.computek.net They are free and run on Unix. The new version runs on any intranet server with SQL*Plus or SQL*Net and is web based.

Hope this helps.

-- 
||----------------------------------------------------------------------||
|| Marc Marchioli
|| The DataBase Group, Inc.                       voice:  214-528-9459
|| 4011 Travis St.                                fax:    214-528-9459
|| Dallas, TX 75204-7512
|| marcm_at_computek.net                         @
|| (Challenging ORACLE and UNIX every day)    |-}ORACLE,UNIX
||                                            /\-------------->