Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Cursor Invalidation is not working in Oracle 19c

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.
Best Answer
-
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
Answers
-
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
-
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 theOBJECT_STATUS
without running theSELECT
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 checkingV$SQL
.You may also want to include
SQL_TEXT
in the query againstV$SQL
to make sure you are looking at the right cursor. -
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.
-
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.