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

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

皆様、はじめまして!
今回がメルマガデビューとなります、ギャバンと申します。
どうぞよろしくお願い致します。

今回は、Oracle11g の隠れた新機能シリーズの第5回!
adaptive cursor sharing 機能について検証します。
この機能を一言で表現すると、

“bind peek 機能の問題を解決する新機能!” と言えます。

筆者は、bind peek には何度か泣かされた経験があります。
その為、この機能には大いに期待しています!

まず、検証を始める前に、bind peek の概要・問題点から解説させて頂きたい
と思います。

■bind peek 機能とは?

バインド変数にセットされた値を評価し、その値によって実行計画を決定する
という、Oracle9i で実装された機能です(デフォルト設定で動作します)。
“peek” という単語を和訳すると、”覗く” という意味があります。文字通り、
「バインド変数の中を覗く」という機能です。

具体例を挙げて解説させて頂きます。

id 列に、1 ~ 1,000,000 までの通番が入っているとします。
この時、

where id <= 10000

という条件が指定された場合、検索対象は全体の 1% となる為、インデックス
スキャンが選択されるべきです。
次に、

where id var v_id number

  SQL> exec :v_id := 500000
  SQL> select * from test_tab where id  var v_id number
  SQL> exec :v_id := 10000
  SQL> select * from test_tab where id  create table ACS_TEST_TAB (id number,random_data varchar2(20));

  -- データを 100 万件挿入
  SQL> declare
         v_data  varchar2(20);
       begin
         for cnt in 1..1000000
         loop
           v_data := dbms_random.string('x',20);
           insert into ACS_TEST_TAB (id,random_data)
           values (cnt,v_data);
           if (mod(cnt,10000) = 0) then
             commit;
           end if;
         end loop;
         commit;
       end;
       /

  -- id 列 にインデックスを作成
  SQL> create index ACS_TEST_IDX on ACS_TEST_TAB(id);

  -- 統計情報を収集
  SQL> exec dbms_stats.gather_table_stats( -
         ownname => 'scott', -
         tabname => 'acs_test_tab', -
         cascade => true);

■検証スタート!!

まずは、フルスキャンが選択されるような条件で実行してみます。
尚、EXPLAIN PLAN、autotrace 機能では、バインド変数にセットされた値を
考慮せずに実行計画を取得する為、SQL トレースで確認を行いました。
ちょっと面倒でしたが・・・

  SQL> var v_id number
  SQL> alter session set events
       '10046 trace name context forever,level 12';
  SQL> exec :v_id := 500000
  SQL> select max(random_data) from acs_test_tab where id  alter session set events '10046 trace name context off';

SQL トレースで実行計画を確認(TKPROF で整形)。

  Rows     Row Source Operation
  -------  ---------------------------------------------------
        1  SORT AGGREGATE (cr=4279 pr=4277 pw=4277 time=0 us)
   500000   TABLE ACCESS FULL ACS_TEST_TAB (cr=4279 pr=4277

予想通り、フルスキャンとなりました。

次に、インデックススキャンが選択されるべき条件を指定してみます。
(トレース取得コマンドの記載は省略します。)

  SQL> exec :v_id := 10000
  SQL> select max(random_data) from acs_test_tab where id  exec :v_id := 10000
  SQL> select max(random_data) from acs_test_tab where id <= :v_id;

SQL トレースを確認。

  Rows     Row Source Operation
  -------  ---------------------------------------------------
        1  SORT AGGREGATE (cr=65 pr=0 pw=0 time=0 us)
    10000   TABLE ACCESS BY INDEX ROWID ACS_TEST_TAB (cr=65 pr=0
    10000    INDEX RANGE SCAN ACS_TEST_IDX (cr=23 pr=0 pw=0 time

インデックススキャンになりました!!

これは、adaptive cursor sharing 機能によって、以下のような動作をした
ものと予想されます。

  1回目 : バインド変数の値を評価しフルスキャンを選択。
  2回目 : Soft Parse により、前回と同じフルスキャンを選択。
          しかし、異変を察知!!
          今のはインデックススキャンの方が良かったのでは???と。
  3回目 : 前回、異変を察知したので再解析してみる。
          その結果、インデックススキャンを選択。

これで、bind peek の問題も見事解決!?

今回はここまで。
次回は、この機能についてもう少し深く検証していきたいと思います。

少し職場の雰囲気にも慣れてきたかなぁ・・・恵比寿にて