Oracle 11g検証 隠れた新機能検証 その6

<Oracle 11g検証 隠れた新機能検証 その6>
ペンネーム: ギャバン

前回に引き続き adaptive cursor sharing 機能の検証を行います。

今回は、実運用を想定した検証を行ってみたいと思います。
前回の検証では、フルスキャンの選択後にインデックススキャンが選択される
ようバインド変数に値を設定し、2 回目の実行時に実行計画が変更される様子
を確認しました。

  1 回目(検索範囲大)→ フルスキャン
  2 回目(検索範囲小)→ フルスキャン(再解析した方が良いと判断)
  3 回目(検索範囲小)→ インデックススキャン

しかし、実運用では、数十、数百、数千回 ・・・ とインデックススキャンが
実行された後、稀にフルスキャンが選択されるような値がバインド変数に設定
される(もしくは、その逆)というケースが考えられると思います。
adaptive cursor sharing 機能は、バインド変数に設定された値のトレンドを
分析し再解析を行うかどうか判断する、という機能です。
その為、トレンドの状況によって動作が異なるものと予想されます。

同じトレンドのバインド変数値が多数実行された場合、実行計画を変更するよう
なバインド変数値で実行されるとどうなるのでしょうか?
すぐに変わってくれるのでしょうか?それとも???

■検証準備

前回と同様のテストデータを利用します。

  SQL> desc ACS_TEST_TAB

  名前          NULL?  型
  ------------- ------ --------------
  ID                   NUMBER         ← 1~1000000 までの連番を格納
  RANDOM_DATA          VARCHAR2(20)   ← ランダムな文字列を格納

※ID 列にはインデックスを付与しています。

統計情報の再取得と共有プールのフラッシュを行い情報をリセットします。

  SQL> exec dbms_stats.delete_table_stats( -
       ownname => 'scott', -
       tabname => 'acs_test_tab');

  SQL> alter system flush shared_pool;

  SQL> exec dbms_stats.gather_table_stats( -
       ownname => 'scott', -
       tabname => 'acs_test_tab', -
       cascade => true);

尚、今回の検証では、adaptive cursor sharing 機能の追加に伴い v$sql に
追加された列 “is_bind_sensitive” と “is_bind_aware” の値を使い確認して
行きます。

(v$sql の実行例)

  SQL> select
         sql_id,
         child_number,
         is_bind_sensitive,
         is_bind_aware
       from
         v$sql
       where
         sql_id = '902qv35r4tzqx'
       /

  SQL_ID        CHILD_NUMBER I I
  ------------- ------------ - -
  902qv35r4tzqx            0 Y Y

(列の意味)

  is_bind_sensitive = 'Y' とは、再解析を行う可能性がある "候補" を意味
  します。この "候補" となった SQL カーソルのバインド変数に設定された
  値を観察し、実際に再解析を行う対象となった場合に is_bind_aware = 'Y'
  となります。

(値の組み合わせパターン)

  is_bind_sensitive   is_bind_aware
  ------------------- -------------
  N                   N             ← バインド変数を観察しない
  Y                   N             ← バインド変数を観察する
  Y                   Y             ← 値によって再解析を行う

■検証スタート!!

まず、インデックススキャンが選択されるよう、1~100 までの値をランダム
に設定して実行します。

  SQL> var v_id number
  SQL> exec :v_id := round(dbms_random.value(1,100),0);
  SQL> select max(random_data) from acs_test_tab where id  exec :v_id := round(dbms_random.value(200000,1000000),0);
  SQL> select max(random_data) from acs_test_tab where id  var v_id number
  SQL> exec :v_id := round(dbms_random.value(1,600),0);
  SQL> select max(random_data) from acs_test_tab where id <= :v_id;

これを、100回繰り返し・・・
この時点での v$sql の結果は、

  SQL_ID        CHILD_NUMBER I I
  ------------- ------------ - -
  902qv35r4tzqx            0 Y N
  902qv35r4tzqx            1 Y Y
  902qv35r4tzqx            2 Y Y
  902qv35r4tzqx            3 Y Y
   ・
   ・
   ・
  902qv35r4tzqx           45 Y Y
  902qv35r4tzqx           46 Y Y

両方共 'Y' となったカーソルが多数追加されました。
これにより再解析の対象となりますが、1~600 の範囲では、最大値の 600 が
設定された場合でも全体の 0.06% である為、結果として、全てインデックス
スキャンが選択されました。
この状況で、フルスキャンが選択されるべき値が設定された場合には、再解析
が行われ、期待通りフルスキャンが選択されることになります。

ここまでの検証結果より、実際に実行計画が変更されないようなケースでも、
バインド変数に設定される値の範囲によって、再解析の対象になることが確認
できました。

再解析の対象とするかどうかは、値の範囲で判断しているのでしょうか?
それとも、選択される件数の範囲で判断しているのでしょうか?

これを確認する為、別のケースで検証してみます。
ここまでは、"id create table ACS_TEST_TAB2 as select * from ACS_TEST_TAB;

  SQL> create index ACS_TEST_IDX2 on ACS_TEST_TAB2(id);
  SQL> update ACS_TEST_TAB2 set id = 999999 where id > 100000;
  SQL> commit;
  SQL> exec dbms_stats.gather_table_stats( -
       ownname => 'scott', -
       tabname => 'acs_test_tab2', -
       cascade => true);

これにより、以下の条件となるデータを生成しました。

・バインド変数に 1~100000 までが設定された場合、1 件のみヒット。
・バインド変数に 999999 が設定された場合、全体の 90% がヒット。

それでは、先ほどの検証で変化の見られた 1~600 の範囲で実行してみます。

  SQL> var v_id number
  SQL> exec :v_id := trunc(dbms_random.value(1,600));
  SQL> select max(random_data) from acs_test_tab where id  select
         sql_id,
         child_number,
         is_bind_sensitive,
         is_bind_aware
       from
         v$sql
       where
         sql_id = '3ykb992prh4pc'
       /

  SQL_ID        CHILD_NUMBER I I
  ------------- ------------ - -
  3ykb992prh4pc            0 Y N

‘Y’,’N’ のカーソルが一つのみ(インデックススキャン)で、再解析の対象と
なりませんでした。
範囲を 1~100000(全て 1 件のみヒットする条件になる)に大きくした場合
でも同様の結果になりました。

この結果より、adaptive cursor sharing 機能では、単純な値の範囲でなく、
選択される件数の範囲を観察していると判断できます。

尚、この後にフルスキャンとなる条件(id = 999999)で実行したところ、
先ほどの検証と同様に、フルスキャンに変更されまでに 50 回程の実行を
要しました。

■まとめ

adaptive cursor sharing 機能は、

・値の範囲ではなく、その値によって選択される件数の範囲を観察している。
・選択される件数の範囲が、ある程度の広い範囲で実行されると、実行計画が
 変更されないようなケースでも再解析の対象となる。
・選択される件数の範囲が狭い条件で多数実行された場合、実行計画が変更
 されるまでには、同様に多数実行される必要がある。

今回はここまで

次回は、adaptive cursor sharing 機能が動作しないケースの確認、強制的に
再解析させる方法はあるか?など、もう少し検証を続けてみたいと思います。

西日が暑い・・・恵比寿にて