実行計画の確認方法
実行計画は主に以下のような方法で取得することができます。本ページではそれぞれの設定手順を記載します。・sqlplusでauto traceを設定する
・SQLトレースを設定する
・explain plan文を実行する
・動的パフォーマンスビューから確認する(9i～)
実行計画とは
実行計画とはSQL実行時にORACLEが内部的に作成しているSQLの実行手順です。 どのような実行計画が立てられたとしてもユーザに戻る最終的な結果は全て同じになりますが実行計画によってパフォーマンスは大きく異なります。 ORACLEは統計情報等等から最もパフォーマンスがよいと予測される実行計画を立てようとはしていますが、 遅い実行計画を立てる場合もあるため遅い処理をチューニングする場合はまず最初に実行計画に問題がないか確認する必要があります。sqlplusでauto traceを設定する
sqlplusにはautotraceコマンドというSQL実行に合わせて実行計画や実行時の統計を取得する機能があり、有効にするとsqlplusの標準出力に実行計画等が表示されるようになります。 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トレースが出力され、出力されたSQLトレースをさらにtkprofというツールで変換することで人間の目で見やすい形の実行計画に変換することができます。 なお、トレースファイルは初期化パラメータuser_dump_destのパスにプロセスのPIDが付与されたファイル名で作成されます。 SQLトレースは特定のセッションまたは全てのセッションに対して設定することができます。特定のセッションで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等の表に格納することができます。explain plan文の使用方法は以下の通りです。
1 explain plan文を実行するユーザに対してPLAN_TABLE表を追加する(～9.2)
9.2までのバージョンの場合実行計画を格納するPLAN_TABLE表を作成します。
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パッケージを使って整形して表示します。
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から確認することができます。ただし、実行計画は共有プールに保存されているため解析情報はエージアウトされる場合があります。1 SQLのSQLID(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
マニュアル
・パフォーマンス・チューニング・ガイド10gリリース2（10.2）・PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス10g リリース2（10.2）
・SQLリファレンス10g リリース2（10.2）
・リファレンス10g リリース2（10.2）
折りたたみ
展開