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

"Anurag Varma" <avarmadba.skipthis_at_yahoo.com> schrieb im Newsbeitrag news:F1wnb.33393$1C5.6700_at_nwrdny02.gnilink.net...
>
> Did you do the following?:
>

0. was: entire schema is already analyzed, *except* this one index.

> 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
>

Yes, this is exactly what I did, from 1. to 5. ; and at 5. I began to wonder.

> There are other statistics on other tables and indexes?

The entire schema is analyzed regularly every two weeks.

> You did not revert it to the original state

Obviously true, that is what I hoped to acheive with DBMS_STATS.DELETE_INDEX_STATS.
But I only do it with ANALYZE INDEX ... DELETE STATISTICS; only then I had my
old execution plan back in place.

And I wonder why ...
and *this* was the thing I am interested - the question why this index is ignored in most cases
is another question which depends of course on the query details;

I just wanted to know:
why is DBMS_STATS.DELETE_INDEX_STATS behaving different from ANALYZE INDEX ... DELETE STATISTICS ?

> ..
> How do you expect that you'll get the same execution plan as before? In
fact
> there are statistics on the table itself.

I expected it because I only played with one variable at a time, and this time
I checked out what happens when statistics for one index is there or not, the rest
of statistics was unchanged, for a systematic approach.

Then I made a second test row with /*+rule*/ in the query, testing for the same
statistic settings.

>
> Then you don't specify .. which oracle version?

Sorry, I think I did:

> > P.S.: Oracle EE 8.1.7.3 on AIX 4.3.3,
> > Oracle Reports (about 500 in total - 1 going mad ...)
> Exactly how you analyzed the tables/indexes? ....

exec DBMS_UTILITY.ANALYZE_SCHEMA('mySchema','COMPUTE'); over night, needs about 2 hours.

> Anurag
>

Thank You for Your comments. Do You have an idea ?

Jan

>
> "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 Wed Oct 29 2003 - 01:48:03 CST