AUTOTRACE の新機能
SQL 文の実行計画を確認するもっとも簡単な方法として SQL*Plus の AUTOTRACE 機能があります。これはSQL 文の実行直後に実行計画と実行統計を自動的に出力する機能です。本記事では Oracle Database 23ai の AUTOTRACE に追加された機能について説明します。従来の AUTOTRACE 機能は実行計画と実行統計の出力だけでしたが、Oracle Database 23ai では「SQL Analysis Report」の結果が付与されるようになりました。SQL Analysis Report は実行された SQL 文を分析し、より高速な SQL 文への書き換えのアドバイスを出力する機能です。
準備
AUTOTRACE 機能を利用するユーザーには PLUSTRACE ロールの作成と付与が必要です。これは従来のバージョンと変わりません。管理者権限で接続し、${ORALCE_HOME}/sqlplus/admin/plustrce.sql スクリプトを実行します。
管理者による操作（一部編集）
SQL> @?/sqlplus/admin/plustrce.sql
SQL> drop role plustrace;
drop role plustrace
*
行1でエラーが発生しました。:
ORA-01919: ロール'PLUSTRACE'は存在しません。 ヘルプ:
https://docs.oracle.com/error-help/db/ora-01919/
SQL> create role plustrace;
ロールが作成されました。
SQL> grant select on v_$sesstat to plustrace;
権限付与が成功しました。
SQL> grant select on v_$statname to plustrace;
権限付与が成功しました。
SQL> grant select on v_$mystat to plustrace;
権限付与が成功しました。
SQL> grant plustrace to dba with admin option;
権限付与が成功しました。
SQL> set echo off
次に PLUSTRACE ロールを AUTOTRACE 機能を利用するユーザーに付与します。下記の例では SCOTT ユーザーに付与しています。
PLUSTRACE ロールの付与
SQL> GRANT PLUSTRACE TO SCOTT;
権限付与が成功しました。
AUTOTRACE の実行準備は整いました。下記では操作するテーブルを作成し、データを格納しています。DBMS_STATS.GATHER_TABLE_STATS プロシージャはオプティマイザ統計を取得しています。
SCOTT ユーザーの操作
SQL> CREATE TABLE data1(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(10));
表が作成されました。
SQL> INSERT INTO data1 SELECT LEVEL c1, 'data' c2 FROM DUAL CONNECT BY LEVEL <= 1000000;
1000000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'DATA1');
PL/SQLプロシージャが正常に完了しました。
AUTOTRACE の確認
AUTOTRACE を有効化するため SET AUTOTRACE ON コマンドを実行します。その後 SQL 文を実行すると実行計画や実行統計が出力されます。
AUTOTRACE の実行確認
SQL> SET AUTOTRACE ON
SQL> SELECT * FROM data1 WHERE c1=100;
C1 C2
---------- ----------
100 data
実行計画
----------------------------------------------------------
Plan hash value: 3801170568
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DATA1 | 1 | 10 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C008415 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=100)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
83 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SELECT 文によるレコード特定が一意検索（INDEX UNIQUE SCAN）で実行されていることがわかります。
SQL Analysis Report の出力
SQL 文の実行結果は同じですが、WHERE 句を書き換えてインデックスを使用しないようにしてみます。SQL Analysis Report が出力されます。
インデックスが使用されない SQL 文の実行
SQL> SELECT * FROM data1 WHERE TO_CHAR(c1)='100';
C1 C2
---------- ----------
100 data
実行計画
----------------------------------------------------------
Plan hash value: 2673507129
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 586 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DATA1 | 1 | 10 | 586 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR("C1")='100')
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SEL$1 / "DATA1"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"C1"
統計
----------------------------------------------------------
9 recursive calls
6 db block gets
2085 consistent gets
0 physical reads
1012 redo size
698 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL 文では実行計画が変わり、インデックスが使われず、テーブル全件検索（TABLE ACCESS FULL）が行われていることがわかります。パフォーマンス上の問題になる可能性があるため SQL Analysis Report の結果が追加されています。
SQL Analysis Report
1 - SEL$1 / "DATA1"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"C1"
DATA1 テーブルに対する SELECT 文で、述語（WHERE 句部分）にインデックス範囲検索（INDEX RANGE SCAN）が使用できない記述があることが示されています。
SQL Analysis Report の例
前述の例ではWHERE 句の左辺に関数が使われることでインデックスが使用されない SQL 文を実行しました。それ以外に以下のような場合にレポートが出力されます。
UNION から UNION ALL への変換
以下の SQL 文では負荷の高い UNION 文から UNION ALL 文に変更できないかというアドバイスが出力されています。
UNION ALL 文への変換
SQL> SELECT COUNT(*) FROM (SELECT * FROM data1 d1 UNION SELECT * FROM data1 d2);
COUNT(*)
----------
1000000
実行計画
----------------------------------------------------------
Plan hash value: 4196913235
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 17105 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 2000K| | 17105 (1)| 00:00:01 |
| 3 | HASH UNIQUE | | 2000K| 19M| 17105 (1)| 00:00:01 |
| 4 | UNION-ALL | | 2000K| 19M| 17105 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DATA1 | 1000K| 9765K| 583 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL| DATA1 | 1000K| 9765K| 583 (1)| 00:00:01 |
-------------------------------------------------------------------------------
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
3 - SET$1
- The query block contains UNION which may be expensive.
Consider using UNION ALL if duplicates are allowed or
uniqueness is guaranteed.
...
暗黙の型変換によるインデックスの使用不可
以下の例では VARCHAR2 型の列 C1 に対して数値を指定して検索しています（WHERE c1=100）。この結果、暗黙の型変換が発生してインデックスが使用されていません。残念ながら型変換についての記述はありませんので Predicate Information 部分から判断する必要があります。
暗黙の型変換
SQL> SELECT * FROM data2 WHERE c1=100;
C1 C2
---------------------------------------- ----------
100 data
実行計画
----------------------------------------------------------
Plan hash value: 307729429
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 653 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DATA2 | 1 | 12 | 653 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("C1")=100)
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SEL$1 / "DATA2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"C1"
...
参考
- マニュアル「Oracle Database Tuning Guide」19.3.4 SQL Analysis Report
- New SQL Analysis Report in Oracle Database 23c Free!
Author: Noriyoshi Shinoda / Date: June 3, 2024