ThanX !

"Wolfgang Breitling" <john.doe_at_aol.com> schrieb im Newsbeitrag news:Xns9423CED40E3FAbreitliwcentrexcccom_at_198.161.157.145...
> "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
>
> a) 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
>
> b) 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

Thank You, Wolfgang,

I think You have found the cause and an explanation that I can understand. Thank You very much for Your efforts !

Also Thanks to all others who greatly gave me hints about this matter. So I will use ANALYZE ... DELETE STATISTICS in the future instaed.

Yours, Jan Received on Thu Oct 30 2003 - 03:29:47 CST