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

＜Oracle 11g検証隠れた新機能検証　その７＞
ペンネーム：ギャバン

前回の検証では、adaptive cursor sharing 機能により選択される件数の範囲
が観察され、様々な範囲が選択されるような場合に再解析の対象になるという
動作を確認しました。
更に、選択される件数の範囲が狭い条件で多数実行された場合には、その後に
実行計画が変更すべき条件がバインド変数に設定されても、実際に実行計画が
変更されるまでには、同様に多数実行する必要がありました。
このような場合に、強制的に再解析させる方法はあるのでしょうか？
また、機能が動作しない条件はどのようなケースなのでしょうか？

今回は、「強制的に再解析させる方法」と「機能が動作しない条件」について
検証してみたいと思います。

■検証準備

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

SQL> desc ACS_TEST_TAB

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

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

※それぞれの検証前には、統計情報の再収集と共有プールのフラッシュを
行い情報をリセットしています。

■検証スタート！！

強制的に再解析させる方法はすぐに思いつかなかったので、まずは機能が動作
しない（実行計画が変更しない）条件の検証から・・・

以下、3つの条件について確認してみたいと思います。

1.統計情報が収集されていない
2.ヒント句が指定されている
3.パラメータで機能が無効となる設定がされている

前回、バインド変数に設定するランダムな値の範囲を 1～600 として繰り返し
実行したところ、確実に再解析対象となる結果を確認しました。
今回は、その同じ条件を使って動作の違いを確認してみたいと思います。

1.統計情報が収集されていない

統計情報を削除します。

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

前項と同様、バインド変数に 1～600 のランダムな値を設定して実行します。

SQL> 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  select
       sql_id,
       child_number,
       is_bind_sensitive,
       is_bind_aware
     from
       v$sql
     where
       sql_id = 'a6td0yyndwr3g'
     /

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
902qv35r4tzqx            0 N N ★

is_bind_sensitive、is_bind_aware の両方 ‘N’ となる子カーソルが 1 つ
のみという結果になりました。
この、両方 ‘N’ となるケースは、前回の検証では存在していませんでした。
is_bind_sensitive = ‘N’ の場合、バインド変数の観察を行いません。
つまり、統計情報が収集されていないと、adaptive cursor sharing 機能は
動作しないということになります。

2.ヒント句が指定されている

full ヒントを指定して実行します。

SQL> var v_id number
SQL> exec :v_id := round(dbms_random.value(1,600),0);
SQL> select /*+ full(acs_test_tab) */ max(random_data)
     from acs_test_tab where id  alter session set "_optimizer_adaptive_cursor_sharing"=false;

同じく、バインド変数に 1～600 のランダムな値を設定して実行します。

SQL> 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  var v_id number
SQL> exec :v_id := 1;
SQL> select max(random_data) from acs_test_tab where id  alter session set "_optimizer_adaptive_cursor_sharing"=true;

バインド変数に 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  alter session set "_optimizer_adaptive_cursor_sharing"=false;

バインド変数にフルスキャンが選択されるべき値を設定して実行します。

SQL> var v_id number
SQL> exec :v_id := 500000;
SQL> select max(random_data) from acs_test_tab where id  alter session set optimizer_features_enable='10.2.0.4';

v$sql の結果は、両方共 ‘N’ になります。

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
902qv35r4tzqx            0 N N

※このパラメータにより無効／有効となる機能は、リファレンスマニュアル
より、ご確認頂けます。

====================================================================
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/index.htm
Oracle Databaseリファレンス
11g リリース1（11.1）

1 初期化パラメータ
表1-5 Oracle Database 11g リリースのオプティマイザ機能の項を参照
====================================================================

今回はここまで。

■今回のまとめ

・機能が動作しない（実行計画が変更しない）条件

  - 統計情報が収集されていない
  - ヒント句が指定されている
      → 但し、adaptive cursor sharing 機能自体は動作する
  - optimizer_features_enable パラメータが 10.2 以前に設定されている
      → その他の 11g でのオプティマイザ機能も無効になる

・強制的に再解析させる方法

  - 隠しパラメータ "_optimizer_adaptive_cursor_sharing" を false に
    設定する

■次回予告

ここまでの検証の中で、子カーソルが複数追加されるケースがありました。
これによって、共有プールを圧迫することはないのでしょうか？
また、再解析による負荷はどの程度なのでしょうか？
パフォーマンス面でのデメリットがないか、とても気になります。

ということで、次回は、 adaptive_cursor_sharing 機能検証の最終章（？）
パフォーマンス影響の観点で検証をしてみたいと思います。

新しいオフィスは快適です♪西日の悩みも解消！！・・・恵比寿にて

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

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