tomy_125: Note

tomy_125 の個人的なメモ

Oracle Database 実行計画を実行統計付きで取得

目次

取得方法

alter session set statistics_level = ALL;

-- 実行計画確認対象のSQL
select * from t1 where c1 = 1;

set line 200 pages 1000 tab off
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL ALLSTATS LAST'));

取得結果

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3c3yp27cag5mv, child number 0
-------------------------------------
select * from t1 where c1 = 1

Plan hash value: 1141790563

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |    26 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T1 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1

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

   2 - access("C1"=1)

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

   1 - "C1"[NUMBER,22], "T1"."C2"[NUMBER,22]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]


31 rows selected.

SQL>