Oracle 11g検証 新機能：SPMって何？ その１
＜Oracle 11g検証 第3弾 新機能：SPMって何？ その１＞
ペンネーム： クリープ
先週に引き続き、今週も11gを検証していきます。
今週からは、11gの新機能にフォーカスして検証していきます。
今回、筆者が注目したのは、SPMという機能。
SPM？何それ？11gの新機能の中にそんな機能あったっけ？
というぐらい地味な機能ですが、これが意外と。。。！？
っと、詳細については検証で明らかにしていきますのでお楽しみに。
■■■■■概要■■■■■
１）SPMとは？
２）SPMを使ってみよう！
■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
■テストテーブル作成
SQL> create table test_spm(seq_no number(10,0), flg number(10,0)); SQL> BEGIN 2 FOR i IN 1..10000 LOOP 3 insert into test_spm values(i,0); 4 commit; 5 END LOOP; 6 END; 7 / SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => 'TEST' 4 ,tabname => 'TEST_SPM' 5 ); 6 END; 7 /
１）SPMとは？
さて、今回とりあげたSPMという言葉を初めて聞いた方も多いと思います。
SPMとは、SQL Plan Managementの略で、SQLの実行計画を記録してそれを評価
した後に本番環境に適用することができる機能のことです。
簡単に言うと、実行計画がかわった時に、パフォーマンスがよくなるかを確
認して、よくなるものだけを適用することができるという機能です。
統計情報取得後、突然パフォーマンスが劣化した、などという話はよく聞きま
す。これは、統計情報の取得により、実行計画が改悪されてしまったことに
起因しています。１度でもこのような経験をしてしまうと、次に統計情報を取
得することを敬遠したり、取得自体をやめたりしてしまいます。
つまり、統計情報を取得する作業は、環境によっては非常にリスクの高い作業
といえます。
今回、11gの新機能特集でSPMを取り上げたのは、この統計情報の取得によるパ
フォーマンス劣化を防ぐことができると考え、それを検証してみようと考えた
為です。
前置きはこれぐらいにして、早速SPMを使ってみましょう。
２）SPMを使ってみよう！
っと、実際にSPMを使ってみる前に、SPMを使用する場合の処理の流れを抑えて
おきましょう。
１．実行計画を取得
２．取得した実行計画とは異なるものを保留
３．保留された実行計画を検証し、承認/拒否を判断
今回は、1つのSELECT文がフルスキャンとインデックススキャンになるように
実行して、その時の挙動を確認します。
では、上記流れを一つずつ見てきます。
１．実行計画を取得
まずは、実行計画を取得して、SYSAUX表領域のSPMのリポジトリ（SQL Management
Base）に実行計画を格納します。
実行計画の取得方法は色々ありますが、今回は自動取得モードをオンにして、
自動的に実行計画をサンプリングする方法を試してみます。
自動取得モードは、初期化パラメータのOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
で変更することができます。デフォルトでは、FALSEになってます。
SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE; System altered. SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES NAME TYPE VALUE ------------------------------------ --------- -------- optimizer_capture_sql_plan_baselines boolean TRUE
これで、実行計画の自動取得モードがONになりました。
これ以降に実行されたSQL文の実行計画は自動的にSQL Management Baseに格納
されるようになります。
また、SQL Management Baseに格納された実行計画を使用する為には、
optimizer_use_sql_plan_baselinesがTRUEである必要があります。
デフォルトでは、optimizer_use_sql_plan_baselinesはTRUEです。
SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES NAME TYPE VALUE ------------------------------------ --------- -------- optimizer_use_sql_plan_baselines boolean TRUE
それでは、実際にSQL文を実行してみます。
今回は、seq_noを条件にtest_spmテーブルをSELECTするSQL文を使用します。
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)
使用したSELECT文の結果は1/10000件ですが、インデックスがないため、フル
スキャンで実行されています。では、この実行計画がちゃんとSQL Management
Baseに格納されているか確認してみましょう。
dba_sql_plan_baselinesを使用して確認することができます。
SQL> set autotrace off SQL> SELECT sql_text,sql_handle FROM dba_sql_plan_baselines; no rows selected
自動取得モードをオンにしたのにサンプリングされていませんでした。
実は、自動取得モードは、2回以上実行されたSQL文のみSQL Management Base
に格納しています。これは、1回しか実行されないSQL文の実行計画を保存して
もあまり意味がないということと、仮に全てサンプリングしたとしたら、大量
のSQL文を格納しなければならなくなるからでしょう。
ちなみに、共有プールでも、同じような仕様にしてくれたら断片化の可能性も
低くなるのに。。。
と、余談はさておき、先のSQL文をもう一度実行してみましょう。
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