Re: dbms_stats no invalidate Parameter

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 Received on Tue Oct 28 2008 - 09:38:43 CDT