Re: DBMS_STATS.DELETE_INDEX_STATS does _not_ delete index statistics ??

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in news:bnm1qc$12cfrg$1_at_ID-152732.news.uni-berlin.de:

> Hello,
>
> recently I had a phenomenon which is beyond my humble comprehension
> {{{8|
>
> I have a very simple index on a non-partitioned table
>
> CREATE INDEX I_SUBSCRIPTORS_CLEARING_FLAG
> ON SUBSCIRPTORS
> (
> PROD_ID, CLEARING_FLAG
> );
>
> When this index is just created,
> but with_out_ being ANALYZEd,
> an Oracle Report query needs just 30 seconds. So far so good.
>
> When analyzed, e.g. when making new statistics for a schema,
> then the index is completely ignored,
> no matter what CBO hints, all execution plans are worse,
> the query lasts 1 hour.
>
>
> But now comes the strange thing:
>
> When I do the follwing
>
> exec DBMS_STATS.DELETE_INDEX_STATS
> (
> 'myUser',
> 'I_SUBSCRIPTORS_CLEARING_FLAG'
> );
>
> in order to get rid of that statistics again,
> the index is _still_ being ignored ! The execution plan is the same
> as if the index kept statistics in a hidden place ...
>
> User_Indexes.Last_Analyzed shows null again, so:
> Is DBMS_STATS.DELETE_INDEX_STATS working correctly ???
> (This is a general question.)
>
>
> I had expected that the index would be treated
> just like before, right after creation.
> Now, that appears to be false.
>
> Do I really have to drop and re-create
> that special index when I run new schema statistics ?
> I hoped that DBMS_STATS.DELETE_INDEX_STATS
> would be a work-around, but it was not ...
>
> Or are Outlines the only one solution left ?
> I did RTFM and expect that some execution plans may go mad
> when doing new statistics, but in most cases we could handle it by
> hints.
>
> This strange case is the only one in my system.
>
>
> P.S.: Oracle EE 8.1.7.3 on AIX 4.3.3,
> Oracle Reports (about 500 in total - 1 going mad ...)
>
> I would appreciate any comments,
> TIA
>
> Jan
>
>

I did some tests and found

1. dbms_stats.delete_index_stats and analyze index delete statistics do exactly the same thing as far as removing the statistics from xxx_indexes. The update statement to sys.ind$, including the values, are identical from both commands
2. dbms_stats.delete_xxx_stats does not invalidate the index statistics present in the library cache, while analyze ... delete statistics does. I tested it with delete_index_stats, delete_table_stats and delete_schema_stats. Although I have not tested it for delete_database_stats, I see no reason to believe that matters would be different there. The deleted table statistics become effective immediately but the index statistics linger. A select from xxx_indexes shows the updated values, i.e. nulls for the statistics values, but the CBO is using what is cached in the library cache. When you follow the dbms_stats.delete_xxx_stats with a flush shared_pool then the CBO uses its statistics defaults for the index instead of the old memorized statistics.

-- 
What lies behind us and what lies before us are small matters when
compared to what lies within us. 

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP