Oracle 11g検証 隠れた新機能検証 その６
＜Oracle 11g検証 隠れた新機能検証 その６＞
ペンネーム： ギャバン
前回に引き続き 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 機能が動作しないケースの確認、強制的に
再解析させる方法はあるか？など、もう少し検証を続けてみたいと思います。
西日が暑い・・・恵比寿にて