2021.11.05
SQL PLAN MANAGEMENTの活用
SQL PLAN MANAGEMENTの活用
(株)エクセンコンサルティング本部/DBコンサルティングチーム長チョンミン
概要
Oracleは多くのリレーショナルDBMSで、ユーザーのSQLクエリを効率的に処理するためにオプティマイザを使用しています。
オプティマイザはユーザーが実行するSQLを受け取って実行計画を作成し、実際のSQLはこの実行計画を介して実行されます。
データベース運用時に普段よく行われていたSQLがパフォーマンス問題を発生させることがありますが、その原因はSQL実行計画の変化にあることが多いです。
SQLの実行計画が変わる理由はさまざまですが、統計情報の変更やインデックスの状態変化、DBのパラメータやバージョン変更などによってSQLの実行計画が変わることがあります。
ところがSQLの実行計画変化を防ぐために、このようなDB作業をしないというわけにはいきません。
実行計画の変化に起因するかもしれない性能低下を防止するために、Oracle 11gからSPM（SQL Plan Management）という機能が提供され、これを利用してSQL外部要素による影響を最小化することができます。
SPMの使用目的と特性
SPMは、SQLの外部要素の変化による影響を最小限に抑えるのにその目的があります。
SPMの主な特性としては、次の2つが挙げられます。
1. Execution Plan の変更による性能低下を事前予防
2. Plan History 管理によるSQLプランの履歴管理可能
SPMはSQL Plan Baseline（PlanとHint）をDBに保存し、検証済みの実行計画のみを使用できるようにしながら、自動的に変更されるSQLの実行計画を管理します。
新しい実行計画が生成される場合、検証が終わるまで使用しないようにして、SQL実行計画変更で発生することがある性能問題を前もって予防することができます。
また、また、新しく生成された実行計画は検証過程で現在の実行計画と比較して性能が向上した場合にだけ使えるようにすることが可能です。
SAPの使用順序
SPMの使用は、次の手順で進めます。
- SQL_PLAN_BASELINES 関連パラメータの変更
- SQL_PLAN_BASELINES に実行計画を登録する
- DBA_SQL_PLAN_BASELINES ビューで確認
l SQL_PLAN_BASELINES 属性変更による利用実行計画の制御
関連 パラメータ クエリ
SQL> show parameter sql_plan_baselines
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
l optimizer_use_sql_plan_baselines : SPBを有効にするパラメータ
- optimizer_capture_sql_plan_baselines : 2回以上実行されるSQLをSPBに自動登録するパラメータ
SQL_PLAN_BASELINESに実行計画を登録＆削除
SPBに実行計画を登録する方法は2つあります。
DBMS_SPMパッケージを使用して手動で直接登録する方法と、optimizer_capture_sql_plan_baselinesパラメータ設定を通じて自動的に実行計画を登録する方法です。
■SPB手動登録
SPB Baseline 登録パッケージの内容 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
■登録するSQL情報の確認
SELECT sql_id ,
plan_hash_value , sql_fulltext
FROM v$sql
WHERE sql_text LIKE ‘%spmtest%’
SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
————- ————— —————————————-
93zny6hkjj6s7 1690735414 SELECT * FROM spmtest WHERE lv = 10 93zny6hkjj6s7 4164974113 SELECT * FROM spmtest WHERE lv = 10
SQL_IDとPLAN_HASH_VALUEを使用してSPBを登録する
DECLARE
pls pls_integer ; BEGIN
pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( ’93zny6hkjj6s7′ , ‘1690735414’) ;
dbms_output.put_line( pls || ‘個別登録’ ) ; END ;
/
SQL_IDとPLAN_HASH_VALUEは実行計画を一意に識別可能です。
PLAN_HASH_VALUE値を入力しない場合は、SQL_IDに対応するすべてのPLANをSPBに登録します。
optimizer_capture_sql_plan_baselinesパラメータをtrueに設定すると、2回以上実行されるすべてのSQLのPLANが自動的にSPBに登録されます。
- SPB Baseline 削除パッケージ
DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;
DECLARE
pls pls_integer ; BEGIN
pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE ( ‘SYS_SQL_351516f2a705638a’) ;
dbms_output.put_line( pls || ‘個別削除’ ) ; END ;
/
Baseline の削除は sql_handle と plan_name を入力して行います。
値を入力しないと、すべての値が該当します。
DBA_SQL_PLAN_BASELINES ビューで確認
DBA_SQL_PLAN_BASELINES ビューの照会
SQL>SELECT *
SQL>FROM dba_sql_plan_baselines
| SIGNATURE | SQL_HANDLE | SQL_TEXT PLAN_NAME | CREATOR |
| ORIGIN | … |
———- ———————— ———- —————————— ——– —– 3.82498868 SYS_SQL_351516f2a705638a SELECT * SQL_PLAN_3a58qyamhaswa2b869b05 SYS
MANUAL-LOAD …
DBA_SQL_PLAN_BASELINES ビュー列の内容
| Column | Datatype | Description |
| SIGNATURE | NUMBER | SQL ID. V$SQL (AREA)ビュー |
| SQL_HANDLE | VARCHAR2(30) | BASLINE ID |
| SQL_TEXT | CLOB | SQL text |
| PLAN_NAME | VARCHAR2(30) | Plan ID |
| CREATOR | VARCHAR2(30) | BASELINE ジェネレータ |
| ORIGIN | VARCHAR2(14) | BASELINE 生成経路 |
| DESCRIPTION | VARCHAR2(500) | BASELINE 説明 |
| VERSION | VARCHAR2(64) | BASELINE 作成時の データベース バージョン |
| CREATED | TIMESTAMP(6) | BASELINE 作成 時間 |
| LAST_MODIFIED | TIMESTAMP(6) | BASELINE が最後に 変更 された時刻 |
| LAST_EXECUTED | TIMESTAMP(6) | BASELINE の最終 実行 時間 |
| LAST_VERIFIED | TIMESTAMP(6) | BASELINE の最終 検証 時刻 |
| ENABLED | VARCHAR2(3) | Optimizer で使用できるかどうかを示す属性 |
| ACCEPTED | VARCHAR2(3) | ACCEPTED プロパティ |
| FIXED | VARCHAR2(3) | FIXED プロパティ |
| AUTOPURGE | VARCHAR2(3) | 使わずに一定時間が過ぎる場合自動purge |
| OPTIMIZER_COST | NUMBER | BASELINE 作成時に POSTを開始します |
| MODULE | VARCHAR2(48) | 宣言 モジュール名を適用します |
| ACTION | VARCHAR2(32) | Application Action |
| EXECUTIONS | NUMBER | BASELINE生成時の値です |
| ELAPSED_TIME | NUMBER | BASELINE生成時の値です |
| CPU_TIME | NUMBER | BASELINE生成時の値です |
| BUFFER_GETS | NUMBER | BASELINE生成時の値です |
| DISK_READS | NUMBER | BASELINE生成時の値です |
| DIRECT_WRITES | NUMBER | BASELINE生成時の値です |
| ROWS_PROCESSED | NUMBER | BASELINE生成時の値です |
| FETCHES | NUMBER | BASELINE生成時の値です |
| END_OF_FETCH_COUNT | NUMBER | BASELINE生成時の値です |
SQL_PLAN_BASELINES 属性変更による利用実行計画の制御
■SPB 属性変更パッケージの内容
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2)
RETURN PLS_INTEGER;
- 変更可能属性 : enabled, fixed, autopurge, plan_name, description
Baseline は実行計画使用時優先順位を決定する属性で、 ENABLED、ACCEPTED、FIXED の
3つの属性があります。
上記3つの属性の中で、ENABLEDとACCEPTED属性は、Baselineが使用可能かどうかを示す属性で、いずれも「YES」状態のBaselineのみが使用可能です。
ENABLED属性はユーザーが直接変更できます。
ACCEPTED属性の場合、最初に登録されるBaselineと、ユーザーがカーソルキャッシュから手動で登録するBaselineは、「YES」状態で登録されます。
実行計画変更が発生して自動登録される実行計画の場合、「NO」状態で登録されます。
FIXED属性はBaseline使用時の優先順位を決定する属性で、ENABLEDとACCEPTED属性の両方が「YES」のBaselineのうち、FIXED属性が「YES」のBaselineが優先的に選択されます。 もし優先順位が同じBaselineが複数存在する場合には、OptimizerによってCostが低く判断されるBaselineが選択されます。
Baselineに登録されて使用可能なBaselineが存在し、DBパラメータがBaselineを使用するように設定されていると、SQL実行時にBaselineを介して実行計画を反映します。
その後、SQL以外の要因でSQLの実行計画に変化が生じた場合、新たに生成される実行計画はすぐにSQLの実行に反映されず、ACCEPTED属性が「NO」の状態で自動的にSPBに格納されます。 新しく生成されたBaselineは、検証過程を経てACCEPTED属性が「YES」の状態に変わるまでBaseline選択から削除されます。
■SPB 検証パッケージ
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT, verify IN VARCHAR2 := ‘YES’,
commit IN VARCHAR2 := ‘YES’) RETURN CLOB;
パッケージを実行すると、non accepted 状態の Baseline に対して検証タスクを実行します。 Verify値がyesの場合、time_limitに設定された時間以内に実際の検証操作を実行します。
Commitがyesの場合はaccepted属性をyesに置き換え、noの場合は変更しません。
結果の値で検証ジョブのレポートを作成します。
■ テスト
SQL>SELECT signature , SQL> sql_handle ,
SQL> plan_name ,
SQL> origin ,
SQL> enabled ,
SQL> accepted ,
SQL> fixed
SQL>FROM dba_sql_plan_baselines
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED OPTIMIZER_COST
———- ———————– ———————— ————– ——- — 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswa2b869b05 AUTO-CAPTURE
YES NO NO 1938
3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswaeaf1df04 AUTO-CAPTURE YES YES NO 2
現在下側 Baseline を使用中 新しい実行計画の生成で上側 Baseline が生成された状態。
(上FULL SCAN、下INDEX SCAN)
AcceptedがNOの状態であるため、Baselineは使用されません。
YESに変更するための検証作業の実行
DECLARE
pls clob ; BEGIN
pls := dbms_spm.evolve_sql_plan_baseline( ‘SYS_SQL_351516f2a705638a’ , ‘SQL_PLAN_3a58qyamhaswa2b869b05’ , DBMS_SPM.AUTO_LIMIT, ‘yes’ , ‘yes’ ) ; dbms_output.put_line( pls ) ;
END ;
/
——————————————————————————-
Evolve SQL Plan Baseline Report
——————————————————————————-
Inputs:
——-
SQL_HANDLE = SYS_SQL_351516f2a705638a
PLAN_NAME = SQL_PLAN_3a58qyamhaswa2b869b05 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = yes
COMMIT = yes
Plan: SQL_PLAN_3a58qyamhaswa2b869b05
————————————
Plan was verified: Time used .12 seconds.
Plan failed performance criterion: 95.07 times worse than baseline plan.
| Baseline Plan | Test Plan | Stats Ratio | |
| ————- | ——— | ———– | |
| Execution Status: | COMPLETE | COMPLETE | |
| Rows Processed: | 1 | 1 | |
| Elapsed Time(ms): | .159 | 10.617 | .01 |
| CPU Time(ms): | .111 | 10.553 | .01 |
| Buffer Gets: | 3 | 7153 | 0 |
| Physical Read Requests: | 0 | 0 | |
| Physical Write Requests: | 0 | 0 | |
| Physical Read Bytes: | 0 | 0 | |
| Physical Write Bytes: | 0 | 0 | |
| Executions: | 1 | 1 |
——————————————————————————-
Report Summary
——————————————————————————-
Number of plans verified: 1 Number of plans accepted: 0
検証作業の実行時にCommit値を「yes」と入力しましたが、 実際の検証時に新しく生成されたSQLは非効率的と
判断され認証されません。
新しいBaselineを使用するには、現在使用されているBaselineよりも優先順位を高くする必要があります。
Accepted 値を変更するために実際の Verify（検証）なしで強制変更
——————————————————————————-
Evolve SQL Plan Baseline Report
——————————————————————————-
Inputs:
——-
SQL_HANDLE = SYS_SQL_351516f2a705638a
PLAN_NAME = SQL_PLAN_3a58qyamhaswa2b869b05 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = no
COMMIT = yes
Plan: SQL_PLAN_3a58qyamhaswa2b869b05
————————————
Plan was changed to an accepted plan.
——————————————————————————-
Report Summary
——————————————————————————-
Number of plans verified: 0 Number of plans accepted: 1
Fixed 属性変更
DECLARE
pls pls_integer ; BEGIN
pls := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( ‘SYS_SQL_351516f2a705638a’ ,
‘SQL_PLAN_3a58qyamhaswa2b869b05’, ‘fixed’, ‘yes’) ; dbms_output.put_line( pls || ‘個別変更’ ) ;
END ;
/
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED OPTIMIZER_COST
———- ———————– ———————— ———— ——- —– 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswa2b869b05 AUTO-CAPTURE YES YES YES 1938
3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswaeaf1df04 AUTO-CAPTURE YES YES NO 2
SQL_ID SQL_TEXT SQL_PLAN_BASELINE PLAN_HASH_VALUE
———– ——————————- ————————— ————–
93zny6hkjj6s7 SELECT * FROM spmtest WHERE lv = 100 SQL_PLAN_3a58qyamhaswa2b869b05 1690735414
– Baseline使用時にFixedされた方が優先順位が高いため、対応するBaseline使用。
2つすべてがFixedになった場合
DECLARE
pls pls_integer ; BEGIN
pls := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( ‘SYS_SQL_351516f2a705638a’ ,
‘SQL_PLAN_3a58qyamhaswaeaf1df04’, ‘fixed’, ‘yes’) ; dbms_output.put_line( pls || ‘個別登録’) ;
END ;
/
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED OPTIMIZER_COST
——— ———————— ———————— ———— ——- —– 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswa2b869b05 AUTO-CAPTURE YES YES YES 1938
3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswaeaf1df04 AUTO-CAPTURE YES YES YES 2
SQL_ID SQL_TEXT SQL_PLAN_BASELINE PLAN_HASH_VALUE
———– ——————————- —————————— ———–
93zny6hkjj6s7 SELECT * FROM spmtest WHERE lv = 100 SQL_PLAN_3a58qyamhaswaeaf1df04 1577308861
– 優先順位が同じBaselineが存在する場合、COSTが低い方のBaselineが選択されます。
SPBにあるSQLのBaselineが存在すると、SQLのTextが同じSQLに対して実行計画の変化が生じると、その実行計画のBaselineが自動的に登録されます。
ただし、SPBのFIXED属性が「YES」のBaselineが存在し、そのBaselineが使用されている場合には、SQLの実行計画に影響を与える変更が生じても、新しい実行計画を生成しません。
以上の内容を次の表のようにまとめることができる。
結論
DBを運営する上で統計情報の変更やインデックスの状態変化、DBのパラメータ変更、DBバージョン変更など、SQLの実行計画を変化させることができる要因は多くあります。
また、これによる性能問題を経験する場合もあります。
ところが、このような問題が生じる可能性があるからといって、その作業をまったくしないというわけにはいきません。。
このようなときにSQLの実行計画の変化による問題を防ぐためにSPMの使用を考慮してみることができます。
SPMは複数の実行計画を保存し、流動的に使用可能です。
SPMの内容をよく理解し、適切に使用できる場合は、DBの運用に役立つ可能性があります。
