Re: table stats

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 21 Apr 2004 10:04:53 +0000 (UTC)
Message-ID: <c65h05$jj6$1@sparta.btinternet.com>

analyze table X estimate statistics;

    includes limited collection of column-level stats     (high, low, num_distinct, num_null)

method_opt=>null

    does not collect ANY column level stats.

I run a test case for every method_opt when I upgrade an Oracle version. (And for every variant of ANALYZE) to check which versions of analyze go with which method_opt.

Look at stats value in:

    user_tables
    user_indexes
    user_tab_columns
    user_tab_histograms

and their partitioned equivalents.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Daud" <daud11_at_hotmail.com> wrote in message
news:f0bf3cc3.0404202225.4145b7f5_at_posting.google.com...


> Hi All

>

> This morning we reloaded two of our tables and then I ran the

> dbms_stats.gather_table_stats to collect statistics. The two tables

> have pretty much the same the number of records as before (more than 8

> mil each).

> I found that when I did this it caused the exec plan of some of our

> queries to change (to one that was not the best) and performance

> degraded by quite a lot.

> Then, out of curiousity I collected statictics from the 2 tables

> using:

> analyze table <mytab> estimate statistics.

> After I did that, the exec plan changed to what it was before and

> everything went back to normal. Why is this? What is the difference

> what method 1 (dbms_stats) and method 2 (analyze). I thought method 1

> is the way to go.

> The exact command: dbms_stats.gather_table_stats ( ownname => null,

> tabname => 'MYTAB', method_opt => null, cascade => true,

> estimate_percent => 20).

> Oracle version 8.1.7.4 on HP-UX 11.0.

>

> regards

> Daud

Received on Wed Apr 21 2004 - 05:04:53 CDT