実行計画の確認方法
実行計画は主に以下のような方法で取得することができます。本ページではそれぞれの設定手順を記載します。・sqlplusでauto traceを設定する
・SQLトレースを設定する
・explain plan文を実行する
・動的パフォーマンスビューから確認する(9i～)
・statistics_level=all＋dbms_xplan.display_cursorで実行計画＋行ソースレベルのSQL統計を確認する(9i～)
実行計画とは
実行計画とは指示されたSQLを処理するにあたっての内部的な方法や順番です。 どのような実行計画が立てられたとしても最終的な結果は全て同じになりますがパフォーマンスは実行計画によって大きく変化する場合があります。 実行計画はオプティマイザと呼ばれるエンジンが作成していますが、オプティマイザにはCBO(コストベースオプティマイザ)、RBO(ルールベースオプティマイザ)の2種類があります。 10g以降RBOが非サポートとなったためこれ以降のバージョンでは基本的にユーザが実行するSQLはCBOが実行計画を作成しています。 CBOは統計情報等を加味しつつ想定できる様々な実行計画のコストをそれぞれ計算し、最もコストの低い実行計画を実際に実行される実行計画として決定しますが、 CBOでもRBOでも遅い実行計画を立てる場合があるためSQLをチューニングする場合はまず実行計画が適切であるかを確認する必要があります。 本ページでは実行計画の確認の仕方について記述します。sqlplusでauto traceを設定する
sqlplusにはautotraceコマンドというSQL実行時に実行計画や実行時の統計を取得する機能があり、有効にするとsqlplusの標準出力に実行計画等が表示されるようになります。 autotraceは設定による負荷が少なく統計値もあわせて取得できるためチューニング時に利用される手段ですが以下の点について注意する必要があります。・ダイレクト処理やパラレル処理に対しては実行計画を取得することができない
・ORACLEのセッションが1セッション増える(統計値等を取得するシャドウセッションが張られる)
・ハードパースを伴うSQL実行だった場合統計値はパースの再帰SQLの統計値も合算される
autotraceコマンドの使用方法は以下の通りです。
1 plustrce.sqlの実行
plustrce.sqlというスクリプトを実行してauto traceの実行に必要なplustraceロールを作成します。
SQL> CONNECT / AS SYSDBA SQL> @?/sqlplus/admin/plustrce.sql2 auto trace機能を使うユーザに対して権限を追加する
auto trace機能を使うにはPLUSTRACEロールが必要になるため付与します。
SQL> GRANT plustrace TO scott;3 auto trace機能を使うユーザに対してPLAN_TABLE表を追加する(～9.2)
9.2までのバージョンの場合実行計画を格納するPLAN_TABLE表を作成します。
SQL> CONNECT scott/tiger SQL> @?/rdbms/admin/utlxplan.sql4 auto traceを設定する
set autotraceコマンドを実行するとsql実行時に実行計画が表示されます。autotraceには以下のようなオプションがあります。
SQLを実際に実行し、実行計画・統計・実行結果を出力する
SET AUTOTRACE ON
SQLを実際に実行するが、結果は表示せずに実行計画・統計は表示する
SET AUTOTRACE TRACEONLY
SQLを実際に実行するが、実行計画は表示せず実行統計と実行結果を表示する
SET AUTOTRACE ON STATISTICS
クエリ(SELECT)は実行せず実行計画のみ表示する（DMLは実行されます）
SET AUTOTRACE TRACEONLY EXPLAIN
トレース出力を無効にする
SET AUTOTRACE OFF
出力例
SQL> set autotrace on
SQL> select emp.empno,emp.ename,emp.deptno,dept.dname from emp ,dept where emp.deptno=dept.deptno;
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7902 FORD 20 RESEARCH
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
7698 BLAKE 30 SALES
12行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 12 | 312 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 12 | 156 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 12 | 156 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1038 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
SQLトレースを設定する
SQLトレースはSQLを実行したプロセスのトレースファイルにSQLの実行計画や各種統計値等を出力する機能です。 設定範囲としては特定セッションまたはシステム(全セッション)に対して設定することができ、 ファイルは初期化パラメータuser_dump_destのパスにプロセスのPIDが付与されたファイル名で作成されます。 また、出力されたSQLトレース見づらいため通常tkprofというユーティリティで変換することで見やすい形のトレースファイルに変換することができます。 SQLトレースはautotraceと比較して実行計画の各オペレーション毎の処理時間やアクセスブロック数が確認できたり、 設定によっては実行中の待機イベントもあわせて出力できるといった大きな優位性がありますが以下の点に注意が必要です。・大量のトレースファイルが作成されパフォーマンスが劣化する
⇒SQLの実行回数が多い、フェッチ件数が多い等のケースの場合大量のトレースが出力されることで処理時間もそれなりに劣化します。 劣化度合いはケースバイケースですが、1セッションのみに設定した場合は該当セッションの性能は1～3割程度ダウンする可能性が高いです。 トレース出力によるDISK枯渇も注意しなければなりません
特定のセッションでSQLトレースを有効にする方法
・自身のセッションでSQLトレースを有効にする
有効化 SQL> alter session set sql_trace=true; 無効化 SQL> alter session set sql_trace=false;・指定したセッションでSQLトレースを有効にする
SQL> conn / as sysdba ※dbms_systemはマニュアルに記載のないPL/SQLパッケージです 有効化 SQL> exec dbms_system.set_sql_trace_in_session(＜セッションID＞,＜シリアル番号＞, TRUE); 無効化 SQL> exec dbms_system.set_sql_trace_in_session(＜セッションID＞,＜シリアル番号＞, FALSE);以下は10g以降で使用可能です。DBMS_MONITORはバインド変数や待機イベントの取得有無も設定可能です。
有効化 SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id=>＜セッションID＞,serial_num=>＜シリアル番号＞,waits=>TRUE,binds=>FALSE); 無効化 SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id=>＜セッションID＞,serial_num=>＜シリアル番号＞);なお、各セッションのセッションIDとシリアル番号はv$sessionより確認します。
全てのセッションでSQLトレースを有効にする方法
・sql_traceパラメータを設定する(10g以降非推奨)
有効化 SQL> alter system set sql_trace=true; 無効化 SQL> alter system set sql_trace=false;・DBMS_MONITORパッケージを使用する(10g以降で可能)
有効化 SQL> exec DBMS_MONITOR.DATABASE_TRACE_ENABLE; 無効化 SQL> exec DBMS_MONITOR.DATABASE_TRACE_DISABLE;SQLトレースの設定、出力例
SQL> alter session set sql_trace=true;
セッションが変更されました。
SQL> select emp.empno,emp.ename,emp.deptno,dept.dname from emp ,dept where emp.deptno=dept.deptno;
EMPNO ENAME DEPTNO DNAME
---------- ------------------------------ ---------- ------------------------------------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14行が選択されました。
SQL> alter session set sql_trace=false;
セッションが変更されました。
[ora102@linux1 udump]$ tkprof /app/oracle/admin/ORCL102/udump/orcl102_ora_20127.trc /app/oracle/admin/ORCL102/udump/orcl102_ora_20127.trc.log SYS=NO
TKPROF: Release 10.2.0.4.0 - Production on 火 4月 26 14:40:43 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
[ora102@linux1 udump]$ cat /app/oracle/admin/ORCL102/udump/orcl102_ora_20127.trc.log
TKPROF: Release 10.2.0.4.0 - Production on 火 4月 26 14:40:43 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: /app/oracle/admin/ORCL102/udump/orcl102_ora_20127.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select emp.empno,emp.ename,emp.deptno,dept.dname
from
emp ,dept where emp.deptno=dept.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.11 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 8 11 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.13 8 11 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
14 MERGE JOIN (cr=11 pr=8 pw=0 time=20510 us)
4 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=2 pw=0 time=10869 us)
4 INDEX FULL SCAN PK_DEPT (cr=2 pr=1 pw=0 time=10568 us)(object id 51147)
14 SORT JOIN (cr=7 pr=6 pw=0 time=9747 us)
14 TABLE ACCESS FULL EMP (cr=7 pr=6 pw=0 time=9677 us)
********************************************************************************
alter session set sql_trace=false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 54
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.11 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 8 11 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.13 8 11 0 14
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 17 0.01 0.01 0 0 0 0
Execute 140 0.04 0.06 0 0 0 0
Fetch 162 0.00 0.03 17 439 0 787
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 319 0.07 0.11 17 439 0 787
Misses in library cache during parse: 14
Misses in library cache during execute: 14
2 user SQL statements in session.
140 internal SQL statements in session.
142 SQL statements in session.
********************************************************************************
Trace file: /app/oracle/admin/ORCL102/udump/orcl102_ora_20127.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
140 internal SQL statements in trace file.
142 SQL statements in trace file.
16 unique SQL statements in trace file.
1241 lines in trace file.
12 elapsed seconds in trace file.
explain plan文を実行する
explain planは実行することで指定したSQL文の実行計画をplan_table表に格納し、plan_tableから実行計画を確認する方法です。この方法は実際にSQLを実行をしないため統計を確認することができませんが、以下の利点があります。
・バインド変数値が含まれるSQLであってもバインド変数を設定しなくても実行計画が取得できる(ただし、autotraceも同様だがバインドピーク機能により実行計画が変わるような場合実際に実行した時と異なる実行計画が表示される場合がある)
・DML文であっても実行せずに実行計画を取得できる
explain plan文の使用方法は以下の通りです。
1 explain plan文を実行するユーザに対してPLAN_TABLE表を追加する(～9.2)
9.2までのバージョンの場合実行計画を格納するPLAN_TABLE表を作成します。10g以降は不要です。
SQL> CONNECT scott/tiger SQL> @?/rdbms/admin/utlxplan.sql2 explain plan文を実行する
SQL> explain plan for select * from emp; 解析されました。3 plan_tableから実行計画を取り出す
plan_tableに格納されたデータはそのまま表示しても見づらいため通常DBMS_XPLAN.DISPLAYを使って整形して表示します。
SQL> select * from table(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 468 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 12 | 468 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- 8行が選択されました。
動的パフォーマンスビューから確認する(9i～)
9i以降のバージョンでは解析された実行計画をV$SQL_PLANから確認することができ、 dbms_xplan.display_cursorでもこれとほぼ同じ結果を整形された形で取得することもできます。 ただし、実行計画は共有プールに保存されているため該当のカーソル情報がエージアウトされていた場合は取得できません。1 SQLのSQL_ID(10g～)またはSQL_ADDRESS、SQL_HASHを確認する
SQL> column sql_text format a50 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'select% from test%'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- -------------------------------------------------- 1CADE4EC 171085072 select * from test2 V$SQL_PLANを確認する
SQL> column operation format a30
SQL> column object_name format a20
SQL> SELECT A.ID, LPAD(' ',2*(DEPTH-1)) || A.OPERATION || nvl2(OPTIMIZER,'(' || OPTIMIZER || ')',NULL) || nvl2(A.OPTIONS,' ' ||
A.OPTIONS ,NULL) OPERATION, A.OBJECT_NAME,A.BYTES,A.COST,A.CPU_COST,A.IO_COST,A.CARDINALITY FROM V$SQL_PLAN A
WHERE A.ADDRESS='1CADE4EC' AND A.HASH_VALUE='171085072' AND A.CHILD_NUMBER=0 ORDER BY ID;
ID OPERATION OBJECT_NAME BYTES COST CPU_COST IO_COST CARDINALITY
---------- ------------------------------ -------------------- ---------- ---------- ---------- ---------- -----------
0 SELECT STATEMENT(CHOOSE) 2
1 TABLE ACCESS(ANALYZED) FULL TEST 3 2 2 1
statistics_level=all＋dbms_xplan.display_cursorで確認する(9i～)
9i以降、初期化パラメータstatistics_level=allを設定することで実行計画の各Id(行ソース)レベルでの実行統計を確認することができます。 以下の例はdbms_xplanを使用していますがV$SQL_PLAN_STATISTICS_ALLからも同等の情報が確認できます。
1 statistics_level=allを設定する
statistics_level=allが設定されたセッションは大幅に性能劣化します。インスタンス全体に対してstatistics_level=allを設定するのは避けたほうが無難です。
逆に以下のようにセッション単位で1つのSQLだけ実行するような場合は当該セッション以外への影響はほぼありません(少し共有プールを使うくらい)ので本番環境でも使える有用な機能になっています。
SQL> alter session set statistics_level=all; セッションが変更されました。2 SQLを実行する
3 dbms_xplan.displayで表示する
display_cursorのオプションの意味
・第一、第二引数をnullに設定することで直前に実行されたSQLを指定したことになります。・allstatsはデフォルトで表示される情報に加え、実際に実行された時の実行時間、バッファ読み込み数、物理読み込み数、使用メモリ等が表示されるようになります。 lastは最後に実行された1回分の統計のみ表示します。
実行計画情報の意味
・Starts・・・該当実行計画IDが実行された回数。nested loopでは駆動表のアクセス部分のstartsは結合件数に応じて増加します。以下の例ではA_IX01の抽出行数が999k行のマージ結合であったため sort joinが999kになっています。この値を見ることでどこで行が絞れているのか、増大しているのかが確認できます。・E-Rows・・・Estimate-Rows(見積もり行数)。この値は統計情報から導かれた予測行数なので正しくない場合があります。以下のケースでも最終的な抽出行数は316kであり323kと乖離しています。
・A-Rows・・・Actual-Rows(実際の行数)。この値を見ることで各Idで何行処理されたのかを確認することができます。
・A-Time・・・Actual-Time(実時間)。各Idで実際に要した時間。時間は下位にあたる実行計画からの積算となっているため以下の例ではId=1は0.53秒、Id=2は0.90秒、Id=3は1.71秒、Id=4は0.91秒、Id=5は0.31秒で合計処理時間が4.36秒となります。
・Buffers・・・バッファ読み込みブロック数。v$sqlのbuffer_getsと同じ意味合いです。これも下位にあたる実行計画からの積算となっています。
・Reads・・・物理読み込みブロック数。v$sqlのdisc_readsと同じ意味合いです。これも下位にあたる実行計画からの積算となっています。
・OMem・・・OPTIMAL_Mem。メモリー内で操作を完了するのに必要と予測されたメモリサイズ
・1Mem・・・1Pass_Mem。単一パスで操作を完了するのに必要と予測されたメモリサイズ。
・Used-Mem・・・実際に使用したメモリーサイズ。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g7spmx46cjst8, child number 0
-------------------------------------
select count(*) from a,b where a.col1=b.col1 group by b.col1
Plan hash value: 3581227421
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 316K|00:00:04.36 | 25243 | 4270 | | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 323K| 316K|00:00:04.36 | 25243 | 4270 | | | |
| 2 | MERGE JOIN | | 1 | 1545K| 2496K|00:00:03.83 | 25243 | 4270 | | | |
| 3 | INDEX FULL SCAN | A_IX01 | 1 | 999K| 999K|00:00:01.71 | 23782 | 2823 | | | |
|* 4 | SORT JOIN | | 999K| 500K| 2496K|00:00:01.22 | 1461 | 1447 | 10M| 1282K| 9M (0)|
| 5 | INDEX FAST FULL SCAN| B_IX01 | 1 | 500K| 500K|00:00:00.31 | 1461 | 1447 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1")
filter("A"."COL1"="B"."COL1")
マニュアル
・パフォーマンス・チューニング・ガイド10gリリース2（10.2）・PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス10g リリース2（10.2）
・SQLリファレンス10g リリース2（10.2）
・リファレンス10g リリース2（10.2）
折りたたみ
展開