꿈꾸는 개발자, DBA 커뮤니티 구루비

Introduction

dbms_xplan 패키지를 통해 Plan_Table에 저장된 실행계획을 좀 더 쉽게 출력 할 수 있고, 10g부터는 실행계획은 물론 Row Source별
수행 통계까지 출력 가능함

(1) 예상 실행계획 출력

첫번째 인자에는 실행 계획이 저장된 Plan_Table 명을 입력하고, 두번째 인자가 NULL일 경우 가장 마지막 explain_plan을 보여주며
세번째 인자를 통해 5(Basic, Typical, All , Outline, Advanced)가지 포맷 옵션을 선택할 수 있음


SQL> select plan_table_output
       from table (dbms_xplan.display('plan_table',null,'all'));

(2) 캐싱된 커서의 실제 실행계획 출력

1)커서란 하드파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree,실행 계획 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말함
2)오라클은 라이브러리 캐시에 캐싱되어 있는 수행 통계를 볼 수 있도록 v$sql 뷰를 제공함
3)활용도가 높은 뷰는 v$sql_plan과 v$sql_plan_statistics와 두개를 합친 v$sql_plan_statistics_all 임
4)dbms_xplan.display_cursor함수를 이용해 조회 가능


SQL> select *
       from table (dbms_xplan.display_cursor('sql_id',child_no,'format'));

참고로 ms_xplan.display_awr 함수를 이용하면 AWR에 수집된 과거 수행SQL에 대해서도 분석 작업을 할 수 있음

(3) 캐싱된 커서의 Row Source별 수행 통계 출력

1)수행 통계 출력 방법

/*\+ gather_plan_statistics \*/힌트를 사용 (set serveroutput off)
시스템 또는 세션 레벨에서 statisticts_level 파라미터를 All로 설정-->운영DB에서는 삼가해야함
\_rowsource_execution_statisticts 파라미터를 True로 설정
SQL 트레이스 수행
v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용하여 조회
dbms_xplan.display_cursor함수를 이용해 조회 가능

2)항목 설명

E-Rows는 SQL을 수행하기 전 옵티마이저가 각 Row Source별 예상했던 로우 수로서 v$sql_plan에서 읽어온 값
A-Rows는 실제 수행 시 읽었던 로우 수로서 v$sql_plan_statistics에서 읽어온 값
기본적으로 누적값을 보여주며, 아래 처럼 Format에 last를 추가해주면 마지막 수행했을 때의 일량을 보여줌


SQL > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aduuuwpa8f64v', 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  aduuuwpa8f64v, child number 0
-------------------------------------
select *  from scott.emp e, scott.dept d where d.deptno = e.deptno   and e.sal >= 1000

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-
----------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |     24 |     24 |00:00:00.01 |      16 |   825K|   825K|  679K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| EMP  |      1 |     24 |     24 |00:00:00.01 |       9 |       |       |          |
----------------------------------------------------------------------------------------------------

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

   1 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("E"."SAL">=1000)

(4) 실습 : dbms_xplan.display


SQL> explain plan set statement_id = 'manon94'
  2  for
  3  SELECT *
  4    FROM emp e, dept d
  5   WHERE d.deptno = e.deptno
  6     AND e.sal >= 1000
  7  ;

해석되었습니다.

SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'basic'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  MERGE JOIN                  |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
|   3 |    INDEX FULL SCAN           | PK_DEPT |
|   4 |   SORT JOIN                  |         |
|   5 |    TABLE ACCESS FULL         | EMP     |
------------------------------------------------

12 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
         40  recursive calls
         12  db block gets
        158  consistent gets
          0  physical reads
          0  redo size
       1045  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL>
SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'basic rows bytes cost'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)|
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)|
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)|
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)|
|   4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)|
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)|
-----------------------------------------------------------------------------

12 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
         29  recursive calls
         12  db block gets
        150  consistent gets
          0  physical reads
          0  redo size
       1335  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL>
SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'typical'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

19 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
         29  recursive calls
         12  db block gets
        156  consistent gets
          0  physical reads
          0  redo size
       1819  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         19  rows processed

SQL>
SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'serial'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

19 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
         29  recursive calls
         12  db block gets
        156  consistent gets
          0  physical reads
          0  redo size
       1819  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         19  rows processed

SQL>
SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'all'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / E@SEL$1

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

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

   1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],
       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

43 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
         29  recursive calls
         12  db block gets
        156  consistent gets
          0  physical reads
          0  redo size
       3176  bytes sent via SQL*Net to client
        438  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         43  rows processed

SQL>
SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'outline'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

37 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
        157  recursive calls
         12  db block gets
        383  consistent gets
          0  physical reads
          0  redo size
       2422  bytes sent via SQL*Net to client
        438  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         37  rows processed

SQL>
SQL> SELECT plan_table_output
  2    FROM TABLE(dbms_xplan.display('PLAN_TABLE', 'manon94', 'advanced'))
  3  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / E@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

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

   1 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22],
       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

61 개의 행이 선택되었습니다.


Statistics
----------------------------------------------------------
        124  recursive calls
         12  db block gets
       1323  consistent gets
          0  physical reads
          0  redo size
       3875  bytes sent via SQL*Net to client
        460  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         61  rows processed

SQL>

(4) 실습 : dbms_xplan.display_cursor


SQL> set serveroutput off
  2  SELECT *
  3    FROM emp e, dept d
  4   WHERE d.deptno = e.deptno
  5     AND e.sal >= 1000
  6  ;

12 개의 행이 선택되었습니다.

SQL> SELECT prev_sql_id, prev_child_number
  2    FROM v$session
  3   WHERE sid = userenv('sid')
  4     AND username IS NOT NULL
  5     AND prev_hash_value <> 0
  6  ;

PREV_SQL_ID   PREV_CHILD_NUMBER
------------- -----------------
chbh66q2vddw3                 0

SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'iostats'))
  3  ;
SQL_ID  chbh66q2vddw3, child number 0
-------------------------------------
SELECT *   FROM emp e, dept d  WHERE d.deptno = e.deptno    AND e.sal
>= 1000

Plan hash value: 844388907

---------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |
|   1 |  MERGE JOIN                  |         |     14 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |
|*  4 |   SORT JOIN                  |         |     14 |
|*  5 |    TABLE ACCESS FULL         | EMP     |     14 |
---------------------------------------------------------

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


31 개의 행이 선택되었습니다.

SQL>
SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'memstats'))
  3  ;
SQL_ID  chbh66q2vddw3, child number 0
-------------------------------------
SELECT *   FROM emp e, dept d  WHERE d.deptno = e.deptno    AND e.sal
>= 1000

Plan hash value: 844388907

------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |       |          |
|   1 |  MERGE JOIN                  |         |     14 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |       |          |
|*  4 |   SORT JOIN                  |         |     14 |  2048 |  2048 |     2/0/0|
|*  5 |    TABLE ACCESS FULL         | EMP     |     14 |       |       |          |
------------------------------------------------------------------------------------

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


31 개의 행이 선택되었습니다.

SQL>
SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'allstats'))
  3  ;
SQL_ID  chbh66q2vddw3, child number 0
-------------------------------------
SELECT *   FROM emp e, dept d  WHERE d.deptno = e.deptno    AND e.sal
>= 1000

Plan hash value: 844388907

------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |       |          |
|   1 |  MERGE JOIN                  |         |     14 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |       |          |
|*  4 |   SORT JOIN                  |         |     14 |  2048 |  2048 |     2/0/0|
|*  5 |    TABLE ACCESS FULL         | EMP     |     14 |       |       |          |
------------------------------------------------------------------------------------

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


31 개의 행이 선택되었습니다.

SQL>
SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'allstats last'))
  3  ;
SQL_ID  chbh66q2vddw3, child number 0
-------------------------------------
SELECT *   FROM emp e, dept d  WHERE d.deptno = e.deptno    AND e.sal
>= 1000

Plan hash value: 844388907

------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |       |          |
|   1 |  MERGE JOIN                  |         |     14 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |       |          |
|*  4 |   SORT JOIN                  |         |     14 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL         | EMP     |     14 |       |       |          |
------------------------------------------------------------------------------------

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


31 개의 행이 선택되었습니다.

SQL>
SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display_cursor('chbh66q2vddw3', '0', 'advanced allstats last'))
  3  ;
SQL_ID  chbh66q2vddw3, child number 0
-------------------------------------
SELECT *   FROM emp e, dept d  WHERE d.deptno = e.deptno    AND e.sal
>= 1000

Plan hash value: 844388907

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     6 (100)|          |       |    |     |
|   1 |  MERGE JOIN                  |         |     14 |   812 |     6  (17)| 00:00:01 |       |    |     |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |    80 |     2   (0)| 00:00:01 |       |    |     |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |     1   (0)| 00:00:01 |       |    |     |
|*  4 |   SORT JOIN                  |         |     14 |   532 |     4  (25)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL         | EMP     |     14 |   532 |     3   (0)| 00:00:01 |       |    |     |
--------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / E@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
      PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

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

   1 - "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14],
       "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22]
   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


71 개의 행이 선택되었습니다.

SQL>

(4) 실습 : ROW-Source 별 수행 통계


SQL> set pagesize 0
SQL> set linesize 200
SQL> set serveroutput off
SQL> SELECT /*+ gather_plan_statistics */ *
  2    FROM emp e, dept d
  3   WHERE d.deptno = e.deptno
  4     AND e.sal >= 1000
  5  ;

12 개의 행이 선택되었습니다.

SQL> SELECT prev_sql_id, prev_child_number
  2    FROM v$session
  3   WHERE sid = userenv('sid')
  4     AND username IS NOT NULL
  5     AND prev_hash_value <> 0
  6  ;
4tbrp92uug9v8                 0

SQL> SELECT *
  2    FROM TABLE(dbms_xplan.display_cursor('4tbrp92uug9v8', '0', 'advanced allstats last'))
  3  ;
SQL_ID  4tbrp92uug9v8, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ *   FROM emp e, dept d  WHERE
d.deptno = e.deptno    AND e.sal >= 1000

Plan hash value: 844388907

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |     12 |00:00:00.01 |      11 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |     14 |   812 |     6  (17)| 00:00:01 |     12 |00:00:00.01 |      11 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |    80 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      4 |     14 |   532 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     12 |00:00:00.01 |       7 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / E@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
      PX_JOIN_FILTER(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   5 - filter("E"."SAL">=1000)

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

   1 - "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "E"."EMPNO"[NUMBER,22],
       "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22],
       "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   5 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]


63 개의 행이 선택되었습니다.

SQL>

참조문서

서적(오라클 성능 고도화 원리와해법 I) : http://book.daum.net/detail/book.do?bookid=KOR9788996246015

DBMS_XPLAN 패키지 (by manon94) [2012.04.21]