[12c R2] CURSOR_INVALIDATIONパラメータ

01.CURSOR_INVALIDATIONとはどのようなパラメータか？

DDL文の実行時に、変更されたオブジェクトに依存する共有カーソルを無効化するかどうかを制御するパラメータです。 デフォルトのCURSOR_INVALIDATION=IMMEDIATEでは、DDL実行時に共有カーソルを無効化します。 CURSOR_INVALIDATION=DEFERREDでは、DDL実行時には共有カーソルを無効化せず、無効化するタイミングを遅延させます。 このパラメータはOracle Database 12.2から導入されました。

02.実行結果：CURSOR_INVALIDATION=DEFERREDによる共有カーソル

デフォルトのCURSOR_INVALIDATION=IMMEDIATEでは、DDL実行時に共有カーソルを無効化します。 以下ではCREATE INDEX文の実行により、SELECT * FROM tbl01に対応する共有カーソルが無効化（v$sql.invalidations: 0→1）されています。

SQL> create table tbl01 (n1 number, n2 number, n3 number);
表が作成されました。
SQL> -- SQLの実行(sql_id=0w6gjmc6qdmpu)
SQL> SELECT * FROM tbl01;
レコードが選択されませんでした。
SQL> define sql_id=0w6gjmc6qdmpu
SQL> col sql_text format a40
SQL> SELECT sql_text, loads, invalidations, is_rolling_invalid
2    FROM v$sql
3    WHERE sql_id ='&sql_id';
旧   3:   WHERE sql_id ='&sql_id'
新   3:   WHERE sql_id ='0w6gjmc6qdmpu'
SQL_TEXT                                      LOADS INVALIDATIONS I
---------------------------------------- ---------- ------------- -
SELECT * FROM tbl01                               1             0 N
SQL> CREATE INDEX idx01 on tbl01(n1);
索引が作成されました。
SQL> SELECT sql_text, loads, invalidations, is_rolling_invalid
2    FROM v$sql
3    WHERE sql_id ='&sql_id';
旧   3:   WHERE sql_id ='&sql_id'
新   3:   WHERE sql_id ='0w6gjmc6qdmpu'
SQL_TEXT                                      LOADS INVALIDATIONS I
---------------------------------------- ---------- ------------- -
SELECT * FROM tbl01                               1             1 N

CURSOR_INVALIDATION=DEFERREDでは、DDL実行時には共有カーソルを無効化せず、無効化するタイミングを遅延させます。 以下では、CURSOR_INVALIDATION=DEFERRED設定後にCREATE INDEX文の実行すると、 SELECT * FROM tbl01に対応する共有カーソルの無効化が実行されず（v$sql.invalidationsが0のまま）、 遅延カーソル無効化の対象になっている（v$sql.is_rolling_invalid=’Y’）ことがわかります。

SQL> ALTER SESSION SET CURSOR_INVALIDATION = DEFERRED;
セッションが変更されました。
SQL> CREATE INDEX idx02 on tbl01(n2);
索引が作成されました。
SQL> SELECT sql_text, loads, invalidations, is_rolling_invalid
2    FROM v$sql
3    WHERE sql_id ='&sql_id';
旧   3:   WHERE sql_id ='&sql_id'
新   3:   WHERE sql_id ='0w6gjmc6qdmpu'
SQL_TEXT                                      LOADS INVALIDATIONS I
---------------------------------------- ---------- ------------- -
SELECT * FROM tbl01                               2             1 Y

03.注意点

※共有カーソルの遅延無効化はALTER TABLE、CREATE INDEXなどのDDLでも指定できます。共有カーソルの遅延無効化の指定にはDEFERRED VALIDATION句を使用します。

04.従来機能との関連性

Oracle Database 12.1以前では、デフォルトのCURSOR_INVALIDATION=IMMEDIATE相当の動作でした。 CURSOR_INVALIDATIONパラメータ、およびCURSOR_INVALIDATION=DEFERREDの動作は、DDLに依存する共有カーソルが多数ある状況において、DDL実行時に多数の共有カーソルが無効化され、多数のカーソルを再解析しなければならなり、データベースの負荷が増加する状況を回避するために導入したと思われます。

05.参考情報

※CURSOR_INVALIDATION – OracleR Databaseリファレンス 12cリリース2 (12.2) E72905-02 [https://docs.oracle.com/cd/E82638_01/refrn/CURSOR_INVALIDATION.html]
※ローリング無効のマークが付けられたカーソル OracleR Database SQLチューニング・ガイド 12cリリース2 (12.2) E72902-01 カーソル共有によるReal-World Performanceの改善 [https://docs.oracle.com/cd/E82638_01/tgsql/improving-rwp-cursor-sharing.html#GUID-0BCC0D7A-F1E9-4173-B123-F175BC236588]