Oracle [12.2]SQLトレースの出力内容

概要

12cR2より、SQLトレースの実行統計にstartsの情報が出力されるように追加修正されているようです。
starts：実行計画内の各ステップでの実行回数を表示しています。
           DBMS_XPLAN.DISPLAY_CURSORで実行統計を表示した場合にも出力されます。

出力内容

12.1

SQL ID: fb2bdb6qyahwn Plan Hash: 615168685

::

select *
from
emp e, dept d where e.deptno = d.deptno

 :

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0         36          0          0
Execute      1     0.00       0.00          0          0          0          0
Fetch        2     0.00       0.00          0         15          0         14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4     0.00       0.00          0         51          0         14

 :

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1

 :

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
        14         14         14 HASH JOIN (cr=15 pr=0 pw=0 time=140 us cost=6 size=1638 card=14)
         4          4          4  TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=26 us cost=3 size=120 card=4)
        14         14         14  TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=10 us cost=3 size=1218 card=14)

12.2

SQL ID: fb2bdb6qyahwn Plan Hash: 844388907

 :

select *
from
emp e, dept d where e.deptno = d.deptno

 :

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.00       0.00          0          0          0          0
Fetch        2     0.00       0.00          0         11          0         14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4     0.00       0.00          0         11          0         14

 :

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 121
Number of plan statistics captured: 1

 :

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
        14         14         14 MERGE JOIN (cr=11 pr=0 pw=0 time=125 us starts=1 cost=6 size=812 card=14)
         4          4          4  TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=36 us starts=1 cost=2 size=80 card=4)
         4          4          4   INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=18 us starts=1 cost=1 size=0 card=4)(object id 74419)
        14         14         14  SORT JOIN (cr=7 pr=0 pw=0 time=80 us starts=4 cost=4 size=532 card=14)
        14         14         14   TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=51 us starts=1 cost=3 size=532 card=14)

 :

SQL_ID fb2bdb6qyahwn, child number 0
-------------------------------------
select * from emp e, dept d where e.deptno = d.deptno

 :

Plan hash value: 844388907

 :

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |     A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |     14 |00:00:00.01 |      11 |
|   1 |  MERGE JOIN                  |         |      1 |     14 |     14 |00:00:00.01 |      11 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       4 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |
|*  4 |   SORT JOIN                  |         |      4 |     14 |     14 |00:00:00.01 |       7 |
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------

参考資料

Oracleの実行計画を読んでみよう！ #dbts2017

https://docs.oracle.com/cd/E82638_01/TGSQL/generating-and-displaying-execution-plans.htm#GUID-60E30B1C-342B-4D71-B154-C26623D6A3B1

Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2) 6 実行計画の生成と表示