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

Hi,

Thanks.

Small test shows that dbms_stats does not clear table or index stats. dba_XXX show no statistics, but v$sql_plan still show that the objects are analyzed and the cbo still use it.

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bo5ubi$cve$1$8300dec7_at_news.demon.co.uk>...
> Unfortunately, the problem is nothing to do with
> whether or not dependent cursors are invalidated
> (in fact, the default action in 9 is the same as it
> was in 8 - don't you love double negatives).
>
> The problem is that the rowcache is not cleared
> of index-related statistics, so the invalid cursors
> are re-parsed and re-optimised using statistics
> which shouldn't be there.
>
> --
> 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
>
>
> "dias" <ydias_at_hotmail.com> wrote in message
> news:55a68b47.0311021418.2ac9dced_at_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
> >
Received on Tue Nov 04 2003 - 08:46:22 CST