Hi
According to SQL Tuning Guide (Oracle 12.2 and Oracle 19c) when we gather statistics of a table the existing cursor gets invalidated (when invoked with NO_INVALIDATE=FALSE) and V$SQL.INVALIDATIONS incremented by 1 (originally 0) and V$SQL.OBJECT_STATUS shows INVALID_UNAUTH (originally VALID). I have conducted the following test (as given in the SQL Tuning Guide - 19c) but neither INVALIDATIONS column incremented by 1 nor OBJECT_STATUS becomes INVALID_AUTH.
SELECT COUNT(*) FROM sales;
--Get the SQL ID
SELECT PREV_SQL_ID SQL_ID FROM V$SESSION WHERE SID = SYS_CONTEXT('userenv', 'SID');
--Check V$SQL
SELECT CHILD_NUMBER, EXECUTIONS, PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS FROM V$SQL WHERE SQL_ID = '<sql id>'
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------
0 1 1 0 VALID
--Gather statistics with NO_INVALIDATE = FALSE
EXEC DBMS_STATS.GATHER_TABLE_STATS('sh','sales',no_invalidate => FALSE);
--Check V$SQL again
SELECT CHILD_NUMBER, EXECUTIONS, PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS FROM V$SQL WHERE SQL_ID = '<sql id>'
--No change in the INVALIDATIONS and OBJECT_STATUS columns
Is there any other factors preventing the cursor invalidation? Your insights would be much helpful.