Hi,
After setting the table prefs NO_INVALIDATE = FALSE, I gather the table stats, but the cursor is still valid.
Does anybody know why? My database version is 11.2.0.3
SQL>alter system flush shared_pool;
SQL>exec DBMS_STATS.RESET_GLOBAL_PREF_DEFAULTS;
SQL>exec dbms_stats.set_table_prefs('sh','customers','no_invalidate','false');
SQL>SELECT /* TEST_CURSOR */ COUNT(*) FROM SH.CUSTOMERS;
SQL>SELECT SQL_ID, EXECUTIONS, INVALIDATIONS, OBJECT_STATUS, sql_text FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* TEST_CURSOR%';
SQL_ID EXECUTIONS INVALIDATIONS OBJECT_STATUS SQL_TEXT
------------- ---------- ------------- ------------------- ------------------------------------------------------------
70yb9jk9t1y91 1 0 VALID SELECT /* TEST_CURSOR */ COUNT(*) FROM SH.CUSTOMERS
SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS');
SQL>SELECT SQL_ID, EXECUTIONS, INVALIDATIONS, OBJECT_STATUS, sql_text FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* TEST_CURSOR%';
SQL_ID EXECUTIONS INVALIDATIONS OBJECT_STATUS SQL_TEXT
------------- ---------- ------------- ------------------- ------------------------------------------------------------
70yb9jk9t1y91 1 0 VALID SELECT /* TEST_CURSOR */ COUNT(*) FROM SH.CUSTOMERS