Search

Top 60 Oracle Blogs

Recent comments

DDL invalidates your SQL right ?

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_VALIDATE 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.