Oracle 11g検証 隠れた新機能検証 その５
＜Oracle 11g検証 隠れた新機能検証 その５＞
ペンネーム： ギャバン
皆様、はじめまして！
今回がメルマガデビューとなります、ギャバンと申します。
どうぞよろしくお願い致します。
今回は、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 の問題も見事解決！？
今回はここまで。
次回は、この機能についてもう少し深く検証していきたいと思います。
少し職場の雰囲気にも慣れてきたかなぁ・・・恵比寿にて