Oracle 11g検証 新機能:SPMって何? その5

<Oracle 11g検証 第3弾 新機能:SPMって何? その5>
ペンネーム: クリープ

3月に入り、だんだんと春らしい陽気になってきました。寒さから解放され、
固まった身体も徐々に柔らかくなってきました。花粉症の人にとっては一番
大変な時期ですが、そうでない人にとってはそろそろ花見が待ち遠しくなっ
いることでしょう。
そんなポカポカ陽気な恵比寿で、今日も検証していこうと思います。今週は先
週、先々週と実施していた検証の続きです。まずは、先週のおさらいから。

■■■■■概要■■■■■
1)先週までのおさらい
2)統計情報取得後のパフォーマンス劣化を再現
3)承認
4)承認される基準とは!?

■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

1)先週までのおさらい
先週は、一意の値が格納された項目をSELECT文しても、インデックススキャン
がSQLベースラインに登録されなかった原因について調査しました。
調査した結果、実行されたSELECT文がライブラリキャッシュの実行計画を使用
していた為、SQL計画ベースラインに登録されなったことが原因でした。この
為、ライブラリキャッシュのフラッシュを実行して、未承認の実行計画をSQL
計画ベースラインに格納されたのを確認して先週は終わりました。

2)統計情報取得後のパフォーマンス劣化を再現
では、この未承認のインデックススキャンに対して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に更新されました。これにより、インデックススキャンが実
行された時のパフォーマンスが劣化する環境が作成されました。

3)承認

 1.前回の検証で使用したtest_spmテーブルのflg列にインデックスを付与
 2.「flg = 0」のSELECT文実行(2回実行)
 3.インデックススキャンが実行されるようにデータを変更
⇒4.インデックススキャンの実行計画に対して承認作業を実施

それでは、環境が整ったところで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を使用しているとその実行計画は使用されず、また
承認もされない、ということがいえます。

4)承認される基準とは!?
ところで、今回の検証ではインデックススキャンが承認されませんでしたが、
この承認される、されないの判断となっている改善率はどれくらいだと承認さ
れるのでしょうか?確認してみましょう。
以下は、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使用時のパフォーマンスを検証してみます。

恵比寿近郊の有名花見スポットって。。。青山霊園!?
                            恵比寿にて