Re: dbms_stats no invalidate Parameter

From: <zigzagdna_at_yahoo.com>
Date: Tue, 28 Oct 2008 18:03:12 -0700 (PDT)
Message-ID: <e475ce70-6e8a-4734-add2-6124f143ab30@v30g2000hsa.googlegroups.com>


On Oct 28, 10:38am, Yong Huang <yong..._at_yahoo.com> wrote:
> On Oct 25, 3:06 pm, zigzag..._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 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