Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_STATS.DELETE_INDEX_STATS does _not_ delete index statistics ??

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 3 Nov 2003 15:54:00 -0000
Message-ID: <bo5ubi$cve$1$8300dec7@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 Mon Nov 03 2003 - 09:54:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US