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

Did you do the following?:

1. Query runs fast
2. Analyze *all* tables and their indexes.
3. Query runs slow
4. Delete statistics on one of the index.
5. Query runs slow

??
There are other statistics on other tables and indexes? You did not revert it to the original state ..
How do you expect that you'll get the same execution plan as before? In fact there are statistics on the table itself.

Then you don't specify .. which oracle version? Exactly how you analyzed the tables/indexes? ....

Anurag

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message 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
>
>
Received on Tue Oct 28 2003 - 09:36:05 CST