I stumbled upon this post by optimizer guru Nigel Bayliss last week, so please have a read of that first before proceeding. But I wanted to show a simple demo of how management of cursors continues to improve with each version of the database.
Throughout the many years and versions of using Oracle, a common mantra has been: if you perform DDL on a table, then any SQL cursors that reference that table will become invalidated and re-parsed on next execution. That makes a good deal of sense because if you (say) drop a column from a table, then a “SELECT * FROM MY_TABLE WHERE ID = 123” is a significantly different proposition than it was before the drop of the column. The asterisk (‘*’) now resolves to something different, and the SQL might not even be valid anymore if the column that was dropped was the ID column. A re-parse is mandatory.
But not all DDL’s are equal, or perhaps a better phrasing would be “not all DDLs are as severe as others”, and since parsing is an expensive operation, any time that we can avoid having to do it is a good thing. Some DDLs are so “innocuous” that we know that a re-parse is not required. Here is an example of that in action in 18c.
I’ll create a table, issue a simple query on it, and check its details in V$SQL. I’m using the cool “set feedback on sql_id” facility in SQL*Plus 18c to immediately get the SQL_ID.
SQL> create table t as select * from all_objects;
Table created.
SQL> set feedback on sql_id
SQL> select count(*) from t;
COUNT(*)
----------
76921
1 row selected.
SQL_ID: cyzznbykb509s
SQL> select
2 sql_text
3 ,sql_fulltext
4 ,sql_id
5 ,executions
6 ,parse_calls
7 ,invalidations
8 ,ddl_no_invalidate
9 ,is_rolling_invalid
10 ,is_rolling_refresh_invalid
11 from v$sql where sql_id = 'cyzznbykb509s'
12 @pr
==============================
SQL_TEXT : select count(*) from t
SQL_FULLTEXT : select count(*) from t
SQL_ID : cyzznbykb509s
EXECUTIONS : 1
PARSE_CALLS : 1
INVALIDATIONS : 0
DDL_NO_INVALIDATE : N
IS_ROLLING_INVALID : N
IS_ROLLING_REFRESH_INVALID : N
PL/SQL procedure successfully completed.
Now I’ll perform a DDL on the table that does not change the structure, the data or the security privileges on the table
SQL> alter table t read only;
Table altered.
In versions of yester year, this would invalidate any cursors even though nothing about the table has changed. But with 18c, the DDL_NO_INVALIDATE column tells us that even though a DDL was performed, we did not need to invalidate the cursor.
SQL> select
2 sql_text
3 ,sql_fulltext
4 ,sql_id
5 ,executions
6 ,parse_calls
7 ,invalidations
8 ,ddl_no_invalidate
9 ,is_rolling_invalid
10 ,is_rolling_refresh_invalid
11 from v$sql where sql_id = 'cyzznbykb509s'
12 @pr
==============================
SQL_TEXT : select count(*) from t
SQL_FULLTEXT : select count(*) from t
SQL_ID : cyzznbykb509s
EXECUTIONS : 1
PARSE_CALLS : 1
INVALIDATIONS : 0
DDL_NO_INVALIDATE : Y
IS_ROLLING_INVALID : N
IS_ROLLING_REFRESH_INVALID : N
PL/SQL procedure successfully completed.
SQL>
Note that this column in V$SQL is also present in most versions of 12c, but does not appear to be populated reliably until you get to 18c.
Learning is not a spectator sport 
Got some thoughts? Leave a comment