本番稼働後ふとしたきっかけでSQLの実行プランが変更してしまい急に遅くなってしまう事があります。本来であればアプリケーションから発行されるSQLを見直したり、ヒント句を入れて相応のパフォーマンスが出るようにすれば良いのですが、ソースに手を入れたものをそう簡単にリリースできないので緊急を要する場合SPM計画ベースラインを使って対応する事があります。
大まかな手順
- ライブラリキャッシュ（GV$SQL）から対応が必要なSQL_IDを取得する。（旧SQL）
- 旧SQLをベースラインとして仮登録する。（新SQLを登録する際のSQL_HANDLE取得が目的）
- ヒント句を入れた新SQLを実行しSQL_IDを取得する
- ヒント句を入れた新SQLを「SQL管理ベース」へ登録する
- 不要なベースライン(旧SQLベースライン)の削除
SPM利用環境を事前に確認
select name,value from v$parameter where name like'%sql_plan%' order by name ;
| NME | VALUE | |
| optimizer_capture_sql_plan_baselines | FALSE | FALSE になっている事（TRUEになっていると、日々自動でどんどんベースラインが追加されていく） デフォルトは「FALSE」になっている |
| optimizer_use_sql_plan_baselines | TRUE | TRUEになっている事（ベースラインに登録したもの（かつ使用可能なもの）を利用する） デフォルトは「TRUE」になっている |
上記の値は何れもalter systemにて設定変更が可能です
SQL> alter system set optimizer_capture_sql_plan_baselines=FALSE
2 ;
システムが変更されました。
SQL> alter system set optimizer_use_sql_plan_baselines=TRUE
2 ;
システムが変更されました。
SQL> set lin 200
SQL> COLUMN name FORMAT A40
SQL> COLUMN value FORMAT A5
SQL> select name,value from v$parameter where name like'%sql_plan%' order by name
2 ;
NAME VALUE
---------------------------------------- -----
optimizer_capture_sql_plan_baselines FALSE
optimizer_use_sql_plan_baselines TRUE
SQL>手順１．V$SQLからSPM対策対象のSQL情報を取得する
SPMベースラインへ登録するには、SQL_IDとPLAN_HASH_VALUEが必要になります。「SPMベースラインへ登録する」と判断するまでには、それなりの分析がされている前提なので以下はSQL_IDをキーにgv$sqlから各種情報を抽出しています。（PLAN_HASH_VALUE、INST_ID
select t.INST_ID -- どこのインスタンスなのか ,t.SQL_ID -- ,t.CHILD_NUMBER -- 共有プールからパージする際に利用 ,t.ADDRESS -- 共有プールからパージする際に利用 ,t.HASH_VALUE ,t.PLAN_HASH_VALUE -- ベースライン登録時に利用 ,t.EXACT_MATCHING_SIGNATURE -- 正規化後SQLシグネチャ ,t.SQL_PLAN_BASELINE ,t.FIRST_LOAD_TIME ,t.PARSING_SCHEMA_NAME ,t.SQL_TEXT from gv$sql t where t.SQL_ID = 'banf5vm5rpd12' order by t.INST_ID,t.CHILD_NUMBER ;
手順２．DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEを使いSQLベースラインとして登録
このファンクションは４つのオーバロードがありますが、以下例ではSQL_IDとPLAN_HASH_VALUEをパラメータにSQLベースラインとして「SQL管理ベース」(SYSAUX表領域)へ登録されます。
注意点として、RAC環境を利用している場合手順１で得られたINST_IDに対応するインスタンスでプロシージャ実行をする必要があります。plan_hash_valueにnullを指定した場合、指定のSQL_IDに紐付く全てのプラン（カーソルキャッシュに存在するもの）が対象となります。そのためこちらのファンクションの戻り値は登録プラン件数となっています。同じものを何度登録してもプランの上書きがされるだけで特に害はないはずです。
declare
aa number;
begin
aa:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id =>'banf5vm5rpd12',
plan_hash_value => 2470095318);
end
;
手順３．登録したベースラインを確認
登録日、SQLの内容から自身が登録したものか確認する。ベースライン登録のタイミングでSQL_HANDLEが採番されているのでこの値をメモっておきます。後に使用します。
select t.SIGNATURE ,t.SQL_HANDLE -- これを後に使用します ,t.PLAN_NAME ,t.CREATOR ,t.CREATED ,t.MODULE ,t.ORIGIN ,t.ACCEPTED ,t.EXECUTIONS --,t.SQL_TEXT from dba_sql_plan_baselines t where t.ACCEPTED ='YES' order by t.CREATED desc ;
手順４．新しい実行計画が採用されるSQLを実行しSQL_IDを取得する
対策が必要なSQLにヒント句をつけてSQLを実行する。この時流すSQLにはコメントとして、V$SQLのtext_areaからSQL_IDを抽出する際のキーワードを付けておく必要があります。私の場合手順１でベースラインとして登録したSQL_IDをキーワードとして使います。
select s.INST_ID ,s.SQL_ID -- この値を取得 ,s.CHILD_NUMBER ,s.SQL_TEXT from gv$sql s where s.SQL_TEXT like '%* g7j2u2mg11752 *%' ;
手順５．新しい実行計画採用SQLを「SQL管理ベース」へ登録する。
手順４で取得したSQL_IDと手順３で取得したSQL_HANDLEをインプットパラメータにDBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEを使ってSQL管理ベースへ登録します。手順２で当プロシージャを利用した際は、SQL_ID+PLAN_HASH_VALUEでしたが、ここではSQL_HANDLEを利用しているところがポイントです
declare
aa number;
begin
aa:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id =>'1wykrqt6a3r0y',
sql_handle => 'SQL_81de8173c820318d');
end;
手順６．ヒント句付き新プラン登録の確認
SELECT b.SIGNATURE ,b.SQL_HANDLE ,b.PLAN_NAME --,b.SQL_TEXT ,b.CREATED ,b.ORIGIN ,b.ACCEPTED ,b.EXECUTIONS FROM dba_sql_plan_baselines b where b.SQL_HANDLE='SQL_81de8173c820318d' and b.ORIGIN='MANUAL-LOAD-FROM-CURSOR-CACHE' order by b.CREATED desc ;
| SIGNATURE | 9358059410001244557 | 9358059410001244557 |
| SQL_HANDLE | SQL_81de8173c820318d | SQL_81de8173c820318d |
| PLAN_NAME | SQL_PLAN_83rn1fg420ccd2720d6b7 | SQL_PLAN_83rn1fg420ccd441fc3ea |
| CREATED | 2021-06-22 23:31:47 | 2021-06-22 23:29:50 |
| ORIGIN | MANUAL-LOAD-FROM-CURSOR-CACHE | MANUAL-LOAD-FROM-CURSOR-CACHE |
| ACCEPTED | YES | YES |
| EXECUTIONS | 1 | 72399 |
手順７．不要なベースラインの削除
SQLプラン名とSQL_HANDLEをキーにベースラインを削除する
declare
aa number;
begin
aa:= DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle =>'SQL_81de8173c820318d',
plan_name => 'SQL_PLAN_83rn1fg420ccd441fc3ea');
end;
ベースラインをまとめて削除する際のサンプル
以下は無名ブロックと動的SQL（EXECUTE IMMEDIATE）を使い「無名ブロックを実行」しています。カーソル中のSELECTに適宜条件を入れて使ってください。
declare exesql varchar2(800); BEGIN FOR CUR IN (select SQL_HANDLE,PLAN_NAME from dba_sql_plan_baselines order by CREATED desc fetch first 10 rows only ) LOOP exesql := 'declare aa number; begin aa:= DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle =>'''|| CUR.SQL_HANDLE||''',plan_name => '''|| CUR.PLAN_NAME||'''); end;'; EXECUTE IMMEDIATE exesql; DBMS_OUTPUT.PUT_LINE(exesql); END LOOP; END;
手順８．不要なベースラインが削除されたことを確認
SQL_HANDLEをキーに確認する
select b.SIGNATURE ,b.SQL_HANDLE ,b.PLAN_NAME --,b.SQL_TEXT ,b.CREATED ,b.VERSION ,b.CREATOR ,b.CREATED ,b.MODULE ,b.ORIGIN ,b.ACCEPTED ,b.EXECUTIONS FROM dba_sql_plan_baselines b where b.SQL_HANDLE='SQL_81de8173c820318d' order by b.CREATED ;
手順９．登録したベースラインがすぐに採用されるように
手順８までの作業で新たなプランをベースラインへ登録する事は完了していますが、ベースライン登録前から共有プールに対象SQL（実行計画を含む解析済SQL情報）がキャッシュされたままになっていると古いアクセスプランが採用され続けられてしまいます。そのため、sys.dbms_shared_pool.purgeを使ってSQLをパージします。
exec sys.dbms_shared_pool.purge('0000000892AE9F70,3725630626','C')
;
パージする際にはいくつか注意点あるのでこちらも合わせて参照ください。
手順10．登録したベースラインが採用されたのか確認する
V$SQLのSQL_PLAN_BASELINEに手順６で確認した「PLAN_NAME」が出力されていることで確認終了です。
select t.INST_ID ,t.SQL_ID ,t.CHILD_NUMBER ,t.ADDRESS ,t.HASH_VALUE ,t.PLAN_HASH_VALUE ,t.SQL_PLAN_BASELINE ,t.FIRST_LOAD_TIME ,t.PARSING_SCHEMA_NAME from gv$sql t where t.SQL_ID = 'g7j2u2mg11752' order by t.INST_ID,t.CHILD_NUMBER ;
おまけ１：PLAN_NAMEから実際のプラン内容を確認する
上記手順４で新しく登録したSQLの実行プランを確認できます。
select * from table( dbms_xplan.display_sql_plan_baseline( plan_name=>'SQL_PLAN_83rn1fg420ccd2720d6b7', format=>'basic' ) )
おまけ２：有効な一覧出力
現時点で有効なベースライン一覧を出力する
select b.PLAN_NAME,b.SIGNATURE,b.SQL_HANDLE,b.CREATED FROM dba_sql_plan_baselines b where b.ACCEPTED ='YES' order by b.CREATED,b.SQL_HANDLE desc ;
