・SPMを利用した実行計画固定設定手順のメモ
★不可視索引を利用させる実行計画で固定する事も出来るっぽい
●作業概要
・遅いSQLのSQL_HANDLEに対してINDEXヒント等、改善したSQLのベースラインを作成し、遅いSQLのベースラインを削除する流れ
1.対象SQLの「SQL_ID」と「PLAN_HASH_VALUE」を取得 2.対象SQLの現状の実行計画をSPMへロードしベースライン作成 3.現状の実行計画のベースラインを確認 4.ヒント句付きSQLの「SQL_ID」と「PLAN_HASH_VALUE」を取得 5.ヒント句付きSQLの実行計画をSPMへロードしベースライン作成 →※ここで同一SQL_HANDLEに対して2種のベースラインが存在する状態となる 6.現状のベースライン（遅い実行計画）を削除 7.SQL_HANDLEに対して1種のみベースラインが存在（ヒント句付き実行計画）する状態となる 8.ヒント句付きペースラインを固定化する →※AUTO-CAPTUREによる不要なベースラインが増えることを抑止する
●SPM利用前提DB側初期化パラメータ
※前提条件：11g以降のEnterpriseEdition（オプションでバラ売りしてくれれば良いのに…）
1.SQL計画の自動取得：OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE 2.取得したSQL計画の使用：OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
今回はSQL計画の自動取得は行わず、使用のみを許可する設定とする。
●詳細手順
※以下、下記のIDの場合の例を記載
問題SQL_ID ：5w3ttmzk5vnn4 問題PLAN_HASH_VALUE ：1879779204 ヒント付きSQL_ID ：82ryzra1857sy ヒント付きPLAN_HASH_VALUE ：981806224 SQL_HANDLE ：SQL_c7fb9afce03237e6 問題PLAN_NAME ：SQL_PLAN_cgywuzmh34dz6ea53fa62 ヒント付きPLAN_NAME ：SQL_PLAN_cgywuzmh34dz67c65d0b0
■1.対象SQLの「SQL_ID」と「PLANL_HASH_VALUE」を取得
・アプリケーションなどからSQLを実行し、V$SESSIONから取得する
・V$SESSION情報からのSQL_IDを取得例
※machine,program,event,wait_timeなどからアタリをつける
set line 120 set pages 1000 col machine for a30 col program for a30 alter session set nls_date_format ='YYYY-MM-DD HH24:MI:SS'; select sid,serial#,username,machine,program,event,wait_time,sql_id from v$session where type!='BACKGROUND' and status ='ACTIVE' order by event,wait_time;
・「SQL_ID」からの「PLANL_HASH_VALUE」確認
set line 120 set pages 1000 select sql_id,plan_hash_value from v$sql where sql_id ='対象のSQLID' /
■2.対象SQLの現状の実行計画をSPMへロードしベースライン作成
・ベースライン作成
var res number exec :res :=dbms_spm.load_plans_from_cursor_cache( sql_id => '問題SQL_ID', plan_hash_value => '問題PLAN_HASH_VALUE');
・ベースライン作成実行例
var res number exec :res :=dbms_spm.load_plans_from_cursor_cache( sql_id => '5w3ttmzk5vnn4', plan_hash_value => '1879779204');
■3.現状の実行計画のベースラインを確認
・下記を実行し、SQL_HANDLE、PLAN_NAMEを控える
・ベースライン確認SQL
select sql_handle,plan_name, sql_text,origin, enabled, accepted, fixed, to_char(created,'YY/MM/DD HH24:MI:SS') created, to_char(last_modified,'YY/MM/DD HH24:MI:SS') modified, to_char(last_executed,'YY/MM/DD HH24:MI:SS') executed, to_char(last_verified,'YY/MM/DD HH24:MI:SS') verified, optimizer_cost, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, rows_processed, fetches from dba_sql_plan_baselines order by created;
・ベースライン詳細チェック
select * from table( dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_c7fb9afce03237e6', format=>'basic'));
■4.ヒント句付きSQLの「SQL_ID」と「PLANL_HASH_VALUE」を取得
・ヒント句付きでSQLを実行し、V$SESSIONから取得する
■5.ヒント句付きSQLの実行計画をSPMへロードしベースライン作成
※ここで同一SQL_HANDLEに対して2種のベースラインが存在する状態となる
・ヒント付きベースライン作成
var res number exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => 'ヒント付きSQL_ID',plan_hash_value => 'ヒント付きPLAN_HASH_VALUE', sql_handle => 'SQL_HANDLE');
・ヒント付きベースライン作成実行例
var res number exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => '82ryzra1857sy',plan_hash_value => '981806224', sql_handle => 'SQL_c7fb9afce03237e6');
※項番3の手順で同一SQL_HANDLEに対して2種のベースラインが作成されたことを確認する
■6.現状のベースライン（遅い実行計画）を削除
・ベースライン削除プロシージャ
var res number exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_HANDLE','問題PLAN_NAME');
・遅いベースライン（最初に登録したベースライン）を削除例
var res number exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_c7fb9afce03237e6','SQL_PLAN_cgywuzmh34dz6ea53fa62');
■7.SQL_HANDLEに対して1種のみベースラインが存在（ヒント句付き実行計画）する状態となる
※項番3の手順で同一SQL_HANDLEに対して1種のみベースラインが存在することを確認する
■8.ヒント句付きペースラインを固定化（Fixed化）する
※自動取得等による不要なベースラインORIGIN列「AUTO-CAPTURE」が増えることを抑止する
★ベースラインの固定（Fixed化）について
★FIXED属性=YES/NO における動作の違い FIXED属性=YES → SQL計画ベースラインの実行計画を固定する FIXED属性=YES/NO で以下のような動作の違いがある ・FIXED属性=NO SQL計画ベースラインを使用した際に、すでに登録されている SQL計画ベースラインよりも コストが低い実行計画がある場合、その実行計画を新たな SQL計画ベースラインとして登録 ・FIXED属性=YES 新たなSQL計画ベースラインを登録しない ・1つのSQLに対してSQLFIXED属性=YES と NO の複数の SQL計画ベースラインが存在する場合、 FIXED属性=NO の SQL計画ベースラインの方がコストが低い場合であっても、FIXED属性=YES のSQL計画ベースラインの使用を優先する。
・ベースラインの固定
var res number execute :res := dbms_spm.alter_sql_plan_baseline(- sql_handle =>'SQL_HANDLE',- plan_name =>'固定するPLAN_NAME',- attribute_name => 'FIXED',- attribute_value => 'YES');
・ベースラインの固定実行例
var res number execute :res := dbms_spm.alter_sql_plan_baseline(- sql_handle =>'SQL_c7fb9afce03237e6',- plan_name =>'SQL_PLAN_cgywuzmh34dz67c65d0b0',- attribute_name => 'FIXED',- attribute_value => 'YES');
※項番3の手順でFIXED列値が「YES」となった事を確認して終了
