実行計画の確認方法

実行計画は主に以下のような方法で取得することができます。本ページではそれぞれの設定手順を記載します。

・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.sql 
2 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.sql 
4 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.sql 
2 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 test
2 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)
★ORACLE案件承ります