Re: dbms_stats no_invalidate parameter
Date: Sat, 25 Oct 2008 13:39:24 -0700
> 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
> 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.orgReceived on Sat Oct 25 2008 - 15:39:24 CDT