Oracle 12cR2: DDL deferred invalidation

In a previous post I described the new V$SQL views about rolling invalidation. I did the example with dbms_stats which is able to do rolling invalidation since 11g. But more is coming with 12.2 as you can use rolling invalidation for some DDL. When you do some DDL on an object, dependent cursors are invalidated. On a busy database it’s something to avoid. I’ve seen recently a peak of hanging sessions during 10 minutes and the root cause was an ALTER TABLE ADD COMMENT. This do not change anything in the execution plan, but the cursors are invalidated. And doing that on a central table can trigger a hard parse storm.

For the example, I’ll reduce the rolling invalidation window to 25 seconds as I don’t want to wait hours:

SQL> alter system set "_optimizer_invalidation_period"=25;
System altered.

Currently 12.2 is available only on Oracle Database Express Cloud Service, but please don’t ask me how I was able to set an underscore parameter there. However you can reproduce the same by waiting 5 hours instead of 25 seconds.

I create a simple table and prepare my query to check cursor information:

SQL> create table DEMO as select * from dual;
Table created.

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
no rows selected

I run a simple query and check the cursor:

SQL> SELECT * FROM DEMO;

D
-
X

SQL>
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           1          1 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    N

1 parse call, cursor loaded, hard parsed and executed.

I create an index on the table with the DEFERRED INVALIDATION new syntax:

SQL> create index DEMO on DEMO(dummy) deferred invalidation;
Index created.

I see that it is flagged as rolling invalid:

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           1          1 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    Y

When I run it, a timestamp is set within the rolling invalidation window (5 hours by default, but here 25 seconds):

SQL> SELECT * FROM DEMO;

D
-
X

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X

As you see, this Y/N flag has a third value to show that it has been executed after being rolling invalidated.

I wait 30 seconds:

SQL> host sleep 30

From that point, the invalidation timestamp has been reached so a new execution will create a new child cursor:

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X

SQL> SELECT * FROM DEMO;

D
-
X

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X
SELECT * FROM DEMO              0          1           1          1 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:45    N

You must be careful here. If you used to check the INVALIDATIONS column, then you may miss the rolling ones. INVALIDATIONS is for parent cursors. IS_ROLLING_INVALID is for invalidated child cursors.

Note that, of course, until the invalidation, the new index will not be used by those cursors. So if you create the index to solve an performance issue you may prefer to invalidate the cursors immediately.

Same can be done with index rebuild:

SQL> alter index DEMO rebuild deferred invalidation;
Index altered.

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X
SELECT * FROM DEMO              0          1           1          1 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:45    Y

SQL> SELECT * FROM DEMO;

D
-
X

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:46    X

Of course, rolling invalidation can happen only for the cursors that do not use the index. The execution plans which use the index are invalidated immediately.

With the same restriction, you can do it when you set an index unusable

SQL> alter index DEMO unusable deferred invalidation;

Index altered.

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:46    X

SQL> SELECT * FROM DEMO;

D
-
X

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';

SQL_TEXT            INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO              0          1           2          2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16    X
SELECT * FROM DEMO              0          1           3          3 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:46    X

You get the same behavior if you drop the index: cursors that do not use it are no invalidated immediately.

From my tests, you can add DEFERRED INVALIDATION when you MOVE TABLE, but invalidation is immediate. Only when moving partitions, the rolling invalidation occurs.

An alternative to specify DEFERRED INVALIDATION in the DDL is to set it as the default:

SQL> show parameter cursor_invalidation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_invalidation                  string      IMMEDIATE

SQL> alter session set cursor_invalidation=deferred;

Session altered.

But remember, not all DDL will do rolling invalidation, even when the syntax is accepted.