Re: dbms_stats no invalidate Parameter

From: <>
Date: Tue, 28 Oct 2008 18:03:12 -0700 (PDT)
Message-ID: <>

On Oct 28, 10:38am, Yong Huang <> wrote:
> On Oct 25, 3:06 pm, 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 depdent 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.
> Sometimes you don't want the cursors to be invalidated at the time you
> gather stats. For instance, there're a lot of SQLs being run during
> the day. Perhaps you'd rather they be invalidated at night. But you
> don't want to gather stats at night either. There're legitimate cases.
> Oracle gives you the flexibility.
> Another case is that when you gather table stats with cascade=>true,
> index stats are also gathered. But their stats are not gathered at
> exactly the same time (difference will be quite noticeable for large
> tables). You'd rather the cursors using the tables and indexes be
> invalidated when both stats are completely refreshed. You can hold off
> invalidation for a while.
> Yong Huang

Thanks a lot, I had not thought of these scenarions. Received on Tue Oct 28 2008 - 20:03:12 CDT

Original text of this message