Re: Index Statistics not gathered even after runing dbms_stats.GATHER_TABLE_STATS

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 24 Dec 2009 10:15:26 +0000
Message-ID: <7765c8970912240215u3c64db9o5143063c590001c1_at_mail.gmail.com>

You don't mention version which is somewhat important here. You haven't specified the cascade parameter so you are relying on either the default value which in 9.2 (and earlier I imagine) is FALSE - i,e don't gather index stats. In some later versions you can set the default using the package itself and again it could be set to false. I think the ealier version is more likely but you never know. Personally I like to run with the 10gR2 defaults but explicitly specify them.

Niall

On Thu, Dec 24, 2009 at 8:31 AM, Dwaraknath Krishnamurthi < dwarak.k_at_gmail.com> wrote:

> Hi All,
>
> I am trying to collect statistics to a table 'T' which is owned by 'S'
> schema.
> When i try to gather statistics to the table, I see that the table
> statistics are gathered but the index statistics are still not gathered.
> I use the following command to collect the statistics.
> EXEC DBMS_STATS.GATHER_TABLE_STATS('S', 'T',estimate_percent => 30);
>
> I confirmed the statics of index are not gathered by quering
>
> select * from dba_indexes
> where table_name = 'T'
>
> columns leaf_blocks ,distinct_keys,clustering_factor,num_rows shows value
> 0
> and the last_analyzed does not show the date on which i ran the
> gather_table_stats.
>
> Why does DBMS_STATS.GATHER_TABLE_STATS not gather statistics for the index?
>
> I have not tried running gather_index_stats or rebuilding the index.
>
> --
> Thanks,
> Dwarak.K
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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

Received on Thu Dec 24 2009 - 04:15:26 CST