Oracle 11g検証 新機能：SPMって何？ その３
＜Oracle 11g検証 第3弾 新機能：SPMって何？ その３＞
ペンネーム： クリープ
2月26日に、弊社のイベント、Insight World 2008が開催されました。
今年も去年に引き続き大勢の方にお来し頂き大盛況の1日となりました。お来
し頂いた皆様、ありがとうございました。
当日使用した資料は近日弊社ホームページにUPされますので、お来し頂けな
かった方も是非ご参照頂ければと思います。
さて、今週も11gの新機能であるSPMについて検証していきます。
今週は、統計情報を取得して実行計画がかわる時のSPMの挙動を検証してみよ
うと思います。
■■■■■概要■■■■■
１）統計情報取得時のパフォーマンス劣化を防ごう！
２）SPM検証！
■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
１）統計情報取得時のパフォーマンス劣化を防ごう！
先週までで、SPMの基本的な流れは理解できたと思います。
今週は、統計情報取得時のパフォーマンス劣化を防ごう！ということで、統計
情報を取得後にパフォーマンスが劣化するような状況を再現し、その時の実行
計画が使用されないかどうか確認してみようと思います。
テスト手順は以下の通り。
１．前回の検証で使用したtest_spmテーブルのflg列にインデックスを付与
２．「flg = 0」のSELECT文実行（2回実行）
=> フルスキャンで実行される
３．インデックススキャンが実行されるようにデータを変更
パターンA.3000件のflgの値を1に更新
パターンB.5000件のflgの値を1に更新
パターンC.7000件のflgの値を1に更新
パターンD.9999件のflgの値を1に更新
※a,bでインデックススキャンが使用された場合、フルスキャンの時
よりパフォーマンスが劣化する可能性があり
※dでは全ての値が一意な為、インデックススキャンになる、はず
４．インデックススキャンの実行計画に対して承認作業を実施
=> パフォーマンスが劣化すると判断して、インデックススキャンの実
行計画が承認されない
それでは早速検証してみましょう。
２）SPM検証！
■１．前回の検証で使用したテーブルのflg列にインデックスを付与
まずは、test_spmテーブルのflgにインデックスを作成します。
SQL> create index idx_test_spm_flg on test_spm(flg); Index created. SQL> BEGIN 2 DBMS_STATS.GATHER_INDEX_STATS( 3 ownname => 'TEST' 4 ,indname => 'IDX_TEST_SPM_FLG' 5 ); 6 END; 7 / PL/SQL procedure successfully completed.
問題なく作成されました。統計情報も忘れずに取得しておきましょう。
■２．「flg = 0」のSELECT文実行
「flg = 0」の結果は現時点では全データヒットする為、オプティマイザはフ
ルスキャンを選択するはずです。
初回実行のSELECT文ですので、2回実行することを忘れずに。
SQL> set autotrace trace SQL> select * from test_spm where flg = 0; SQL> select * from test_spm where flg = 0; 10000 rows selected. SQL> set autotrace off SQL> select * from table( 2 dbms_xplan.display_sql_plan_baseline( 3 sql_handle=>'SYS_SQL_9bc07d95122ef8d5', 4 format=>'basic')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- --------------------------------------------------------------------- SQL handle: SYS_SQL_9bc07d95122ef8d5 SQL text: select * from test_spm where flg = 0 --------------------------------------------------------------------- --------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_122ef8d5eb1890ae Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE --------------------------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------------------------------------------------- Plan hash value: 1145642998 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | -------------------------------------- 19 rows selected.
想定通りフルスキャンで実行されました。
■３．インデックススキャンが実行されるようにデータを変更
a.3000件のflgの値を更新
b.5000件のflgの値を更新
c.8000件のflgの値を更新
d.9999件のflgの値を更新
▼パターンA.3000件のflgの値を更新
まずは、3000件を更新してみることにしましょう。
3000件の値が0以外に更新された場合、「flg = 0」の結果は7000件ということ
になります。この時にインデックススキャンが使用されれば、インデックスへ
のアクセス負荷によりパフォーマンスは悪くなることが予想されます。
SQL> BEGIN 2 FOR i IN 1..3000 LOOP 3 update test_spm set flg = i where seq_no = i; 4 END LOOP; 5 commit; 6 END; 7 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => 'TEST' 4 ,tabname => 'TEST_SPM' 5 ,cascade => TRUE 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> set autotrace trace SQL> select * from test_spm where flg = 0; SQL> set autotrace off SQL> select * from table( SQL> dbms_xplan.display_sql_plan_baseline( SQL> sql_handle=>'SYS_SQL_9bc07d95122ef8d5', SQL> format=>'basic')); ※一部省略 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | --------------------------------------
やはりフルスキャンで実行されていました。それでは続けて、b,cのパターン
を確認してみることにしましょう。
▼パターンB.5000件のflgの値を更新
SQL> BEGIN 2 FOR i IN 1..5000 LOOP 3 update test_spm set flg = i where seq_no = i; 4 END LOOP; 5 commit; 6 END; 7 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => 'TEST' 4 ,tabname => 'TEST_SPM' 5 ,cascade => TRUE 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> set autotrace trace SQL> select * from test_spm where flg = 0; SQL> set autotrace off SQL> select * from table( SQL> dbms_xplan.display_sql_plan_baseline( SQL> sql_handle=>'SYS_SQL_9bc07d95122ef8d5', SQL> format=>'basic')); ※一部省略 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | --------------------------------------
▼パターンC.8000件のflgの値を更新
SQL> BEGIN 2 FOR i IN 1..8000 LOOP 3 update test_spm set flg = i where seq_no = i; 4 END LOOP; 5 commit; 6 END; 7 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => 'TEST' 4 ,tabname => 'TEST_SPM' 5 ,cascade => TRUE 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> set autotrace trace SQL> select * from test_spm where flg = 0; SQL> set autotrace off SQL> select * from table( SQL> dbms_xplan.display_sql_plan_baseline( SQL> sql_handle=>'SYS_SQL_9bc07d95122ef8d5', SQL> format=>'basic')); ※一部省略 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | --------------------------------------
2000件のヒット件数でもフルスキャンが実行されてしまいました。。。
残るは一意な値にして、インデックススキャンになることを確認、しかないで
すが、さすがにこれはインデックススキャンが実行されるのであまり意味ない
ですね。。。
▼パターンD.9999件のflgの値を更新
SQL> BEGIN 2 FOR i IN 1..9999 LOOP 3 update test_spm set flg = i where seq_no = i; 4 END LOOP; 5 commit; 6 END; 7 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => 'TEST' 4 ,tabname => 'TEST_SPM' 5 ,cascade => TRUE 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> set autotrace trace SQL> select * from test_spm where flg = 0; SQL> set autotrace off SQL> select * from table( SQL> dbms_xplan.display_sql_plan_baseline( SQL> sql_handle=>'SYS_SQL_9bc07d95122ef8d5', SQL> format=>'basic')); ※一部抜粋 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | -------------------------------------- 19 rows selected.
あれ？
dbms_xplan.display_sql_plan_baselineの結果にインデックススキャンの実行
計画が存在していませんでした。つまり、今回のSQL文はフルスキャンで実行
された、ということになります。
本来であれば、以下のようなインデックススキャンの実行計画があるはずなの
に。。。
-------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM | | 2 | INDEX RANGE SCAN | IDX_TEST_SPM_FLG | --------------------------------------------------------
統計情報も取ったし、値も一意、前回検証した時と値もSQLもほぼ同じようなS
QL文なのに、なんで今回はフルスキャンで実行されたんだろう？
と疑問に思いつつ来週に続く。。。
KWEENのライブを見てて思ったこと：フレディが予想外にセクシー
恵比寿にて