Oracle 11g検証 新機能：SPMって何？ その４
＜Oracle 11g検証 第3弾 新機能：SPMって何？ その４＞
ペンネーム： クリープ
先週は、統計情報を取得後にパフォーマンスが劣化するような状況を再現中
に、予想外の結果になったところで終わりました。
通常であれば、一意な値が格納された項目をSELECTすればインデックススキ
ャンになるはずなのに何故フルスキャンになってしまったのでしょうか？
今週は、この疑問を解明していこうと思います。
■■■■■概要■■■■■
１）一意な値の参照でフルスキャンが選択された理由とは！？
■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
１）一意な値の参照でフルスキャンが選択された理由とは！？
早速、フルスキャンが実行された原因を調査していくことにします。
とりあえず、実行したSELECT文のSQLトレースを見て、どのような処理が実行
されているか見てみることにしましょう。
まずは、インデックス作成後に実行したSELECT文のSQLトレースを見てみます。
▼インデックス作成後のSELECTで取得したSQLトレース（TKPROFで整形後）
**********************************************************************
SQL ID : 4q15a86440bpk
select *
from
test_spm where flg = 0
call count cpu elapsed disk query current rows
------- ------ ------- --------- ------- -------- --------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 8 0
Fetch 668 0.12 0.10 0 690 0 10000
------- ------ ------- --------- ------- -------- --------- --------
total 670 0.14 0.12 0 690 8 10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
10000 TABLE ACCESS FULL TEST_SPM (cr=690 pr=0 pw=0 time=1967
us cost=7 size=50000 card=10000)
**********************************************************************
※上記SQL文のリカーシブコールとして実行されていたSQL文抜粋
・SELECT /*+ INDEX(sqlobj$ (name obj_type)) */ signature, category FROM sqlobj$ WHERE name = :1 AND obj_type = :2 ・SELECT obj_type, plan_id, name, flags, last_executed FROM sqlobj$ WHERE signature = :1 AND category = :2 ・INSERT INTO sqlobj$ (signature, category, obj_type, plan_id... ・INSERT INTO sqlobj$auxdata (signature, category, obj_type... ・INSERT INTO sqlobj$data (signature, category, obj_type... ・INSERT INTO sql$ (signature, inuse_features, flags, spare1... ・INSERT INTO sql$text (signature, sql_handle, sql_text)...
リカーシブコールを見ると、sqlobj$というテーブルをSELECTした後に、INSER
T文が実行されています。
これはつまり、sqlobj$へのSELECT文がSQL計画ベースラインの参照であり、
sqlobj$へのINSERT文がSQL計画ベースラインへの実行計画の登録のようです。
sqlobj$テーブルはSQL計画ベースラインの実体といえるでしょう。
それ以外は特に気になるところはないですね。。。
では、9999件UPDATEした後にSELECTした時のSQLトレースを確認して、上記
SQLトレースと比較してみましょう。
▼9999件UPDATE後のSELECTで取得したSQLトレース（TKPROFで整形後）
**********************************************************************
SQL ID : 4q15a86440bpk
select *
from
test_spm where flg = 0
call count cpu elapsed disk query current rows
------- ------ ------- --------- ------- -------- --------- --------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 47 0 1
------- ------ ------- --------- ------- -------- --------- --------
total 4 0.00 0.00 0 47 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL TEST_SPM (cr=47 pr=0 pw=0 time=0 us cost=7
size=50000 card=10000)
**********************************************************************
※上記SQL文のリカーシブコールとして実行されていたSQL文
なし
先のSQLトレースの情報と比較すると、今回はsqlobj$へのリカーシブコール
が実行されていないというところでしょうか。
つまり、SQL計画ベースラインを参照していないということになります。。。
参照していない。。。
あれ？
よく見たら、「Misses in library cache during parse」の値が前回は1だっ
たのに今回は0になってます。
「Misses in library cache during parse」の値は、1はHARD PARSE、0はSOFT
PARSEで実行されたことを表しています。
HARD PARSEとは解析処理が実行されたことを表しており、SOFT PARSEはライ
ブラリキャッシュにある解析済み情報を使用したことを意味します。
つまり、今回のケースはSQL計画ベースラインの情報（sqlobj$）は参照せず、
ライブラリキャッシュ上にキャッシュされた実行計画が使用されていたよう
です。
ということは、ライブラリキャッシュをフラッシュすれば、インデックスス
キャンの実行計画が登録されるはず！？
SQL> alter system flush shared_pool; System altered. SQL> select * from test_spm where flg = 0; SQL> select * from table( 2 dbms_xplan.display_sql_plan_baseline( 3 sql_handle=>'SYS_SQL_9bc07d95122ef8d5', 4 format=>'basic')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- --------------------------------------------------------------------- SQL handle: SYS_SQL_9bc07d95122ef8d5 SQL text: select * from test_spm where flg = 0 --------------------------------------------------------------------- --------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_122ef8d586e04f64 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE --------------------------------------------------------------------- Plan hash value: 3667803417 -------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM | | 2 | INDEX RANGE SCAN | IDX_TEST_SPM_FLG | -------------------------------------------------------- --------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_122ef8d5eb1890ae 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.
期待通り、インデックススキャンの実行計画が取得されました。
ということは、今回はインデックススキャンが選択されたようです。
但し、ここで注意しておくべきは、このインデックススキャンはまだ承認さ
れていない為、実際にはフルスキャンで実行されています。
わかりづらいので、まとめると、
▼今回の流れ
１．「select * from test_spm where flg = 0」を実行
２．SQL計画ベースラインにフルスキャンの実行計画が格納
３．flgの値をユニークな値に変更
４．統計情報を取得
５．再度「select * from test_spm where flg = 0」を実行
=> １でキャッシュしたライブラリキャッシュの情報を参照（フルスキャン）
６．ライブラリキャッシュをフラッシュ
７．再再度「select * from test_spm where flg = 0」を実行
８．オプティマイザはインデックススキャンを選択
=> 承認されていない為、未承認としてSQL計画ベースラインに格納
=> 実際はフルスキャンで実行
となります。
SPMのリポジトリを参照する負荷を考えると、ライブラリキャッシュにキャッ
シュされてる実行計画を優先した方がパフォーマンスがよいと判断している
のでしょう。
と、疑問が解消した所で今週はここまで。
来週は、統計情報取得後にパフォーマンス劣化する状況を改めて再現してみ
ようと思います。
風邪でマスクをしてたら、やっと花粉症になったの？と言われました
恵比寿にて