Re: dbms_stats no_invalidate parameter

From: DA Morgan <>
Date: Sat, 25 Oct 2008 13:39:24 -0700
Message-ID: <> wrote:
> dbms_stats.gather_schema_stats (.., no invalidate=>FALSE, )
> I am on on HP UNIX. I am trying to understand the pupose of
> no_invalidate parameter. It is my understanding that default value of
> this parameter used to be FALSE, but in Oracle 10g it is
> AUTO_INVAIDATE i.e., Oracle will decide when to make dependent cursors
> invalid.
> What is the drawback of setting it to FALSE, i.e., when new statistics
> is calculated, Oracel will use it for all cursors (dependent or non
> dependent) I will like my new staistics to be used ASAP.
> Apprecaite your insight.
> Thanks a lot.

Keep in mind that when you invalidate a plan you are forcing the optimizer to throw out an existing plan that may be optimal and replace it with a new plan which will be the next time the statement is run. It may, or may not, due to data skew, the as good as what you had. You've a bit of hard parsing coming your way.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sat Oct 25 2008 - 15:39:24 CDT

Original text of this message