Re: dbms_stats no_invalidate parameter

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 25 Oct 2008 13:39:24 -0700
Message-ID: <1224967158.220000@bubbleator.drizzle.com>


zigzagdna_at_yahoo.com wrote:
> dbms_stats.gather_schema_stats (.., no invalidate=>FALSE, …)
>
> I am on 10.2.0.4 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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Oct 25 2008 - 15:39:24 CDT

Original text of this message