DDL invalidates your SQL right ?

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

2 comments

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

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

      ======================================

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.