Oracle/SqlPlan

実行計画とは

実行計画とは、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 設定の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

/* ************************************************************************** */
/* *  Oracle 実行計画(アクセスパス)の解析 および 表示  for SqlPlus          * */
/* ************************************************************************** */

/* SqlPlus項目設定 */
/* 90,90,90,A50,  9990,  9990,  9990 で1行80文字 */
/* 90,90,90,A70,999990,999990,999990 で1行110文字 */
SET LINESIZE 110
COLUMN "ID"  FORMAT 90;
COLUMN "PID" FORMAT 90;
COLUMN "Lv"  FORMAT 90;
COLUMN "Query Plan" FORMAT A70 WRAPPED;
COLUMN "Lines" FORMAT 999990;
COLUMN "Cost"  FORMAT 999990;
COLUMN "Bytes" FORMAT 999990;

/* OPTIMIZERモード設定 (Oracle10gでは廃止) */
-- ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS;
-- ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS;
-- ALTER SESSION SET OPTIMIZER_GOAL = RULE;
-- ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE;

/* プランテーブルの内容削除 */
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID = 'EXPLAIN';

/* 実行計画の解析 */
EXPLAIN PLAN SET STATEMENT_ID = 'EXPLAIN' FOR /* ↓解析するSQL文を記述↓ */
    SELECT SYSDATE FROM DUAL
/* ↑解析するSQL文を記述↑ */ ;

/* 実行計画の表示 */
SELECT
    ID AS "ID", PARENT_ID AS "PID", LEVEL AS "Lv",
    LPAD(' ', 2*(LEVEL-1)) || OPERATION ||
        CASE WHEN OPTIONS IS NOT NULL THEN ' (' || OPTIONS || ')' END ||
        CASE WHEN OBJECT_NAME IS NOT NULL THEN ' OF ''' || OBJECT_NAME || '''' END
        AS "Query Plan",
    CARDINALITY AS "Lines", COST AS "Cost", BYTES "Bytes"
FROM PLAN_TABLE
START WITH ID = 0 AND STATEMENT_ID = 'EXPLAIN'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'EXPLAIN';

/* ************************************************************************** */
/* *  End Of File (ExplainPlan.sql)                                         * */
/* ************************************************************************** */

ExplainPlan.sql(501)

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
- コストに基づいた最適化を行います

最終更新時間：2009年02月06日 12時51分53秒　指摘や意見などあればSandBoxのBBSへ。

ExplainPlan.sql

INDEX