dbms_stats and no_invalidate

Since at least 10.2.0, Oracle has decided by default when to invalidate a dependent cursor when query optimizer statistics are gathered. You can determine what setting you have by the following:

SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL>

This probably works well if you are using the default statistics gathering job. If you find yourself in a position where you have to gather optimizer statistics out of that window, please make sure you set no_invalidate => false when you call dbms_stats to ensure query plans are regenerated.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.