Re: DBMS_STATS.DELETE_INDEX_STATS does _not

From: dias <ydias_at_hotmail.com>
Date: 2 Nov 2003 14:18:54 -0800
Message-ID: <55a68b47.0311021418.2ac9dced@posting.google.com>

Hi Jonathan,

In 9i, you can choose to invalidate or not dependent statements when you analyze or delete stats with dbms_stats with the parameter "no_invalidate":

DBMS_STATS.DELETE_INDEX_STATS (

   ownname       VARCHAR2, 
   indname       VARCHAR2,
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade_parts BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT FALSE);

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bo3eb0$rfi$1$8300dec7_at_news.demon.co.uk>...
> The problem persists in 9.2 and 10.1 beta.
> Probably worth the OP raising a TAR so
> that Oracle Corp. can bug it and fix it.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> ____Belgium__November (EOUG event - "Troubleshooting")
> ____UK_______December (UKOUG conference - "CBO")
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Wolfgang Breitling" <john.doe_at_aol.com> wrote in message
> news:Xns9423CED40E3FAbreitliwcentrexcccom_at_198.161.157.145...
> >
> > 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.
> >
Received on Sun Nov 02 2003 - 16:18:54 CST

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