以前にこちらの記事で、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が特定できている場合には有効な方法であると思います。
覚えておいて損はないでしょう。
プロとしてのOracleアーキテクチャ入門 ［第2版］（12c、11g、10g 対応） 図解と実例解説で学ぶ、データベースの仕組み (Oracle現場主義)
- 作者: 株式会社コーソル,渡部亮太
- 出版社/メーカー: SBクリエイティブ
- 発売日: 2015/04/24
- メディア: Kindle版
- この商品を含むブログを見る
