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

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

先週に引き続き、今週も11gを検証していきます。
今週からは、11gの新機能にフォーカスして検証していきます。

今回、筆者が注目したのは、SPMという機能。
SPM?何それ?11gの新機能の中にそんな機能あったっけ?
というぐらい地味な機能ですが、これが意外と。。。!?
っと、詳細については検証で明らかにしていきますのでお楽しみに。

■■■■■概要■■■■■
1)SPMとは?
2)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  /

1)SPMとは?
さて、今回とりあげたSPMという言葉を初めて聞いた方も多いと思います。
SPMとは、SQL Plan Managementの略で、SQLの実行計画を記録してそれを評価
した後に本番環境に適用することができる機能のことです。
簡単に言うと、実行計画がかわった時に、パフォーマンスがよくなるかを確
認して、よくなるものだけを適用することができるという機能です。

統計情報取得後、突然パフォーマンスが劣化した、などという話はよく聞きま
す。これは、統計情報の取得により、実行計画が改悪されてしまったことに
起因しています。1度でもこのような経験をしてしまうと、次に統計情報を取
得することを敬遠したり、取得自体をやめたりしてしまいます。
つまり、統計情報を取得する作業は、環境によっては非常にリスクの高い作業
といえます。

今回、11gの新機能特集でSPMを取り上げたのは、この統計情報の取得によるパ
フォーマンス劣化を防ぐことができると考え、それを検証してみようと考えた
為です。

前置きはこれぐらいにして、早速SPMを使ってみましょう。

2)SPMを使ってみよう!
っと、実際にSPMを使ってみる前に、SPMを使用する場合の処理の流れを抑えて
おきましょう。

1.実行計画を取得
2.取得した実行計画とは異なるものを保留
3.保留された実行計画を検証し、承認/拒否を判断

今回は、1つのSELECT文がフルスキャンとインデックススキャンになるように
実行して、その時の挙動を確認します。
では、上記流れを一つずつ見てきます。

1.実行計画を取得
まずは、実行計画を取得して、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
-----
   - SQL plan baseline "SYS_SQL_PLAN_a52a4eafeb1890ae" used for this statement

Noteの記載に「このSQL文はSYS_SQL_PLAN_a52a4eafeb1890aeというプランを
使用した」とあるように、実行したSQL文の実行計画がSQL Management Base
に格納され、それを使用したことがわかります。
この、SYS_SQL_PLAN…は、プラン名のことで、実行計画に対してユニークに
作成されます。
このプラン名を条件に、dba_sql_plan_baselinesで確認してみましょう。

SQL> set autotrace off
SQL> SELECT sql_text,sql_handle
  2    FROM dba_sql_plan_baselines
  3   WHERE plan_name = 'SYS_SQL_PLAN_a52a4eafeb1890ae';

SQL_TEXT                                 SQL_HANDLE
---------------------------------------- -------------------------
select * from test_spm where seq_no = 1  SYS_SQL_5c11383aa52a4eaf

今度は、ちゃんと格納されていました。
抽出したSQL_HANDLEとは、SQL文に対するユニークな番号のことをさします。
つまり、1つのSQL_HANDLEに対して複数のPLAN_NAMEが作成されることがあり
ます。

ちなみに、dba_sql_plan_baselinesをと同じような情報はdbms_xplan.display_
sql_plan_baselineでも参照することができます。
このパッケージを実行すると、実行計画付きで表示されます。

SQL> select * from table(
  2      dbms_xplan.display_sql_plan_baseline(
  3          plan_name=>'SYS_SQL_PLAN_a52a4eafeb1890ae',
  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_a52a4eafeb1890ae
Enabled: YES     Fixed: NO      Accepted: YES    Origin: AUTO-CAPTURE
---------------------------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1145642998

--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|   1 |  TABLE ACCESS FULL| TEST_SPM |
--------------------------------------

19 rows selected.

それでは、次にこのSELECT文をフルスキャンではなくインデックススキャンに
なるように変更してみることにします。

。。。っと、今週はココまで。
来週は、今回実行したフルスキャンのSELECT文をインデックススキャンで実行
するところから再開します。
それではまた来週!

SPMとは… 社内でパッション蔓延中!?(パッション、パッション)
                            恵比寿にて