この章の内容は次のとおりです。
EXPLAIN
PLAN
文は、SELECT
、UPDATE
、INSERT
およびDELETE
文に関して、オプティマイザによって選択された実行計画を表示します。この項の内容は次のとおりです。
文の実行計画とは、その文を実行するためにデータベースで行われる一連の処理です。
行ソース・ツリーは、実行計画の中核です。このツリーには、次の情報が表示されます。
文によって参照される表の順序
文で言及される各表へのアクセス方法
文の結合操作の影響を受ける表の結合方法
フィルタ、ソート、集計などのデータ操作
PLAN TABLEには、行ソース・ツリーの他、次の情報が含まれています。
最適化。各操作のコストとカーディナリティについて。
パーティション化。アクセスされたパーティションのセットなど。
パラレル実行。結合入力の配分方法など。
EXPLAIN PLAN
の結果を使用して、オプティマイザが特定の実行計画(たとえば、ネステッド・ループ結合)を選択するかどうかを判断できます。また、オプティマイザの決定(たとえば、オプティマイザがハッシュ結合でなくネステッド・ループ結合を選択した理由)を理解するためにも役立ちます。
関連項目:
EXPLAIN PLAN
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
実行計画は基礎となるオプティマイザ入力が変化するたびに変化します。
EXPLAIN PLAN
の出力は、EXPLAINされたSQL文をデータベースがどのように実行するかを示します。実行環境とEXPLAIN PLAN環境は異なるため、この計画は、SQL文で使用される実際の実行計画とは異なる可能性があります。
注意:
実行計画の変更から生じる可能性があるSQLパフォーマンスの低下を回避するには、SQL計画の管理を使用することを検討してください。
関連項目:
スキーマは、様々な理由で異なる場合があります。
主な理由は次のとおりです。
実行とEXPLAIN PLANのデータベースが異なる場合。
文をEXPLAINするユーザーが、文を実行するユーザーとは異なる場合。2人のユーザーが同じデータベース内の異なるオブジェクトを指していれば、異なる実行計画が発生します。
2つの操作間でスキーマが変更された場合(多くは索引の変更)。
EXPLAIN PLANを調べることにより、次の場合の排除行数を確認できます。
全体スキャン
選択性のないレンジ・スキャン
遅延した述語フィルタ
誤った結合順序
遅延したフィルタ処理
例6-1の計画では、最後のステップは非常に選択性のないレンジ・スキャンです。このレンジ・スキャンは76563回実行され、11432983行にアクセスし、アクセスした行の99パーセントを排除して76563行を保持します。11432983行にアクセスした結果、必要な行が76563行のみであると認識された理由について考えます。
例6-1 EXPLAIN PLAN内の排除行数の確認
Rows Execution Plan -------- ---------------------------------------------------- 12 SORT AGGREGATE 2 SORT GROUP BY 76563 NESTED LOOPS 76575 NESTED LOOPS 19 TABLE ACCESS FULL CN_PAYRUNS_ALL 76570 TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL 76570 INDEX RANGE SCAN (object id 178321) 76563 TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL 11432983 INDEX RANGE SCAN (object id 186024)
実行計画の操作のみでは、よく調整された文とうまく機能しない文を区別できません。たとえば、文による索引の使用がEXPLAIN
PLAN
出力で示されたとしても、その文が効率的に機能するとはかぎりません。効率的でない索引もあります。この場合、次を調べることをお薦めします。
使用される索引の列
その索引の選択性(アクセスされる表の一部)
EXPLAIN
PLAN
を使用してアクセス計画を判断し、後からテストによってそれが最適な計画であることを確認するのが最もよい方法です。計画を評価する際は、文の正確なリソース使用量を調べてください。
EXPLAIN
PLAN
コマンドを実行して計画を表示するかわりに、V$SQL_PLAN
ビューを問い合せて計画を表示できます。V$SQL_PLAN
には、共有SQL領域に格納されたすべての文の実行計画が含まれます。その定義は、PLAN_TABLE
に類似しています。「PLAN_TABLE列」を参照してください。
V$SQL_PLAN
がEXPLAIN
PLAN
よりも優れている点は、特定の文の実行に使用されたコンパイル環境を知らなくてもかまわないということです。EXPLAIN
PLAN
の場合は、文の実行時に同じ計画を取得するために同一環境をセットアップする必要があります。
V$SQL_PLAN_STATISTICS
ビューは、出力行数や経過時間など、計画に含まれる操作ごとに実際の実行統計を提供します。出力行数を除き、すべての統計は累積されます。たとえば、結合操作の統計には、2つの入力の統計も含まれます。V$SQL_PLAN_STATISTICS
の統計は、STATISTICS_LEVEL
初期化パラメータをALL
に設定してコンパイルされたカーソルに使用できます。
V$SQL_PLAN_STATISTICS_ALL
ビューを使用すると、行数と経過時間に関してオプティマイザにより提供される見積りを並べて表示できます。このビューでは、各カーソルのV$SQL_PLAN
およびV$SQL_PLAN_STATISTICS
情報が結合されます。
関連項目:
V$SQL_PLAN_MONITOR
ビューについては、「データベース操作の監視」を参照してください
V$SQL_PLANビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
STATISTICS_LEVEL
初期化パラメータについては、『Oracle Databaseリファレンス』を参照してください
PLAN_TABLE
は、グローバル一時表に対するパブリック・シノニムとして自動的に作成されます。この一時表に、すべてのユーザーに対するEXPLAIN
PLAN
文の出力が保持されます。PLAN_TABLE
は、EXPLAIN
PLAN
文が実行計画について記述している行を挿入するデフォルトのサンプル出力表です。表内の列の詳細は、「PLAN_TABLE列」を参照してください。
PLAN_TABLE
表は各ユーザーに対し自動的に設定されますが、SQLスクリプトcatplan.sql
を使用して、グローバル一時表とPLAN_TABLE
シノニムを手動で作成できます。このスクリプトの名前と位置は、使用するオペレーティング・システムによって異なります。UNIXおよびLinux上では、このスクリプトは$ORACLE_HOME/rdbms/admin
ディレクトリにあります。
たとえば、SQL*Plusセッションを開始し、SYSDBA
権限で接続し、そして次のようにスクリプトを実行します。
@$ORACLE_HOME/rdbms/admin/catplan.sql
データベースのバージョンを更新した場合は、列が変更される可能性があるため、ローカルのPLAN_TABLE
表を削除して再作成することをお薦めします。表を指定する場合は、スクリプトの実行が失敗したり、TKPROF
が失敗する場合があります。
名前PLAN_TABLE
を使用しない場合は、catplan.sql
スクリプトの実行後に新しいシノニムを作成します。次に例を示します。
CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$
EXPLAIN PLAN
文により、オプティマイザがSQL文に対して選択した実行計画を確認できます。文が発行されると、オプティマイザが実行計画を選択した後で、計画を説明するデータがデータベース表に挿入されます。EXPLAIN PLAN
文を発行してから、出力表を問い合せてください。
SQLスクリプトCATPLAN
.SQL
を使用し、使用しているスキーマ内にPLAN_TABLE
というサンプル出力表を作成します。「PLAN_TABLEの作成のガイドライン」を参照してください。
SQL文の前に、EXPLAIN PLAN
FOR
句を含めます。
EXPLAIN PLAN
文を発行した後、Oracle Databaseから提供されるスクリプトまたはパッケージを使用して最新のPLAN TABLE出力を表示します。「PLAN_TABLE出力の表示」を参照してください。
EXPLAIN PLAN
の出力実行順序は、最も右端にインデントされている行から始まります。次のステップは、その行の親です。2つの行が等しくインデントされている場合、通常、一番上の行が最初に実行されます。
注意:
この章のEXPLAIN
PLAN
出力表は、utlxpls.sql
スクリプトで表示したものです。
この章のEXPLAIN
PLAN
出力のステップは、システムによって異なる場合があります。データベース構成によって、オプティマイザは異なる実行計画を選択する場合があります。
SQL文をEXPLAINする場合は、文の直前でEXPLAIN
PLAN
FOR
句を使用します。次に例を示します。
EXPLAIN PLAN FOR SELECT last_name FROM employees;
計画をEXPLAINしたものがPLAN_TABLE
表に挿入されます。PLAN_TABLE
から実行計画を選択できるようになります。「PLAN_TABLE出力の表示」を参照してください。
複数の文があるときは、文の識別子を指定し、その識別子で特定の実行計画を識別できます。SET
STATEMENT
ID
を使用する前に、その文と同じ識別子を持つ既存の行を削除してください。
例6-2の場合は、st1
が文の識別子として指定されています。
例6-2 STATEMENT ID句でのEXPLAIN PLANの使用方法
EXPLAIN PLAN SET STATEMENT_ID = 'st1' FOR SELECT last_name FROM employees;
次の文のように、INTO
句を指定して異なる表を指定できます。
EXPLAIN PLAN INTO my_plan_table FOR SELECT last_name FROM employees;
次の文のように、INTO
句を使用する場合は、文のIDを指定できます。
EXPLAIN PLAN SET STATEMENT_ID = 'st1' INTO my_plan_table FOR SELECT last_name FROM employees;
関連項目:
EXPLAIN PLAN
構文の詳細は、Oracle Database SQL言語リファレンスを参照してください。
UTLXPLS
.SQL
このスクリプトは、シリアル処理のためのPLAN TABLE出力を表示します。例6-4は、UTLXPLS
.SQL
スクリプトを使用した場合のPLAN TABLE出力の例です。
UTLXPLP
.SQL
このスクリプトは、パラレル実行列を含むPLAN TABLE出力を表示します。
DBMS_XPLAN.DISPLAY
テーブル・ファンクション
このファンクションは、PLAN TABLE出力を表示するオプションを受け入れます。指定できるのは次のとおりです。
PLAN_TABLE
とは別の表を使用している場合のPLAN TABLE名
EXPLAIN
PLAN
に文の識別子を設定した場合の文の識別子
詳細レベルを決定するフォーマット・オプション: BASIC
、SERIAL
、TYPICAL
およびALL
DBMS_XPLAN
を使用してPLAN_TABLE
出力を表示する例を次に示します。
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));
関連項目:
DBMS_XPLANパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
例6-3では、EXPLAIN PLAN
を使用して、IDが103より小さい従業員のemployee_id
、job_title
、salary
およびdepartment_name
を選択するSQL文を確認します。
例6-3 EXPLAIN PLANの使用方法
EXPLAIN PLAN FOR SELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103 AND e.job_id = j.job_id AND e.department_id = d.department_id;
例6-4 EXPLAIN PLAN出力
次の出力表では、例6-3にあるSQL文を実行するためにオプティマイザで選択する実行計画が示されています。
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)| | 1 | NESTED LOOPS | | 3 | 189 | 10 (10)| | 2 | NESTED LOOPS | | 3 | 141 | 7 (15)| |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)| | 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)| |* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)| |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."EMPLOYEE_ID"<103) 5 - access("E"."JOB_ID"="J"."JOB_ID") 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 189 | 8 (13)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 3 | 189 | 8 (13)| 00:00:01 | | 3 | MERGE JOIN | | 3 | 141 | 5 (20)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID | JOBS | 19 | 513 | 2 (0)| 00:00:01 | | 5 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 | |* 6 | SORT JOIN | | 3 | 60 | 3 (34)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 3 | | 1 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("E"."JOB_ID"="J"."JOB_ID") filter("E"."JOB_ID"="J"."JOB_ID") 8 - access("E"."EMPLOYEE_ID"<103) 9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
文の識別子を指定した場合は、PLAN_TABLE
を問い合せるための独自のスクリプトを書くことができます。次に例を示します。
START WITH ID = 0およびSTATEMENT_ID
を指定します。
CONNECT
BY
句を使用して親から子へツリーを移動します。結合キーは、STATEMENT_ID
= PRIOR
STATEMENT_ID
およびPARENT_ID
= PRIOR
ID
です。
疑似列LEVEL
(CONNECT
BY
に関連付けられている)を使用して子をインデントします。
SELECT cardinality "Rows", lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan" FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'st1' ORDER BY id; Rows Plan ------- ---------------------------------------- SELECT STATEMENT TABLE ACCESS FULL EMPLOYEES
Rows
列のNULL
は、オプティマイザが表に統計を持っていないことを示します。表をANALYZEすると、次の内容が表示されます。
Rows Plan ------- ---------------------------------------- 16957 SELECT STATEMENT 16957 TABLE ACCESS FULL EMPLOYEES
COST
も選択できます。これは、実行計画を比較する場合や、オプティマイザが複数の中からある実行計画を選択した理由を理解する場合に便利です。
注意:
これらの単純な例は、再帰的SQLの場合には有効ではありません。