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

Hi,

Could you give the exact syntaxe you've used to delete the index stats with DBMS_STATS.DELETE_INDEX_STATS ...

If you delete the stats with "no_invalidate" set to true, Oracle uses the existing execution plans. Restarting the db or flushing the shared pool is then necessary

Dias

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<bnnsud$13a4bv$1_at_ID-152732.news.uni-berlin.de>...
> "mommy dba" <mommydba_at_netscape.com> schrieb im Newsbeitrag
> news:vptiqas9nonr3e_at_corp.supernews.com...
> > Have you tried removing the statistics on the table as well?
>
> No, because the table is vital for the app, it has (too) many columns and 10
> indexes,
> and many parts of the app are basing on it.
> The ERM maybe suboptimal, but cannot be changed without heavy side effects
> (but of course my fingers tickle to do so ..)
> to the rest of the app (Forms and Reports).
>
> > This may
> > make a difference. I would try the following: Run the statistics on
> > the table (and indexes)
>
> The entire schema is analyzed regularly.
>
> >
> > Set the session into CBO and run an explain plan.
>
> System Init parameter is set to CHOOSE.
>
> >Now do the same thing
> > in Rule base.
>
> I tried /*+rule*/ ... but to no avail.
>
> >In theory (don't you like that....?) You should see some
> > significant differences.
>
> I did, and the best result I got was when the index had no statistics - the
> rest of course was analyzed.
> Strange ...
>
> >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.
>
> q.e.d.
>
> >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 :-)
> >
>
> OK, it is not a science, it is an art :-)
>
> Thanks for the comments, I was just curious about
> DBMS_STATS.DELETE_INDEX_STATS ...
> The index tuning is another question ...
>
> >
> >
> > Jan Gelbrich wrote:
> ...
Received on Wed Oct 29 2003 - 16:31:21 CST