Help us understand the problem. What is going on with this article?

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> 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> exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'HR',OPTIONS => 'GATHER AUTO');

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

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

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

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

では、さっそく始めてみる
まずは、10万行から1レコードを選択する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> 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> 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> 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」で実行されたことが分かる。

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> 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> 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> 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> 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の違い。

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'で共有可能ではなかったため、新しい実行計画ができたようだ。

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件だけだった。

参考

mkyz08
SIer&バックエンドエンジニア&日曜プログラマー。 Apache Camel/VoltDB/Oracle/Apache karaf。 基本的に仕事外での自分用のメモ(興味があること)として記事を書いています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away