| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
この章の内容は次のとおりです。
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の場合には有効ではありません。
