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

Have you tried removing the statistics on the table as well? This may make a difference. I would try the following: Run the statistics on the table (and indexes)

Set the session into CBO and run an explain plan. Now do the same thing in Rule base. In theory (don't you like that....?) You should see some significant differences. It may be that the query is not very efficient and may need "tweaking." CBO is not as intelligent as we may like it to be. At times, you may actually need to remove the statistics off of a table in order to get it to perform better. Welcome to the world of Oracle :-)

Jan Gelbrich wrote:
> 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 - 14:04:10 CST