SQL実行計画取得手順

アプリ側からの情報が殆ど無い場合の実行計画取得手順メモ

■対象SQLが不明な場合以下でセッション情報からSQL_IDを取得する
・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
/

①OLD_HASH_VALUE,PLAN_HASH_VALUEなどからSQL_IDとSQL本文を取得

select sql_id, to_char(sql_fulltext)
from v$sql
where plan_hash_value = '{ハッシュ値}'
/

※SQLが非常に大きい場合

select sql_text
from v$sqltext
where sql_id='｛SQL_ID｝'
order by piece
/

②SQL_IDに対応したバインド変数を取得
・任意のLAST_CAPTUREDを選択する

set line 130
set pages 1000
col name for a16
col datatype_string for a32
col value_string for a48
select
sb.last_captured
,sb.name
,sb.datatype_string
,sb.value_string
from v$sql_bind_capture sb
where sb.sql_id='{SQL_ID}'
order by sb.last_captured,sb.name,sb.value_string
/

■PL/SQLバインド変数埋め込み形式整形
・VARIABLE部分出力

SELECT
 'variable var' || TO_CHAR(SUBSTR(NAME, 2)) || ' ' || DATATYPE_STRING || ';' as VARIABLE
FROM V$SQL_BIND_CAPTURE SB
WHERE SB.SQL_ID='{SQL_ID}'
ORDER BY SB.LAST_CAPTURED DESC,SB.NAME,SB.VALUE_STRING
/

・EXECUTE部分出力

SELECT
 'execute :var' || TO_CHAR(SUBSTR(NAME, 2)) || ' := ' || CHR(39) || TRIM(VALUE_STRING) || CHR(39) || ';' as EXECUTE
FROM V$SQL_BIND_CAPTURE SB
WHERE SB.SQL_ID='{SQL_ID}'
ORDER BY SB.LAST_CAPTURED DESC,SB.NAME,SB.VALUE_STRING
/

③上記の結果をもとにバインド埋め込み形式でSQLを作成
※①で取得したSQLのバインド変数部分は下記書式に修正する

variable  var1   varchar2(4);
variable  var2   varchar2(8);
variable  var3   number;
execute :var1 := 'TEST';
execute :var2 := 'TEST1234';
execute :var3 := 9999;
select col1,col2,col3
from test_tbs
where col1 = :var1
and col2 = :var2
and col3 = :var3
/

④以下でactual付き実行計画を取得
・SYSDBAで接続して実行
・spool名は適時変更（いずれ変数化したい）
※term offしておかないと大量の結果を返すSQLの場合に標準出力が酷いことに

spool {出力ファイル名}
set trimspool on
set timing on
set term off
alter session set STATISTICS_LEVEL = 'ALL';
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
----------------------------------------
ここで④で作成したSQLを実行
この手順は後続のdbms_xplan.display_cursorにて「直前に実行されたSQLの実行計画を取得する」
という内容になるため対象SQL以外実行しないように
----------------------------------------
set linesize 300
col PLAN_TABLE_OUTPUT format a300
set pagesize 1000
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST'))
/
spool off;

■AUTOTRACE形式の場合は下記で実行計画取得
・spool名は適時変更

spool {出力ファイル名}
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set timing on
set line 300
set autotrace traceonly
----------------------------------------
ここで④で作成したSQLを実行
----------------------------------------
set autotrace off