SQL Adviser Report 機能を試す（Oracle Database 23ai）

# 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: 管理者による操作（一部編集）
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 ユーザーに付与しています。

```SQL: PLUSTRACE ロールの付与
SQL> GRANT PLUSTRACE TO SCOTT;

権限付与が成功しました。
```

AUTOTRACE の実行準備は整いました。下記では操作するテーブルを作成し、データを格納しています。DBMS_STATS.GATHER_TABLE_STATS プロシージャはオプティマイザ統計を取得しています。


```SQL: 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 文を実行すると実行計画や実行統計が出力されます。

```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 文の実行
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 文に変更できないかというアドバイスが出力されています。

```SQL: 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: 暗黙の型変換
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](https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/influencing-the-optimizer.html#GUID-A2CEDBF8-948F-4C84-8063-DF8A13D476AD
)
- [New SQL Analysis Report in Oracle Database 23c Free!](https://blogs.oracle.com/optimizer/post/sql-analysis-report-in-23c-free)

Author: Noriyoshi Shinoda / Date: June 3, 2024