dbms_stats no invalidate Parameter

[zigz...@yahoo.com]

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.

Apprecaite your insight.

Thanks a lot.

[ddf]

Posting the exact same message twice does not ingratiate you with
those who regularly respond nor does it improve the speed with which
responses appear.

Stop doing so.

David Fitzjarrell

[Yong Huang]

On Oct 25, 3:06 pm, zigzag...@yahoo.com wrote:

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

[zigz...@yahoo.com]

Thanks a lot, I had not thought of these scenarions.