!!!実行計画とは
{{category Oracle,nolink}}実行計画とは、DML文(SELECT,UPDATE,DELETEなど)を実行するために、いくつかの手順を踏むことになります。この手順(アクセスパス)は、索引・行の読み込み、ソート、変換などの様々な動作が含まれています。これらの動作の組み合わせが実行計画です。

この実行計画を分析するために一般にはEXPLAIN PLANというコマンドを発行し、戻される結果により判断します。

!!!PLAN_TABLE の作成
実行計画を取得(EXPLAIN PLANコマンド、SQL*PlusのAUTOTRACE 等)するためには、PLAN_TABLE の作成が必要です。

PLAN_TABLEのクリエイト文は、サーバをインストールした環境の $(ORACLE_HOME)/rdbms/admin/utlxplan.sql です。

各ユーザにテーブルを作成しても良いですが、SYSユーザで作成し、パブリックシノニムで各ユーザが利用できるようにしても良いでしょう。

 REM sysユーザでログインして行う
 
 REM PLAN_TABLE作成
 @?/rdbms/admin/utlxplan.sql
 
 REM 権限を付与
 GRANT SELECT ON SYS.PLAN_TABLE TO PUBLIC;
 GRANT INSERT ON SYS.PLAN_TABLE TO PUBLIC;
 GRANT UPDATE ON SYS.PLAN_TABLE TO PUBLIC;
 GRANT DELETE ON SYS.PLAN_TABLE TO PUBLIC;
 
 REM パブリックシノニムの作成。
 CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;

!!!SqlPlusのAUTOTRACEを利用する
[[Sql*Plus 設定|Oracle/SqlPlusSet]]の'''AUTOTRACE'''を参照

!!!SQL実行計画の解析と表示
!!実行計画の解析
 EXPLAIN PLAN SET STATEMENT_ID = '一意の値' FOR /* 解析するSQL文を記述 */ ;

!!実行計画の表示
 SELECT ID, PARENT_ID, LEVEL,
  LPAD(' ', 2*(LEVEL-1)) || OPERATION || OPTIONS || OBJECT_NAME
 FROM PLAN_TABLE
 START WITH ID = 0 AND STATEMENT_ID = '一意の値'
 CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = '一意の値';

!!Oracle 実行計画の解析 および 表示  for SqlPlus
{{ref_code ExplainPlan.sql:sjis,,SQL}}
{{ref ExplainPlan.sql}} 

!!!SQLトレースの取得

!!Oracle INITファイルのトレース設定の確認
トレースファイルを出力する初期設定がされているか INITファイル(init.ora) に次が設定されているかを確認する。
 user_dump_destp=xxx

!!セッション単位のトレースON/OFF
トレースの設定
 alter session set sql_trace = true;

トレースの終了
 alter session set sql_trace = false;

!!Oracleインスタンス レベルでのSQLトレース
init.ora に "sql_trace = true" を設定する。

但し、init.ora に設定すると全てのセッションのSQLトレースが作成されるため、システムのパフォーマンスが著しく低下します。

!!別セッションに対してトレースを設定する
 -- 行数を設定して見やすくします
 -- SET LINE 150
 
 -- SID, SERIAL#を検索します
 SELECT USERNAME, SID, SERIAL#, substr(MACHINE,1,25)
   FROM V$SESSION
 -- WHERE USERNAME = xxx
 ;
 
 -- トレースの設定
 -- dbms_system.set_sql_trace_in_session は、ユーザー"SYS"の
 -- 所有オブジェクトなので、スキーマ名 "SYS." を修飾する
 -- SID, SERIAL# は上記のSQLの結果の値をセットする
 EXECUTE sys.dbms_system.set_sql_trace_in_session(SID ,SERIAL# ,TRUE);
 
 -- PL/SQL に対してのトレース設定
 DBMS_SESSION.SET_SQL_TRACE(TRUE);

!!SQLトレースの解析(tkprof)
tkprofユーティリティはSQL文の実行にかかった時間を1/100秒単位で、解析（Parse）、実行（Execute）、フェッチ（Fetch）の３つのフェーズで表示させることができます。

トレースを実行すると、統計情報は init.ora の user_dump_dest で指定したディレクトリにトレース・ファイル(*.trc)が出力されます。そのままでも見られますが、読みづらいのでTKPROFユーティリティを使って見やすい形式に整形します。

!!tkprofの実行
tkprofユーティリティは下記のようにして実行できます。
 tkprof xxx.trc xxx.prf [explain=user/pass@tsn] [sort=パラメータ]

::xxx.trc
:::整形するトレースファイル名
::xxx.prf
:::出力するプルーフファイル名
::explain
:::実行計画を出力する。user/pass@tsn は実行計画を作成するユーザを指定する。
::sort
:::経過時間など特定の項目に関して、統計情報を降順(悪い順)にソートし出力する。パラメータは、CPU時間(execpu, fchcpu)や経過時間(exeela, fchela)など。

!!tkprofのプルーフ内容
実行されたsql毎に"****"で区切られ、最後にsqlを実行したプロセス毎のTOTALが表示されます。

elapsedを見て10秒以上かかっているsqlがあれば、そのsqlの実行の際にINDEXを使用しているかをExecution Planで確認します。(full) が表示されていれば、INDEXが使用されていないことになります。

::Parse
:::問い合せのsql文をOptimizerが解析して、実行計画を作成。
::Execute
:::準備済みのSQL文、又はPL/SQLブロックの実行。又は無名PL/SQLブロックの実行。
::Fetch
:::問い合せの結果選択された行を検索して、選択リストの値をホスト変数に割り振り。
::Optimizer
:::オプティマイザーとはSQLを実行するために、最も効率的なアクセスパスを見つけるためのOracleの内部メカニズムのことです。

Optimizer goalの各モード
*RULE
**アクセスパス(SQLの実行計画)をoracleが設定しているランクを基に作成します。
**init.ora に "optimizer_mode = RULE" の記述があれば、ルールベース オプティマイザーが使用されます。
**ルールベースは、15のランクが存在し、ランク１がROWIDアクセス、ランク１５は全権検索です。
*CHOOSE
**データディクショナリの統計を基にした最適化の方法をOptimizerが選択します。
*COST
**コストに基づいた最適化を行います