(Oracle)AUTOTRACEによるSQL実行計画の取得

スポンサーリンク

以前にこちらの記事で、EXPLAIN PLANによる統計情報の取得方法をまとめました。
quotto.hatenablog.com


今回はもう一つの実行計画取得方法である、AUTOTRACEの利用方法についてまとめます。

なおAUTOTRACEはSQL*PlusおよびSQL Developerの機能であるため、それ以外のOracleクライアントからは利用することができません。

サンプルデータ

前回同様、emp表とdept表を利用します。

SQL> select * from emp;

EMPNO ENAME      JOB          MGR  SALARY DEPTNO
----- ---------- ---------- ------ ------ ------- 
7369  SMITH      CLERK        7902    800 20
7499  ALLEN      SALES        7698   1600 30
7521  WARD       SALES        7698   1250 30
7566  JONES      MANAG        7839   2970 20
7654  MARTIN     SAL          7698   1250 30
7698  BLAKE      MANAG        7839   2850 30
7782  CLARK      MANAG        7839   2450 10
7839  KING       PRESIDENT           5000 10
7844  TURNER     SAL          7698   1500 30
7900  JAMES      CLERK        7698    950 30
7902  FORD       ANALY        7566   3000 20
7934  MILLER     CLE          7782   1300 10

12 rows selected.

SQL> select * from dept;

DEPTNO DNAME      LOC
------ ---------- ---------- 
10     ACCOUNTING NEW YORK
20     RESEARCH   DALLAS
30     SALE       CHICAGO
40     OPERATIONS BOSTON

emp表のdeptnoにはdept表のdeptnoが入ります。

AUTOTRACEによる実行計画取得手順

1.PLUSTRACEロールの有効化

autotraceを利用するには、PLUSTRACEロールが作成されている必要があります。

ロール作成用のスクリプトとして「$ORACLE_HOME/sqlplus/admin/plustrace.sql」が用意されているため、DBA権限を持つユーザでこのスクリプトを実行しておきます。

SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off

-- 作成したら一般ユーザにロールを付与する
SQL> grant PLUSTRACE to scott;

Grant succeeded.

これで下準備はOKです。

2.AUTTRACEを有効にしてSQLを実行する

AUTOTRACEを使った実行計画の取得はEXPLAIN PLANよりも簡単です。
AUTOTRACEオプションをonにして、SQLを流すだけです。

SQL> show autotrace;
autotrace OFF
SQL> set autotrace on;
SQL> show autotrace;
autotrace ON EXPLAIN STATISTICS
SQL> select e.ename from emp e,dept d where e.deptno=d.deptno
select e.ename from emp e,dept d where e.deptno=d.deptno;

ENAME
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD

ENAME
--------------------
MILLER

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3691029676

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    12 |   156 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |          |    12 |   156 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP      |    12 |   120 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPT_IDX |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"="D"."DEPTNO")


Statistics
----------------------------------------------------------
     67  recursive calls
      0  db block gets
    125  consistent gets
      9  physical reads
      0  redo size
    712  bytes sent via SQL*Net to client
    551  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     13  sorts (memory)
      0  sorts (disk)
     12  rows processed

ここでEXPLAIN PLANとの違いがわかったでしょうか。

EXPLAIN PLANでは事前に指定したSQL文の実行計画だけを出力していたのに対して、AUTOTRACEを利用した場合は実際に表をフェッチした結果・実行計画・実行統計の3セットが出力されます。

なお、実行統計にはこのSQLを実行した際のソート回数・読み込みブロックサイズなどの情報が出力されます。詳しくはこちらのサイトに載っていますのでご参照下さい。
SQL*Plusで実行計画を取得する - オラクル・Oracle SQL*Plus リファレンス

AUTOTRACEのオプション

AUTOTRACEがonになるとそのセッション内でSQLを実行する度にフェッチの結果・実行計画・実行統計が出力されるようになります。

set autotraceコマンドにオプションを付けることで、出力情報を限定することができます。

フェッチの結果は表示しない

実行計画・実行統計のみを出力する場合はtraceonlyオプションを付与します。

SQL> set autotrace traceonly 
SQL> select e.ename from emp e,dept d where e.deptno=d.deptno

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3691029676

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    12 |   156 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |          |    12 |   156 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP      |    12 |   120 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPT_IDX |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"="D"."DEPTNO")


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     12  consistent gets
      0  physical reads
      0  redo size
    712  bytes sent via SQL*Net to client
    551  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     12  rows processed

実行計画だけを表示する

実行計画だけを表示するにはtraceonly explainオプションを指定します。

SQL> set autotrace traceonly explain;
SQL> select e.ename from emp e,dept d where e.deptno=d.deptno

Execution Plan
----------------------------------------------------------
Plan hash value: 3691029676

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    12 |   156 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |          |    12 |   156 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP      |    12 |   120 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPT_IDX |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"="D"."DEPTNO")

実行統計だけを表示する

実行統計だけを表示する場合はstatisticsオプションを指定します。

SQL> set autotrace traceonly statistics;
SQL> select e.ename from emp e,dept d where e.deptno=d.deptno

12 rows selected.


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
     12  consistent gets
      0  physical reads
      0  redo size
    712  bytes sent via SQL*Net to client
    551  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     12  rows processed

AUTOTRACEを無効にする

有効にしたAUTOTRACEを無効にするにはOFFを指定します。デフォルトはこの状態です。

SQL> set autotrace off;

注意点

AUTOTRACEにおける実行計画についても、EXPLAIN PLAN同様に”想定”の情報です。実際に表をフェッチして使われたものではありません。

また、AUTOTRACEによる実行計画の取得はEXPLAIN PLANやトレースファイル解析に比べて簡単ですが、有効となるのは単一のセッション内だけです。

インスタンス全体あるいは特定のユーザを対象に実行計画を取得するには、トレースファイルへ出力された結果の解析が必要です。


とは言え、AUTOTRACEによるトレース解析は解析したい(時間がかかっている・問題になっている)SQLが特定できている場合には有効な方法であると思います。

覚えておいて損はないでしょう。