＜Oracle 11g検証 第3弾 新機能：SPMって何？ その２＞
ペンネーム： クリープ
先週のバレンタインデーもひっそりと過ぎ、2月のイベントはこれで終わり、
次のイベントは３月のひな祭り。。。
って、ちょっと待った！
ひな祭りの前に大きなイベントがあります！
来る2/26にグランドプリンスホテル赤坂でInsight Worldという弊社イベント
が開催されます。当日はOracleの技術情報や弊社製品のご紹介だけでなく、
F1で大活躍した片山右京氏の講演や、QWEENのトリビュートバンドKWEENによ
るライブなど、見所満載のイベントとなっています。
メルマガでしかインサイトを知らない！という方でももちろん参加可能です
ので、興味あるセッションがあれば、是非足を運んで下さい。
↓詳細は、こちらをチェック！
http://www.insight-tec.com/news/seminar/iw080226.html
宣伝はこれくらいにして、今週もSPMについて検証していきます。
今週は、先週確認できなかった実行計画の登録を実施してみましょう。
■■■■■概要■■■■■
１）SPMを使ってみよう！
１．実行計画を取得
⇒２．取得した実行計画とは異なるものを保留
⇒３．保留された実行計画を検証し、承認/拒否を判断
※.「⇒」は今週作業予定項目
■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
１）SPMを使ってみよう！
１．実行計画を取得
⇒２．取得した実行計画とは異なるものを保留
３．保留された実行計画を検証し、承認/拒否を判断
前回は、以下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になっており、このインデックススキ
ャンの実行計画がまだ未承認であることがわかります。
それでは、この保留状態の実行計画を承認してみましょう。
１）SPMを使ってみよう！
１．実行計画を取得
２．取得した実行計画とは異なるものを保留
⇒３．保留された実行計画を検証し、承認/拒否を判断
それでは早速、保留状態の実行計画を承認してみましょう。
承認は、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日であって欲しいと願いつつ。。。
恵比寿にて
