Posted by 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 
Hey Connor:-),
You’re a person absolutely assiduous and hard-working on Oracle tech. (because I received your this note via my Email at 02:13 today, by the way I’ve followed your blog). Why I such say something? At that moment I woke up and was glanced at my cell phone – It’s your blog post news.
After I read it I seem to feel whether your keyword “DDL_NO_VALIDATE” should be “DDL_NO_INVALIDATE”? Because from the SQL select statement on V$SQL I just discover “DDL_NO_INVALIDATE”.
One more thing, could you share your script “pr.sql” with me? It’s a showing format about row switching to column. In the meantime I found you’ve used it many times on your other post(s).
Best Regards
Quanwen Zhao
https://quanwenzhao.wordpress.com
I got it from Tanel Poder
======================================
— Notes: This script is based on Tom Kyte’s original printtbl code ( http://asktom.oracle.com )
— For coding simplicity (read: lazyness) I’m using custom quotation marks ( q’\ ) so
— this script works only from Oracle 10gR2 onwards
def _pr_tmpfile=c:\tmp\pr.out
set termout off
store set &_pr_tmpfile.set replace
set termout on
set serverout on size 1000000 termout off
save &_pr_tmpfile replace
set termout on
0 c clob := q’\
0 declare
999999 \’;;
999999 l_theCursor integer default dbms_sql.open_cursor;;
999999 l_columnValue varchar2(4000);;
999999 l_status integer;;
999999 l_descTbl dbms_sql.desc_tab;;
999999 l_colCnt number;;
999999 begin
999999 dbms_sql.parse( l_theCursor, c, dbms_sql.native );;
999999 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999 for i in 1 .. l_colCnt loop
999999 dbms_sql.define_column( l_theCursor, i,
999999 l_columnValue, 4000 );;
999999 end loop;;
999999 l_status := dbms_sql.execute(l_theCursor);;
999999 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999 dbms_output.put_line( ‘==============================’ );;
999999 for i in 1 .. l_colCnt loop
999999 dbms_sql.column_value( l_theCursor, i,
999999 l_columnValue );;
999999 dbms_output.put_line
999999 ( rpad( l_descTbl(i).col_name,
999999 30 ) || ‘: ‘ || l_columnValue );;
999999 end loop;;
999999 end loop;;
999999 exception
999999 when others then
999999 dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999 raise;;
999999 end;;
/
set termout off
@&_pr_tmpfile.set
set termout on
get &_pr_tmpfile nolist
host del &_pr_tmpfile
======================================
True the exact column_name is DDL_NO_INVALIDATE.
Ah thanks for spotting the typo – will correct it.