오라클 성능 고도화 원리와 해법 I (2012년)
DBMS_XPLAN 패키지 0 0 63,274

by 구루비스터디 DBMS_XPLAN [2018.03.20]


  1. Introduction
  2. (1) 예상 실행계획 출력
  3. (2) 캐싱된 커서의 실제 실행계획 출력
  4. (3) 캐싱된 커서의 Row Source별 수행 통계 출력
  5. (4) 실습 : DBMS_XPLAN.DISPLAY
  6. (4) 실습 : DBMS_XPLAN.DISPLAY_CURSOR
  7. (4) 실습 : ROW-Source 별 수행 통계


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>

코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3086

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입