Adaptive cursor sharingでOracleオプティマイザの賢さを再確認してみた

# Adaptive cursor sharingについて

Oracle11gから追加されたバインドピーク機能は、バインド変数にセットされた値を見て実行計画を作成する。
最初にSQLが実行された際のバインド変数の値によって実行計画が作成され共有されるため、別の値が入った場合は適切ではない実行計画となることがありえる。その問題を解決する機能がAdaptive cursor sharingになる。

Adaptive cursor sharingでは、バインド変数に設定された値を監視し必要に応じて新しい実行計画を作成する。
それにより、最初のバインド変数の値によって実行計画が決まるというバインドピーク機能の欠点を解決している。
ただし、以下の2点の問題がある。

- 新しい実行計画を作成する前に、一度は適切でない実行計画でSQLが実行されてしまう。  
つまり、たまにSQLが遅くなる可能性が残る。ただし、それはそのうち解消する。
- バインド変数の値に応じて適切な複数の実行計画が作成されたとしても、共有プールからフラッシュされると同じ問題が発生する。

Adaptive cursor sharingの説明は今更なことなので、実際に動かして確認してみる。


# Adaptive cursor sharingを確認する

Oracle12cR2を使用してAdaptive cursor sharingを確認してみる。
といってもAdaptive cursor sharing自体は11gからの機能なので、11gでもほぼ同様の結果になる（はず）。
まずは、準備作業として確認用のテーブルとデータを作成する。

```sql
SQL> drop table TEST_TBL purge;
SQL> create table TEST_TBL (
    c1 number
);

SQL> insert into TEST_TBL select level from dual connect by level <= 100000;
SQL> commit;

SQL> update TEST_TBL set c1 = 100000 where c1 >= 10000;
SQL> commit;

SQL> select c1, count(*) from TEST_TBL where c1 >= 9990 group by c1 order by c1 desc;

        C1   COUNT(*)
---------- ----------
    100000      90001
      9999          1
      9998          1
      9997          1
      9996          1
      9995          1
      9994          1
      9993          1
      9992          1
      9991          1
      9990          1
```

TEST_TBLテーブルに10万件のデータを投入し、90001件は同じ値を持ち、それ以外はユニークな値を持つようにした。

次に統計情報を取得。

```sql
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'HR',OPTIONS => 'GATHER AUTO');

PL/SQLプロシージャが正常に完了しました。
```

そして、カラムC1にヒストグラムが作成されていることを確認する。
以下のSQLを実行すると、HISTGRAMが'HYBRID'になっていることが分かる。
詳しい話はおいといて、'NONE'以外であればOK。

```sql
SQL> select column_name, histogram from user_tab_cols where table_name = 'TEST_TBL';

COLUMN_NAME          HISTOGRAM
-------------------- --------------------
C1                   HYBRID

```

では、さっそく始めてみる
まずは、10万行から1レコードを選択するSQLを実行する。

```sql
SQL> variable v_c1 number
SQL> exec :v_c1 := 1
SQL> select max(c1), count(*) from TEST_TBL where c1 = :v_c1;

   MAX(C1)   COUNT(*)
---------- ----------
         1          1

```

V$SQLビューを確認。

```sql
SQL> set linesize 1000
SQL> column is_bind_sensitive format a20
SQL> column is_bind_aware format a15
SQL> column is_shareable format a15

SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='2shnfnt5hnczg';  
※注：事前にSQL_IDは確認済み。

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE   IS_SHAREABLE
------------ ---------- ----------- -------------------- --------------- ---------------
           0          1         231 Y                    N               Y
```

最初の実行なのでEXECUTIONS='1'になっている。
IS_BIND_SENSITIVE='Y'はバインド変数の再解析を行う可能性があることを示している。
再解析が必要になると、IS_BIND_AWARE='Y'になる。初回の実行なので、IS_BIND_AWARE='N'になっている。

以下で実行計画を確認。

```sql
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('2shnfnt5hnczg', 0, format=>'ALL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  2shnfnt5hnczg, child number 0
-------------------------------------
select max(c1), count(*) from TEST_TBL where c1 = :v_c1
 
Plan hash value: 1809449678
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |               |      1 |      1 |     3 |            |          |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| IDX_TEST_TBL1 |      1 |     18 |    54 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------
```

「INDEX RANGE SCAN」が選択されていることが分かる。

次に、10万件から90001件が検索されるSQLを実行する。

```sql
SQL> exec :v_c1 := 100000
SQL> select max(c1), count(*) from TEST_TBL where c1 = :v_c1;

   MAX(C1)   COUNT(*)
---------- ----------
    100000      90001
```

V$SQLビューを確認すると、実行計画は一つだけであり、不適切な「INDEX RANGE SCAN」で実行されたことが分かる。

```sql
select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='2shnfnt5hnczg';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE   IS_SHAREABLE
------------ ---------- ----------- -------------------- --------------- ---------------
           0          2         409 Y                    N               Y
```

再度、10万件から90001件が検索されるSQLを実行する。

```sql
SQL> exec :v_c1 := 100000
SQL> select max(c1), count(*) from TEST_TBL where c1 = :v_c1;

   MAX(C1)   COUNT(*)
---------- ----------
    100000      90001
```

V$SQLビューを確認すると、実行計画が1つ増えて2つになっていることが分かる。
また、IS_BIND_AWARE='Y'になっている。

```sql
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='2shnfnt5hnczg';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE   IS_SHAREABLE
------------ ---------- ----------- -------------------- --------------- ---------------
           0          2         409 Y                    N               N
           1          1         205 Y                    Y               Y
```

実行計画を確認すると、「INDEX RANGE SCAN」ではなく「INDEX FAST FULL SCAN」が選択されていることが分かる。賢い！
※インデックスに全てのカラムが含まれているのでテーブルフルスキャンではなく、く「INDEX FAST FULL SCAN」が選択されている。


```sql
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('2shnfnt5hnczg', 1, format=>'ALL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  2shnfnt5hnczg, child number 1
-------------------------------------
select max(c1), count(*) from TEST_TBL where c1 = :v_c1
 
Plan hash value: 1151349798
 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |       |    55 (100)|          |      1 |00:00:00.03 |     205 |
|   1 |  SORT AGGREGATE       |               |      1 |      1 |     3 |            |          |      1 |00:00:00.03 |     205 |
|*  2 |   INDEX FAST FULL SCAN| IDX_TEST_TBL1 |      1 |  89879 |   263K|    55   (0)| 00:00:01 |  90001 |00:00:00.02 |     205 |
---------------------------------------------------------------------------------------------------------------------------------

```

今度は、再度10万件から1レコードが選択されるSQLを実行する。

```sql
SQL> exec :v_c1 := 2
SQL> select max(c1), count(*) from TEST_TBL where c1 = :v_c1;

   MAX(C1)   COUNT(*)
---------- ----------
         2          1
```

実行すると、今度も実行計画が1つ増え、3つになっていることが分かる。
最初の子カーソルとの違いはIS_SHAREABLEの違い。

```sql
select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id='2shnfnt5hnczg';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE   IS_SHAREABLE
------------ ---------- ----------- -------------------- --------------- ---------------
           0          2         409 Y                    N               N
           1          1         205 Y                    Y               Y
           2          1           2 Y                    Y               Y
```

実行計画を確認すると、最初の子カーソルと同じ「INDEX RANGE SCAN」が選択された実行計画が作成されている。
最初の子カーソルはIS_SHAREABLE='N'で共有可能ではなかったため、新しい実行計画ができたようだ。

```sql
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('2shnfnt5hnczg', 2, format=>'ALL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  2shnfnt5hnczg, child number 2
-------------------------------------
select max(c1), count(*) from TEST_TBL where c1 = :v_c1
 
Plan hash value: 1809449678
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |               |      1 |      1 |     3 |            |          |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| IDX_TEST_TBL1 |      1 |      1 |     3 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------

```

これで、Adaptive cursor sharingによりバインド変数に設定された値を監視し必要に応じて新しい実行計画を作成されることが確認できた。ただし、これによってバインドピーク機能の欠点が全て解決するわけではなく、最初に記載した欠点は残ることに注意しなければならない。
ちなみにバインドピーク機能自体を使わないシステム（プロジェクト）も多い（特に11gでは多いと思う）。メリット・デメリットを勘案し決める。（私はオプティマイザの便利機能は全部使う派）

あと、Adaptive cursor sharingだけでなく、11gR2でカーディナリティフィードバック、12cR1で適応計画が追加され改善されてはいる（が、完全には解決しない）
「Adaptive cursor sharingでOracleオプティマイザの賢さを再確認してみた」というタイトルにしたけど、この辺りも説明しないと足りてないので後日書くかも。

なお、Qiitaで"Adaptive cursor sharing"を検索すると2件だけだった。

# 参考

- [しばちょう先生の試して納得！DBAへの道　第36回 SQLのパース処理とバインド変数の理解](https://www.oracle.com/technetwork/jp/database/articles/shibacho/index-2405696-ja.html)

- [SQL性能を最大限に引き出すDB 12cクエリー・オプティ - Otn - 日本オラクル](http://otndnld.oracle.co.jp/ondemand/technight/20170227-12c-optimizer.pdf)

- [Oracle Database 12c Release 2のSQL計画管理 by Oracle](https://www.oracle.com/technetwork/jp/database/multitenant/overview/twp-sql-plan-mgmt-12c-1963237-ja.pdf)

- [バッチ処理にバインド変数はもうやめません？ by 株式会社コーソル](http://www.csus4.net/d/wp-content/uploads/120406_BindVariablesOnBatch.pdf)

- [Bind Peek をもっと使おうぜ！(柴田 歩) ](https://www.slideshare.net/shibataayumu/20141203-jpoug-adventcalendarbindpeek)