Cursor Invalidation is not working in Oracle 19c

sabyakgp

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.

Paulzip

Works for me. Maybe try selecting something that relies on the stats and also look at all of the v$sql cursors for the query, not just the previous one.

create table t_test_1 as
select level as id
from dual
connect by level <= 100
/

Table created.


alter table t_test_1 add constraint pk_t_test_1 primary key (id)
/

Table altered.


select * from t_test_1 where id between 20 and 30
/


        ID
----------
        20
[snip...]
        29
        30


11 rows selected.

select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
from v$sql
where sql_text like '%from t_test_1%'
/


SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
-------------|------------|----------|----------|-----------|-------------|-------------------
2tf5p22k0124j|           0|         1|         1|          1|            0|VALID
5ftwnxd23bt8f|           0|         1|         1|          1|            0|VALID


exec dbms_stats.gather_table_stats(user, 'T_TEST_1', no_invalidate => false);

PL/SQL procedure successfully completed.


select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
from v$sql
where sql_text like '%from t_test_1%'
/


SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
-------------|------------|----------|----------|-----------|-------------|-------------------
2tf5p22k0124j|           0|         2|         1|          2|            0|VALID
5ftwnxd23bt8f|           0|         1|         1|          1|            1|INVALID_UNAUTH


select * from t_test_1 where id between 20 and 30
/


        ID
----------
        20
[snip...]
        29
        30


11 rows selected.


select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
from v$sql
where sql_text like '%from t_test_1%'
/


SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
-------------|------------|----------|----------|-----------|-------------|-------------------
2tf5p22k0124j|           0|         3|         1|          3|            0|VALID
5ftwnxd23bt8f|           0|         1|         2|          1|            1|VALID

Paulzip

Works for me. Maybe try selecting something that relies on the stats and also look at all of the v$sql cursors for the query, not just the previous one.

create table t_test_1 as
select level as id
from dual
connect by level <= 100
/

Table created.


alter table t_test_1 add constraint pk_t_test_1 primary key (id)
/

Table altered.


select * from t_test_1 where id between 20 and 30
/


        ID
----------
        20
[snip...]
        29
        30


11 rows selected.

select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
from v$sql
where sql_text like '%from t_test_1%'
/


SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
-------------|------------|----------|----------|-----------|-------------|-------------------
2tf5p22k0124j|           0|         1|         1|          1|            0|VALID
5ftwnxd23bt8f|           0|         1|         1|          1|            0|VALID


exec dbms_stats.gather_table_stats(user, 'T_TEST_1', no_invalidate => false);

PL/SQL procedure successfully completed.


select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
from v$sql
where sql_text like '%from t_test_1%'
/


SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
-------------|------------|----------|----------|-----------|-------------|-------------------
2tf5p22k0124j|           0|         2|         1|          2|            0|VALID
5ftwnxd23bt8f|           0|         1|         1|          1|            1|INVALID_UNAUTH


select * from t_test_1 where id between 20 and 30
/


        ID
----------
        20
[snip...]
        29
        30


11 rows selected.


select sql_id,child_number,executions,loads,parse_calls,invalidations, object_status
from v$sql
where sql_text like '%from t_test_1%'
/


SQL_ID       |CHILD_NUMBER|EXECUTIONS|     LOADS|PARSE_CALLS|INVALIDATIONS|OBJECT_STATUS
-------------|------------|----------|----------|-----------|-------------|-------------------
2tf5p22k0124j|           0|         3|         1|          3|            0|VALID
5ftwnxd23bt8f|           0|         1|         2|          1|            1|VALID

mathguy

Like Paulzip, I am unable to reproduce what you are reporting. no_invalidate => FALSE works as expected on my system (Oracle 12.2.0.1).

Just to make sure - in your post you seem to do things in the right order; you check the INVALIDATIONS count and the OBJECT_STATUS without running the SELECT statement again. That is as it should be; if you do run it again before checking, the statement is re-validated. Just making sure you didn't accidentally (or otherwise) re-run the statement after gathering stats but before checking V$SQL.

You may also want to include SQL_TEXT in the query against V$SQL to make sure you are looking at the right cursor.

sabyakgp

mathguy and Paulzip. Thanks for your answers. I tried the example you have provided but it still did not work. What finally worked, and quite interestingly, is when I issued a DDL to the table adding a new column. It's then when the INVALIDATIONS got incremented by 1 and OBJECT_STATUS to INVALID_AUTH.

Rainer Stenzel

Check out the enhanced fine-grained-cursor-invalidations. Updating statistics may only mark the cursor for rolling invalidation (IS_ROLLING_INVALID) and create a new child with roll_invalid_mismatch on next execution.

SELECT CHILD_NUMBER, EXECUTIONS, PARSE_CALLS, INVALIDATIONS, DDL_NO_INVALIDATE, IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID, OBJECT_STATUS FROM V$SQL WHERE SQL_ID='<sql id>'

https://asktom.oracle.com/pls/apex/asktom.search?tag=invalidation-of-child-cursor

https://blogs.oracle.com/optimizer/fine-grained-cursor-invalidation

<quote>

There are four actions that we can take for a cursor:

Cursor remains valid -- this is used when we know that the cursor is safe to execute and that the cursor's plan is still optimal. 

  For example, modifying a table partition to read only.

Cursor is marked for rolling invalidation -- this is used when we know that the cursor is safe to execute but its plan may be sub-optimal. 

 For example, if we create a visible index, then the cursor is safe to execute 

 but we want to recompile it sometime in the future so the new index can be considered for the plan.

Cursor is marked for rolling invalidation with metadata refresh -- this is used when the cursor is safe to execute after refreshing some of its metadata at runtime. 

 For example, if we rebuild an index that is used by the cursor, 

 then the cursor can be executed if we refresh the metadata for the index so the cursor uses the new index segment.

Cursor is invalidated -- this is used if the cursor is no longer safe to execute. 

 For example, if we drop an index that is used by the cursor, 

 we must invalidate the cursor so it is recompiled to get a new plan that does not use the index.

 Note that actions 1, 2, and 3 correspond to DDL_NO_INVALIDATE, IS_ROLLING_INVALID, and IS_ROLLING_REFRESH_INVALID columns in V$SQL. 

Action 4 is simply the existing invalidation action that was used prior to the fine-grained invalidation feature.

</quote>

Looks like the INVALIDATION counter is only incemented for Action 4 with fine-grained-cursor-invalidations.