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

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

先週のバレンタインデーもひっそりと過ぎ、2月のイベントはこれで終わり、
次のイベントは3月のひな祭り。。。

って、ちょっと待った!
ひな祭りの前に大きなイベントがあります!

来る2/26にグランドプリンスホテル赤坂でInsight Worldという弊社イベント
が開催されます。当日はOracleの技術情報や弊社製品のご紹介だけでなく、
F1で大活躍した片山右京氏の講演や、QWEENのトリビュートバンドKWEENによ
るライブなど、見所満載のイベントとなっています。
メルマガでしかインサイトを知らない!という方でももちろん参加可能です
ので、興味あるセッションがあれば、是非足を運んで下さい。

↓詳細は、こちらをチェック!
https://www.insight-tec.com/news/seminar/iw080226.html

宣伝はこれくらいにして、今週もSPMについて検証していきます。
今週は、先週確認できなかった実行計画の登録を実施してみましょう。

■■■■■概要■■■■■
1)SPMを使ってみよう!
1.実行計画を取得
⇒2.取得した実行計画とは異なるものを保留
⇒3.保留された実行計画を検証し、承認/拒否を判断
※.「⇒」は今週作業予定項目

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

1)SPMを使ってみよう!
1.実行計画を取得
⇒2.取得した実行計画とは異なるものを保留
3.保留された実行計画を検証し、承認/拒否を判断

前回は、以下SQL文を実行して、SPMリポジトリ(SQL Management Base)にフ
ルスキャンの実行計画が格納されたところまで確認しました。

SQL> select * from test_spm where seq_no = 1;

今回は、前回の実行計画とは異なる実行計画をSPMリポジトリに保存してみま
しょう。
上記SELECT文は前回フルスキャンだったので、今回はインデックススキャンに
なるようにpk_noにインデックスを作成して実行してみましょう。

SQL> create index idx_test_spm on test_spm(seq_no);

Index created.

SQL> BEGIN
  2    DBMS_STATS.GATHER_INDEX_STATS(
  3       ownname      => 'TEST'
  4      ,indname      => 'IDX_TEST_SPM'
  5    );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> set autotrace trace
SQL> select * from test_spm where seq_no = 1;


Execution Plan
----------------------------------------------------------
Plan hash value: 1145642998

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     5 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SPM |     1 |     5 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SEQ_NO"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_a52a4eafeb1890ae" used for this statement

実行計画をみると、フルスキャンで実行されています。
先ほどインデックスを追加しましたが、インデックススキャンの実行計画は
まだ有効な実行計画として承認されていない為、保留状態になっています。
このことは、dbms_xplan.display_sql_plan_baselineで確認することができ
ます。

SQL> set autotrace off
SQL> select * from table(
  2      dbms_xplan.display_sql_plan_baseline(
  3          sql_handle=>'SYS_SQL_5c11383aa52a4eaf',
  4          format=>'basic'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

---------------------------------------------------------------------
SQL handle: SYS_SQL_5c11383aa52a4eaf
SQL text: select * from test_spm where seq_no = 1
---------------------------------------------------------------------

---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eaf739aca96
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
---------------------------------------------------------------------
Plan hash value: 2439131642

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_SPM     |
|   2 |   INDEX RANGE SCAN          | IDX_TEST_SPM |
----------------------------------------------------

---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eafeb1890ae
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.

インデックススキャンのAcceptedがNOになっており、このインデックススキ
ャンの実行計画がまだ未承認であることがわかります。
それでは、この保留状態の実行計画を承認してみましょう。

1)SPMを使ってみよう!
1.実行計画を取得
2.取得した実行計画とは異なるものを保留
⇒3.保留された実行計画を検証し、承認/拒否を判断

それでは早速、保留状態の実行計画を承認してみましょう。
承認は、DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEを使用して行います。
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEを実行すると、未承認の実行計画と承認
済みの実行計画と比較して、未承認の実行計画のパフォーマンスがよい場合
に、承認された実行計画(SQL計画ベースライン)として登録されます。

それでは、実際に承認してみます。

SQL> set serveroutput on
SQL> DECLARE
  2      report clob;
  3  BEGIN
  4      report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
  5                    sql_handle => 'SYS_SQL_5c11383aa52a4eaf');
  6      DBMS_OUTPUT.PUT_LINE(report);
  7  END;
  8  /


----------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
----------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_5c11383aa52a4eaf
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_a52a4eaf739aca96
-----------------------------------
  Plan was verified: Time used .04 seconds.
  Passed performance criterion: Compound improvement ratio >= 7.67.
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):               0              0
  CPU Time(ms):                   0              0
  Buffer Gets:                   23              3              7.67
  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.



PL/SQL procedure successfully completed.

レポートの中ほどに「Plan was changed to an accepted plan.」とあり、新
しい実行計画が承認されたことがわかります。
さらに、その下で、登録済みの実行計画(Baseline Plan)と新しい実行計画
(Test Plan)を比較して、改善率としてパフォーマンスがどれぐらい改善さ
れたか数値化して表しています。
この改善率が高い場合、その実行計画は使用可能な実行計画として承認され
ます。
今回は、Buffer Getsの値が23から3に減少で、7.67%改善されています。この
数値を見て、Oracleはパフォーマンスが改善されたと判断し、新しい実行計
画を承認しています。
ちなみに、パフォーマンスが改善されるかどうかを無視して承認したい場合は、
パッケージの引数に「VERIFY => ‘no’」を追加することで、結果に関係なく
承認することができます。

それでは、dbms_xplan.display_sql_plan_baselineを使用してちゃんと承認
されているか確認してみましょう。

SQL> select * from table(
  2      dbms_xplan.display_sql_plan_baseline(
  3          sql_handle=>'SYS_SQL_5c11383aa52a4eaf',
  4          format=>'basic'));



PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
---------------------------------------------------------------------
SQL handle: SYS_SQL_5c11383aa52a4eaf
SQL text: select * from test_spm where seq_no = 1
---------------------------------------------------------------------

---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eaf739aca96
Enabled: YES     Fixed: NO      Accepted: YES    Origin: AUTO-CAPTURE
---------------------------------------------------------------------

Plan hash value: 2439131642

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_SPM     |
|   2 |   INDEX RANGE SCAN          | IDX_TEST_SPM |
----------------------------------------------------

---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eafeb1890ae
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.

インデックススキャンのAcceptedがYESに変わっており、承認された実行計画
になったことがわかります。
つまり、今回のSELECT文を実行すると、フルスキャンとインデックススキャン
が使用可能ということになります。
ちなみに、承認された実行計画が複数ある場合、オプティマイザがコストが
低いと判断した方の実行計画で実行されます。

以上、SPMの基本の流れについて簡単に確認しました。
次回は、このSPMを色々な確度から検証していこうと思います。

それではまた来週!

2/26が、Insight Worldに来た人全員にとって「It’s a beautiful day~♪」
な1日であって欲しいと願いつつ。。。
                            恵比寿にて