[Oracle]SQL*Plusで実行計画を取得する（SET AUTOTRACE）

実行計画の確認にはいくつかを方法がありますが、ここでは最も手軽に実施できるSQL*Plusでの実行計画の取得の方法について解説します。

実行計画を取得するための設定を行う

私がよく使っている設定をご紹介します。とりあえずこの設定を使っていただければ実行計画を取得できるようになります。

SQL> set lines 1000
SQL> set pages 1000
SQL> set timing on
SQL> set autotrace traceonly

以上です。

この設定を行った状態でSQLを実行すると以下のようになります。

SQL> select * from dept
  2  /

レコードが選択されませんでした。

経過: 00:00:00.02

実行計画
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    30 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     1 |    30 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        449  bytes sent via SQL*Net to client
        369  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

実行計画と統計情報が出力されていることがわかります。

上記手順を実施しているときにエラーが出る場合があります。その場合は後述の「手順の実施時にエラーが出る場合」を参照してください。

これだけだとよくわからないと思いますので、各コマンドについて解説します。

表示幅を広げる

linesizeで結果行の幅を設定します。実行計画の出力内容は表を含むため、途中で折り返されるとかなり見にくいです。そこでとりあえず折り返されないような大きな値を設定しています。

正式にはlinesizeですが、linesでも設定できます。タイプ数が少ない方が楽なのでlinesを使用しています。

set lines 1000

1ページの行数を増やす

実行計画の出力の途中で改ページすると見づらいため、1ページの行数も増やしています。こちらもとりあえずで増やしていますが、普通に使う分には問題は起こらないと思いますが、あまりにも長いSQLの実行計画を確認するときに、改ページされるようであれば値を増やしてください。

先ほどと同様にpagesizeが正式な設定ですが、pagesでも設定できます。

set pages 1000

タイミング統計をONにする

タイミング統計をONにすることで、実行時間を10ミリ秒単位で出力できるようになります。

経過: 00:00:00.02

実行時間もチューニングの重要な情報源となりますし、出力結果が邪魔になることもないので、ONにしておきましょう。

set timing on

実行計画の設定を行う

実行計画の出力設定は、autotraceで行います。出力できる内容は、

• 実行結果
• 実行計画
• 統計情報

の3つがありますが、実行結果を必要としない場合はtraceonlyを設定し、実行結果を確認したい場合はoffに切り替えるのがおすすめです。

設定	説明
set autotrace on explain	実行結果と実行計画を表示する
set autotrace on statistics	実行結果と統計情報を表示し、実行計画をは表示しない
set autotrace on	実行結果、実行計画、統計情報を表示する
set autotrace traceonly	実行計画と統計情報を表示し、実行結果は表示しない
set autotrace off	autotraceの設定をOFFにする

set autotrace traceonly

手順の実行時にエラーが出る場合

これから説明する手順を実行したときに、以下のようなエラーが出る場合があります。

SQL> set autotrace traceonly
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

このエラーが出る場合は、以下の手順を実行してPLUSTRACEロールを有効にしてください。

SYSDBAでCDBにログインする

ロールを追加するため、SYSDBAでCDBにログインします。

ORACLE_SID=ORCLCDB sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 29 15:44:05 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 

PDBに切り替える

ロールを作成するPDBに切り替えます。ここではpdb1というPDBに切り替えています。

SQL> alter session set container=pdb1;

Session altered.

SQL> 

ロールを作成するPDBというのは、実行計画を取得するPDBのことです。

PLUSTRACEロールを作成するSQLファイルを実行する

OracleDatabaseに用意されているSQLファイルを実行します。SQLファイルのファイル名はplustrace.sqlではなくplustrce.sqlですので、打ち間違いに注意してください（aがない）。

SQL> @?/sqlplus/admin/plustrce.sql
SQL> 
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

PLUSTRACEロールをユーザーに付与する

作成したPLUSTRACEロールをユーザーに付与します。ここでは、user01ユーザーに設定しています。

SQL> grant plustrace to user01;

Grant succeeded.

SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

再度、該当ユーザーでログインしてautotraceの設定を行ってください。