oracleチューニング

Oracle SPMベースライン登録

本番稼働後ふとしたきっかけで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>

手順1.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
;

手順2.DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEを使いSQLベースラインとして登録

このファンクションは4つのオーバロードがありますが、以下例ではSQL_IDとPLAN_HASH_VALUEをパラメータにSQLベースラインとして「SQL管理ベース」(SYSAUX表領域)へ登録されます。
注意点として、RAC環境を利用している場合手順1で得られた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
;

手順3.登録したベースラインを確認

登録日、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
;

手順4.新しい実行計画が採用されるSQLを実行しSQL_IDを取得する

対策が必要なSQLにヒント句をつけてSQLを実行する。この時流すSQLにはコメントとして、V$SQLのtext_areaからSQL_IDを抽出する際のキーワードを付けておく必要があります。私の場合手順1でベースラインとして登録した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 *%'
;

手順5.新しい実行計画採用SQLを「SQL管理ベース」へ登録する。

手順4で取得したSQL_IDと手順3で取得したSQL_HANDLEをインプットパラメータにDBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEを使ってSQL管理ベースへ登録します。手順2で当プロシージャを利用した際は、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;

手順6.ヒント句付き新プラン登録の確認

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

手順7.不要なベースラインの削除

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;

手順8.不要なベースラインが削除されたことを確認

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
;

手順9.登録したベースラインがすぐに採用されるように

手順8までの作業で新たなプランをベースラインへ登録する事は完了していますが、ベースライン登録前から共有プールに対象SQL(実行計画を含む解析済SQL情報)がキャッシュされたままになっていると古いアクセスプランが採用され続けられてしまいます。そのため、sys.dbms_shared_pool.purgeを使ってSQLをパージします。

exec sys.dbms_shared_pool.purge('0000000892AE9F70,3725630626','C')
;

パージする際にはいくつか注意点あるのでこちらも合わせて参照ください。

手順10.登録したベースラインが採用されたのか確認する

V$SQLのSQL_PLAN_BASELINEに手順6で確認した「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
;
おまけ1:PLAN_NAMEから実際のプラン内容を確認する

上記手順4で新しく登録したSQLの実行プランを確認できます。

select * from table(
   dbms_xplan.display_sql_plan_baseline(
   plan_name=>'SQL_PLAN_83rn1fg420ccd2720d6b7',
   format=>'basic'
   )
)
おまけ2:有効な一覧出力

現時点で有効なベースライン一覧を出力する

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
;
スポンサーリンク