Oracle 11g検証 新機能：SPMって何？ その５
＜Oracle 11g検証 第3弾 新機能：SPMって何？ その５＞
ペンネーム： クリープ
3月に入り、だんだんと春らしい陽気になってきました。寒さから解放され、
固まった身体も徐々に柔らかくなってきました。花粉症の人にとっては一番
大変な時期ですが、そうでない人にとってはそろそろ花見が待ち遠しくなっ
いることでしょう。
そんなポカポカ陽気な恵比寿で、今日も検証していこうと思います。今週は先
週、先々週と実施していた検証の続きです。まずは、先週のおさらいから。
■■■■■概要■■■■■
１）先週までのおさらい
２）統計情報取得後のパフォーマンス劣化を再現
３）承認
４）承認される基準とは！？
■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
１）先週までのおさらい
先週は、一意の値が格納された項目をSELECT文しても、インデックススキャン
がSQLベースラインに登録されなかった原因について調査しました。
調査した結果、実行されたSELECT文がライブラリキャッシュの実行計画を使用
していた為、SQL計画ベースラインに登録されなったことが原因でした。この
為、ライブラリキャッシュのフラッシュを実行して、未承認の実行計画をSQL
計画ベースラインに格納されたのを確認して先週は終わりました。
２）統計情報取得後のパフォーマンス劣化を再現
では、この未承認のインデックススキャンに対してDBMS_SPM.EVOLVE_SQL_PLAN
_BASELINEで承認作業を実施した場合、どのような結果になるでしょうか？
今回実行しているSELECT文は、「select * from test_spm where flg = 0」と
いうSELECT文です。条件に指定されているflgの値は一意であり、flgに対して
インデックスも作成されている為、通常であればインデックススキャンの方が
パフォーマンスがよいと判断され、SQL計画ベースラインとして承認されてし
まいます。これでは、統計情報取得後のパフォーマンス劣化を再現したとはい
えません。
そこで、全てのflgの値を0に更新してこのような環境を作成することにしまし
ょう。こうすることで
フルスキャン ：10000件アクセス インデックススキャン：10000件アクセス + インデックス参照によるオーバー ヘッド
となり、フルスキャンよりインデックススキャンの方がパフォーマンスが劣化
する環境を作り出すことができます。
ということで、早速検証開始！
SQL> BEGIN 2 FOR i IN 1..9999 LOOP 3 update test_spm set flg = 0 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> select count(*) from test_spm where flg = 0; COUNT(*) ---------- 10000
flgの全ての値が0に更新されました。これにより、インデックススキャンが実
行された時のパフォーマンスが劣化する環境が作成されました。
３）承認
１．前回の検証で使用したtest_spmテーブルのflg列にインデックスを付与 ２．「flg = 0」のSELECT文実行（2回実行） ３．インデックススキャンが実行されるようにデータを変更 ⇒４．インデックススキャンの実行計画に対して承認作業を実施
それでは、環境が整ったところでDBMS_SPM.EVOLVE_SQL_PLAN_BASELINEを実行
して承認作業を実施してみることにしましょう。
SQL> set serveroutput on SQL> DECLARE 2 report clob; 3 BEGIN 4 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( 5 sql_handle => 'SYS_SQL_9bc07d95122ef8d5'); 6 DBMS_OUTPUT.PUT_LINE(report); 7 END; 8 / ---------------------------------------------------------------------- Evolve SQL Plan Baseline Report ---------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_9bc07d95122ef8d5 PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_122ef8d586e04f64 ----------------------------------- Plan was verified: Time used .04 seconds. Failed performance criterion: Compound improvement ratio select * from table( 2 dbms_xplan.display_sql_plan_baseline( 3 sql_handle=>'SYS_SQL_9bc07d95122ef8d5', 4 format=>'basic')); ---------------------------------------------------------------------- SQL handle: SYS_SQL_9bc07d95122ef8d5 SQL text: select * from test_spm where flg = 0 ---------------------------------------------------------------------- ---------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_122ef8d586e04f64 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE ---------------------------------------------------------------------- Plan hash value: 3667803417 -------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM | | 2 | INDEX RANGE SCAN | IDX_TEST_SPM_FLG | -------------------------------------------------------- ---------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_122ef8d5eb1890ae Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE ---------------------------------------------------------------------- Plan hash value: 1145642998 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | -------------------------------------- 34 rows selected.
インデックススキャン（Plan name:SYS_SQL_PLAN_122ef8d586e04f64）のAccep
tedがNOになっていることから、インデックススキャンの実行計画が承認され
ていないことがわかります。
このことから、統計情報取得後に実行計画が変更されたことによりパフォーマ
ンスが劣化する場合、SPMを使用しているとその実行計画は使用されず、また
承認もされない、ということがいえます。
４）承認される基準とは！？
ところで、今回の検証ではインデックススキャンが承認されませんでしたが、
この承認される、されないの判断となっている改善率はどれくらいだと承認さ
れるのでしょうか？確認してみましょう。
以下は、flgのデータを加工してインデックススキャンが承認された時とされ
なかった時のレポート結果になります。
■承認された時のレポート
----------------------------------------------------------------------
Evolve SQL Plan Baseline Report
----------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_9bc07d95122ef8d5
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_122ef8d586e04f64
-----------------------------------
Plan was verified: Time used .05 seconds.
Passed performance criterion: Compound improvement ratio >= 1.59.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 107 107
Elapsed Time(ms): 1 0
CPU Time(ms): 1 0
Buffer Gets: 46 29 1.59
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
----------------------------------------------------------------------
Report Summary
----------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
■承認されなかった時のレポート
----------------------------------------------------------------------
Evolve SQL Plan Baseline Report
----------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_9bc07d95122ef8d5
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_122ef8d586e04f64
-----------------------------------
Plan was verified: Time used .04 seconds.
Failed performance criterion: Compound improvement ratio = 1.5.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1236 1236
Elapsed Time(ms): 3 1 3
CPU Time(ms): 2 2 1
Buffer Gets: 455 303 1.5
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
■未承認時（一部省略）
Failed performance criterion: Compound improvement ratio <= 1.49.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1237 1237
Elapsed Time(ms): 2 1 2
CPU Time(ms): 3 1 3
Buffer Gets: 455 305 1.49
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
上記の通り改善率1.5が承認基準であることが確認できました。
つまり、新しい実行計画は、登録されているSQL計画ベースラインの実行計画
に対して改善率1.5以上（約66%以下）に改善されていないと承認されない、
ということになります。なかなか承認基準は厳しいようです。
ということで、今週はここまで。
来週は、最終回としてSPM使用時のパフォーマンスを検証してみます。
恵比寿近郊の有名花見スポットって。。。青山霊園！？
恵比寿にて