DBMS_XPLAN.DISPLAY_CURSOR の FORMATパラメータ に色々設定して出力の違いを見る(Oracle Database)

Oracle Database の実行計画を確認するための DBMS_XPLAN.DISPLAY_CURSOR の FORMATパラメータに色々設定して出力の違いを見てみます。
彡(ﾟ)(ﾟ)

なお検証は 23ai で行いました。19cなど他バージョンだと若干違う動作をするかもしれませんが、あらかじめご了承ください。~~ヒントレポートとかは 19c だと明示指定しないと出なかったような…？~~ そんなことはありませんでした。

> 242 DBMS_XPLAN
> DISPLAY_CURSORファンクション
> https://docs.oracle.com/cd/G11854_01/arpls/DBMS_XPLAN.html#GUID-0EE333AF-E9AC-40A4-87D5-F6CF59D6C47B

# 1. テストデータのセットアップと計測用SQL

下記の SQL でテストデータをセットアップします。

```sql
-- Create Table
DROP TABLE TEST_TABLE;
CREATE TABLE TEST_TABLE (
   C1 NUMBER
  ,C2 VARCHAR2(100)
  ,C3 DATE
);

-- Dummy Data
INSERT INTO TEST_TABLE
SELECT LEVEL
     , 'TEST' || TO_CHAR(LEVEL)
     , TRUNC(SYSDATE, 'DD') + (LEVEL/24/60/60)
  FROM DUAL
CONNECT BY LEVEL <= 10000;

-- Commit
COMMIT;
```

SQL は下記を使用します。

```sql
SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */ COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;
```

各種統計の初期化のために共有プールをフラッシュして3回ほど実行しておきます。

```sql
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */ COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;

  COUNT(*)
----------
      2801

SQL> SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */ COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;

  COUNT(*)
----------
      2801

SQL> SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */ COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;

  COUNT(*)
----------
      2801

SQL>
```


# 2. FORMATパラメータに 'ALL' を設定してみる。

DBMS_XPLAN.DISPLAY_CURSOR の FORMATパラメータ に 'ALL' を設定してみます。

```sql
SET LINESIZE 300;
SET PAGESIZE 300;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'dqa7x4f5js6jq', FORMAT => 'ALL'));
```

下記が出力されます。

```sql
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  dqa7x4f5js6jq, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */
COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24

Plan hash value: 711311523

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |    13 (100)|          |
|   1 |  SORT AGGREGATE    |            |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |  2801 | 25209 |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / "TEST_TABLE"@"SEL$1"

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

   2 - filter("C3">=TRUNC(SYSDATE@!,'fmdd')+.0833333333333333333333333333
              333333333333)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=256)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  INDEX

   2 -  SEL$1 / "TEST_TABLE"@"SEL$1"
           -  FULL(TEST_TABLE)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


47 rows selected.
```

|項目|説明|
|---|---|
|SQL_ID|対象SQL の SQL_ID となります。|
|child number|対象SQL の child number となります。指定しない場合は child number = 0 の実行計画が出力されます。|
|Plan Hash Value|実行計画を一意に表現する数値となります。|
|実行計画|ツリー形式の実行計画となります。|
|Query Block Name / Object Alias|Query Block Name や Object Alias は SQL の内部情報です。ビューや副問い合わせ内のテーブルに対してヒントを適用する時等に使用します。|
|Predicate Information|SQL に適用されるフィルタ条件の意味を出力します。|
|Column Projection Information|SQL が返す列の情報を出力します。|
|Hint Report|ヒントが設定されている場合、ヒントの使用状況が出力されます。|
|Note|SQL の補足情報が出力されます。例えば SPM/SQLプロファイル/Dynamic Sampling等が利用されている場合はこのセクションでレポートされます。|


# 3. FORMATパラメータに 'ALL ALLSTATS' を設定してみる。

対象の SQL は gather_plan_statisticsヒントを設定しているので、実行統計を確認できます。
DBMS_XPLAN.DISPLAY_CURSOR の FORMATパラメータ に 'ALL ALLSTATS' を設定してみます。

```sql
SET LINESIZE 300;
SET PAGESIZE 300;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'dqa7x4f5js6jq', FORMAT => 'ALL ALLSTATS'));
```

下記が出力されます。

```sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqa7x4f5js6jq, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */
COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24

Plan hash value: 711311523

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      3 |        |       |    13 (100)|          |      3 |00:00:00.01 |     138 |
|   1 |  SORT AGGREGATE    |            |      3 |      1 |     9 |            |          |      3 |00:00:00.01 |     138 |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |      3 |   2801 | 25209 |    13   (0)| 00:00:01 |   8403 |00:00:00.01 |     138 |
---------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / "TEST_TABLE"@"SEL$1"

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

   2 - filter("C3">=TRUNC(SYSDATE@!,'fmdd')+.0833333333333333333333333333333333333333)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=256)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  INDEX

   2 -  SEL$1 / "TEST_TABLE"@"SEL$1"
           -  FULL(TEST_TABLE)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


46 rows selected.
```

|項目|説明|
|---|---|
|E-Rows|Estimate Rows の略となります。オプティマイザが予測したアクセス件数となります。|
|E-Time|Estimate Time の略となります。オプティマイザが予測したSQLの実行時間となります。|
|A-Rows|Actual Rows統計となります。SQL が該当Id部分で実際にアクセスした行数となります。|
|A-Time|Actual Time統計となります。SQL が該当Id部分で経過した時間となります。|

この Estimate と Actual の差はSQLチューニングを進める上で重要な手がかりとなります。


# 4. FORMATパラメータに 'ALL ALLSTATS LAST' を設定してみる。

DBMS_XPLAN.DISPLAY_CURSOR の FORMATパラメータ に 'ALL ALLSTATS' を設定してみます。
LAST を指定した場合、実行統計は最後に実行された SQL の統計が出力されます。

```sql
SET LINESIZE 300;
SET PAGESIZE 300;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'dqa7x4f5js6jq', FORMAT => 'ALL ALLSTATS LAST'));
```

```sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqa7x4f5js6jq, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */
COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24

Plan hash value: 711311523

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |       |    13 (100)|          |      1 |00:00:00.01 |      46 |
|   1 |  SORT AGGREGATE    |            |      1 |      1 |     9 |            |          |      1 |00:00:00.01 |      46 |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |      1 |   2801 | 25209 |    13   (0)| 00:00:01 |   2801 |00:00:00.01 |      46 |
---------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / "TEST_TABLE"@"SEL$1"

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

   2 - filter("C3">=TRUNC(SYSDATE@!,'fmdd')+.0833333333333333333333333333333333333333)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=256)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  INDEX

   2 -  SEL$1 / "TEST_TABLE"@"SEL$1"
           -  FULL(TEST_TABLE)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


46 rows selected.
```

出力される統計の種類に変化は無いですが、A-Rows統計が1回分になっているのが解ります。


# 5. FORMATパラメータに 'ALL ALLSTATS LAST ADVANCED' を設定してみる。

DBMS_XPLAN.DISPLAY_CURSOR の FORMATパラメータ に 'ALL ALLSTATS LAST ADVANCED' を設定してみます。
ADVANCED を設定すると SQL に関するより詳しい情報が出力されます。詳細は下記ドキュメント等を参照してください。

> Oracle Support Document 2342863.1 How to Display and Explore Outlines Data(Hints) for a SQL
> https://support.oracle.com/epmos/faces/DocumentDisplay?id=2342863.1

下記を実行してみます。

```sql
SET LINESIZE 300;
SET PAGESIZE 300;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'dqa7x4f5js6jq', FORMAT => 'ALL ALLSTATS LAST ADVANCED'));
```

下記が出力されます。

```sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqa7x4f5js6jq, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */
COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24

Plan hash value: 711311523

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |       |    13 (100)|          |      1 |00:00:00.01 |      46 |
|   1 |  SORT AGGREGATE    |            |      1 |      1 |     9 |            |          |      1 |00:00:00.01 |      46 |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |      1 |   2801 | 25209 |    13   (0)| 00:00:01 |   2801 |00:00:00.01 |      46 |
---------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / "TEST_TABLE"@"SEL$1"

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TEST_TABLE"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - filter("C3">=TRUNC(SYSDATE@!,'fmdd')+.0833333333333333333333333333333333333333)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=256)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  INDEX

   2 -  SEL$1 / "TEST_TABLE"@"SEL$1"
           -  FULL(TEST_TABLE)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Query Block Registry:
---------------------

  SEL$1 (PARSER) [FINAL]



66 rows selected.
```
|項目|説明|
|---|---|
|Outline Data|出力されている実行計画を再現するためのヒント群となります。|

# 6. まとめ

DBMS_XPLAN.DISPLAY_CURSOR で色々確認できます。DBMS_SQLTUNE.REPORT_SQL_MONITOR と組み合わせると SQLチューニングをする上で有効な情報を色々取得できます。

皆さんぜひ活用してください。www.oracle.com で静かに眠っている下記資料も併せて参照してくださいね……。
彡(^)(^)

> 【A-1】オラクル・コンサルが語る！ SQLチューニングに必要な考え方と最新テクニック資料
> https://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/A-1.pdf

# Appendix. 追記(19c, 19.3 で検証)

(2025/4/2追記)19c, 19.3 で検証しましたが 19c でもヒントレポートは ALL で出力されました。最初のは勘違い
彡(^)(^)

```sql
SQL> SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */ COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24;

  COUNT(*)
----------
      2801

SQL> SET LINESIZE 300;
SQL> SET PAGESIZE 300;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'dqa7x4f5js6jq', FORMAT => 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqa7x4f5js6jq, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics MONITOR FULL(TEST_TABLE) INDEX */
COUNT(*) FROM TEST_TABLE WHERE C3 >= TRUNC(SYSDATE, 'DD') + 2/24

Plan hash value: 711311523

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |    13 (100)|          |
|   1 |  SORT AGGREGATE    |            |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |  2801 | 25209 |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TEST_TABLE@SEL$1

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

   2 - filter("C3">=TRUNC(SYSDATE@!,'fmdd')+.0833333333333333333333333333
              333333333333)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  INDEX

   2 -  SEL$1 / TEST_TABLE@SEL$1
           -  FULL(TEST_TABLE)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


47 rows selected.

SQL> SELECT * FROM V$VERSION;

BANNER                                                                           BANNER_FULL
-------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY                                                                        CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
                                                                                 Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                    0


SQL>
```