Oracle 11g検証新機能：SPMって何？　その６

＜Oracle 11g検証第3弾新機能：SPMって何？　その６＞
ペンネーム：クリープ

さて、SPMについていろいろ検証してきましたが、今週でいよいよ最終回です。
SPMがなんとなく使えそう、ということはわかりましたが、やはりこの機能を
使用した時のパフォーマンスが一番気になるところではないでしょうか？
ということで、最後にSPMのパフォーマンスについてみてみることにしましょ
う。

■■■■■概要■■■■■
１）SPMのパフォーマンスについて
２）SPMパフォーマンス検証

■環境
RedHat Enterprise Linux 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

１）SPMのパフォーマンスについて
SPM検証の第1回でOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESをTRUEに設定しまし
た。このパラメータをTRUEに設定していると、実行されたSQL文の実行計画が
SQL計画ベースラインに自動的にサンプリングされます。サンプリングされる
ということは、サンプリング時のオーバーヘッドが発生していることになりま
す。実際、SPM検証の第４回では、再帰SQL文で複数のテーブルにINSERTされて
いたように、SPMを使用する上でこのような負荷は気になるところです。

ということで、最終回として、自動実行計画取得時のパフォーマンスについて
検証してみましょう。

２）SPMパフォーマンス検証
それでは早速検証してみることにします。
テストケースは以下の通り。

■テストケース
１．通常ケース
・OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESはFALSE
・10000件のSELECT文実行

２．SQL計画ベースラインに実行計画が格納されるケース
・OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESはTRUE
・10000件のSELECT文実行
※SELECT実行時、SQL計画ベースラインに10000件の実行計画が格納

３．SQL計画ベースラインに実行計画が格納済みのケース
・OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESはTRUE
・実行計画が格納済のSELECT文を10000件実行

※全てのケースでライブラリキャッシュのフラッシュを実行

今回のテストケースは、２のケースは全てのSQL文の実行計画を格納してしま
うのでパフォーマンス的には最悪のケースであり、３は全てのSELECT文の実行
計画が格納されているのでパフォーマンス的には最高のケースであるというこ
とができます。

以下、テストで使用したスクリプトになります。

■テストシェルスクリプト

[oracle@localhost ~]$ vi perf_test.sh

#!/bin/sh

time sqlplus test/test &lt; /dev/null
@perf_test.sql
exit
_EOF_
exit

■SQL

[oracle@localhost ~]$ vi perf_test.sql

SELECT seq_no FROM test_spm WHERE seq_no = 0;
SELECT seq_no FROM test_spm WHERE seq_no = 1;
SELECT seq_no FROM test_spm WHERE seq_no = 2;
SELECT seq_no FROM test_spm WHERE seq_no = 3;
                  .
                  .
                  .
SELECT seq_no FROM test_spm WHERE seq_no = 9999;

■SQL計画ベースライン削除SQL

DECLARE
　　　　　　i NATURAL;
　　　　　　cursor cur_spm is
　　　　　　　　　　　　select distinct sql_handle from dba_sql_plan_baselines;

BEGIN
　　　　　　FOR r_spm IN cur_spm LOOP
　　　　　　　　　　　　i := dbms_spm.drop_sql_plan_baseline(r_spm.sql_handle);
　　　　　　END LOOP;
END;
/

では、早速テストを実行してみましょう。
まずは、通常ケースで実行！

■１．通常ケース

1回目
real    0m12.496s
user    0m1.496s
sys     0m0.280s

2回目
real    0m12.063s
user    0m1.148s
sys     0m0.196s

3回目
real    0m12.144s
user    0m1.212s
sys     0m0.188s

3回実行した平均は約12秒でした。この時間をベースにその他のケースと比較
することになります。
では次にSQL計画ベースラインに実行計画が格納されるケースを実行！

■２．SQL計画ベースラインに実行計画が格納されるケース
1回目

real    4m33.481s
user    0m0.176s
sys     0m0.084s

2回目
real    4m18.627s
user    0m0.344s
sys     0m0.056s

3回目
real    4m47.122s
user    0m0.328s
sys     0m0.056s

※テストの間でSQL計画ベースライン削除SQLを実行

平均で4分43秒、通常ケースと比べて約23倍の処理時間がかかってしまいまし
た。やはり、SYSAUX表領域への書き込みによるオーバーヘッドが影響してい
るようです。
今回の検証環境はかなり非力なことが大きく影響しているとは思いますが、
そのことを考慮してもやはりパフォーマンスへの影響は少なくないといえます。

最後に、既にSQL計画ベースラインに実行計画が格納されているケースを確認
してみましょう！

■３．SQL計画ベースラインに実行計画が格納済みのケース

1回目
real    0m18.386s
user    0m1.780s
sys     0m0.276s

2回目
real    0m17.585s
user    0m1.572s
sys     0m0.324s

3回目
real    0m17.952s
user    0m1.564s
sys     0m0.216s

平均で約18秒前後と、通常ケースより若干遅くなっていました。
今回のテストケースでは、実行計画を参照する為にSYSAUX表領域にアクセスし
ていますが、この時のオーバーヘッドが影響して通常ケースよりも若干遅く
なっているようです。

今回、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESをTRUEに設定した時にパフォー
マンスとして最悪のケース（２のケース）と最高のケース（３のケース）を検
証しました。
２のような常にSQL計画ベースライン上に実行計画が格納されるようなケース
はOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESのパラメータをTRUEに設定した直後
が考えられます。OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESをFALSEからTRUEに
変更する際には注意が必要と言えます。

以上、6回に渡ってSPMを検証してきましたが、今週でSPMの検証は終了です。

SPM検証は今回で終了ですが、11gの検証は来週以降も引き続きます。
それでは、また次の検証で会いましょう。

春眠暁を覚えず、な今日この頃
恵比寿にて

Oracle 11g検証 新機能：SPMって何？ その６

Oracle 11g検証新機能：SPMって何？　その６