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

Last updated at 2025-04-02Posted at 2025-03-31

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 でテストデータをセットアップします。

-- 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 は下記を使用します。

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

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

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' を設定してみます。

SET LINESIZE 300;
SET PAGESIZE 300;
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=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' を設定してみます。

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

下記が出力されます。

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 の統計が出力されます。

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

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

下記を実行してみます。

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

下記が出力されます。

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> 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>

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

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

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

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

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

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

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

6. まとめ

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

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