by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

DBMS_XPLAN

Added by 이재현, last edited by 이재현 on 3월 18, 2011



DBMS_XPLAN

  • Oracle 9i에서 소개됨
  • Query 튜닝의 시작과 끝을담당하는 유일무이한 Tool
TEST 준비 스크립트 ( 기본적인 사용법 ) 
SQL> select * from v$version where rownum <= 1
  2  ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

SQL> CREATE TABLE T1( C1 INT, C2 CHAR(10) );

테이블이 생성되었습니다.

SQL> INSERT INTO T1
  2  SELECT LEVEL, 'DUMMY'
  3  FROM DUAL
  4  CONNECT BY LEVEL <= 10000;

10000 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> CREATE INDEX T1_NL ON T1( C1 );

인덱스가 생성되었습니다.

SQL> SELECT COUNT( * ) CNT
  2    FROM T1
  3   WHERE C1 = 1;

       CNT
----------
         1

SQL>

|
기본 실행 계획 
SQL> EXPLAIN PLAN FOR
  2   SELECT *
  3    FROM T1
  4  WHERE C1 = 1 AND C2 = 'DUMMY';
WHERE C1 = 1 AND C2 = 'DUMMY'
           *
4행에 오류:
ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-00001: 무결성 제약 조건(JLIVE.PLAN_INDEX)에 위배됩니다
|
어쭈 =_= 

SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME LIKE '%PLAN%';

truncate table PLAN_TABLE;

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T1
  4   WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );

--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_NL |     1 |       |     1   (0)|
--------------------------------------------------------------------------

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

   1 - filter("C2"='DUMMY')
   2 - access("C1"=1)

Note
-----
   - 'PLAN_TABLE' is old version <-- =_=

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

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T1
  4   WHERE C1 = 1 AND C2 = 'DUMMY';
 WHERE C1 = 1 AND C2 = 'DUMMY'
            *
4행에 오류:
ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-00001: 무결성 제약 조건(JLIVE.PLAN_INDEX)에 위배됩니다

SQL> DROP TABLE PLAN_TABLE PURGE;

테이블이 삭제되었습니다.

SQL>
SQL> @?/rdbms/admin/utlxplan.sql

테이블이 생성되었습니다.

SQL>  EXPLAIN PLAN FOR
  2   SELECT *
  3     FROM T1
  4    WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );

Plan hash value: 2154509930

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_NL |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"='DUMMY')
   2 - access("C1"=1)

Note
-----
   - dynamic sampling used for this statement <-- ^^

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

|
  • Row Source Operation을 보여준다. Index t1_nl을 INDEX RANGE SCAN으로 Access하는 실행 계획이 수립될 것임을 보여준다.
  • Predicate 정보를 보여준다. 2번 Operation, 즉 Index t1_nl에 대한 RS 단계에서는 access("C1" = 1 ) Predicate가 사용되었다.
    Index Access를 통해서 걸러진 Data는 1번 단계, 즉 TABLE ACCESS BY INDEX ROWID Operation에서
    filter("C2" = 'dummy' ) Predicate를 이용해 다시 Fitering 된다. Predicate 정보는 이를 이해하지 못하면 Execution Plan의
    절반 밖에 이해하지 못한다고 할 정도로 중요한 정보이다.
  • Note 정보를 통해 부가적으로 필요한 정보를 제공한다. 이 예제에서는 Dynamic Sampling이 사용되었음을 알려준다.
    Orcle 10g에서는 통계 정보가 없는 Table에 대해서 Dynamic Sampling을 수행한다.
  • Access Predicate는 Access Type을 결정하는데 사용되는 Predicate( 조건 )를 의미한다. 더 정확하게 말하면 실제 Block을 읽기 전에
    어떤 방법으로 Block을 읽을 것인가를 결정한다는 의미이다. 따라서 Index Lookup 이나 Join 등은 Access Predicate로 표현된다.
  • Filter Predicate는 실제 Block을 읽은 후 Data를 걸러 내기 위해 사용되는 Predicate( 조건 )를 의미한다.
그럼 통계 정보가 있다면.?? =_= 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T1
  4   WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL>  SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2154509930

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    14 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_NL |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"='DUMMY')
   2 - access("C1"=1)

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

|
그럼 유니크 인댁스를 사용한다면..?? =_= 
SQL>  CREATE TABLE T2( C1 INT, C2 CHAR(10) );

테이블이 생성되었습니다.

SQL>  INSERT INTO T2
  2  SELECT LEVEL, 'DUMMY'
  3  FROM DUAL
  4  CONNECT BY LEVEL <= 10000;

10000 개의 행이 만들어졌습니다.

SQL>  CREATE UNIQUE INDEX T1_UNIQUE_INDEX ON T2( C1 );

인덱스가 생성되었습니다.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>  SELECT /*+ gather_plan_statistics */*
  2    FROM T1
  3   WHERE C1 = 1 AND C2 = 'DUMMY';

        C1 C2
---------- ----------
         1 DUMMY

SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));

SQL_ID  4p1gp2192gq0k, child number 0
-------------------------------------
 SELECT /*+ gather_plan_statistics */*   FROM T1  WHERE C1 = 1 AND C2 = 'DUMMY'

Plan hash value: 2154509930

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |00:00:00.01 |       4 |      2 |
|*  2 |   INDEX RANGE SCAN          | T1_NL |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("C2"='DUMMY')
   2 - access("C1"=1)


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

SQL>
SQL> SAVE XPLAN_C
file XPLAN_C.sql(이)가 생성되었습니다
SQL>

SQL>  SELECT /*+ gather_plan_statistics */*
  2    FROM T2
  3   WHERE C1 = 1 AND C2 = 'DUMMY';

        C1 C2
---------- ----------
         1 DUMMY

SQL> @XPLAN_C

SQL_ID  0fnqn7r64jg7m, child number 0
-------------------------------------
 SELECT /*+ gather_plan_statistics */*   FROM T2  WHERE C1 = 1 AND C2 = 'DUMMY'

Plan hash value: 3273381761

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2              |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | T1_UNIQUE_INDEX |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

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

   1 - filter("C2"='DUMMY')
   2 - access("C1"=1)

|

Access Predicate와 Filter Predicate가 표현 되는 방식에 대한 정확한 이해는 실행 계획 해석에 있어 필수적인 지식

준비 
SQL> DROP TABLE T1 PURGE;
SQL> DROP TABLE T2 PURGE;

SQL>
SQL> CREATE TABLE T1 ( C1 INT, C2 INT )
 QL> CREATE TABLE T2 ( C1 INT, C2 INT );

SQL>
SQL> CREATE INDEX T1_N1 ON T1 ( C1 );
SQL> CREATE INDEX T1_N2 ON T2 ( C1 );

SQL> INSERT INTO T1 SELECT LEVEL, LEVEL
  2  FROM DUAL
  3  CONNECT BY LEVEL <= 1000;

SQL> INSERT INTO T2 SELECT LEVEL, LEVEL
  2  FROM DUAL
  3  CONNECT BY LEVEL <= 1000;
|
NL 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ USE_NL( T1 T2 ) */ *
  3    FROM T1
  4           , T2
  5  WHERE T1.C1 = T2.C1
  6      AND T1.C2 = 1;

해석되었습니다.

SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );

Plan hash value: 1090018354

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    14 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |     1 |    14 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |     7 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - filter("T1"."C2"=1)
   4 - access("T1"."C1"="T2"."C1")

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

SQL> SAVE @PLAN
|
  • NL JOIN에서는 후행 Table 에 대한 Access 단계( 4번 )dptj Access Predicate가 표현되는 것을 알 수 있다.
    Join 단계인 2번이 아니라 4번 단계에서 Access Predicate정보가 출력되는 것에 주의해야 한다.
    Join에 참여하지 못하는 Column에 대한 조건( 3번 )은 Filter Predicate로 표현된다.
Hash Join 
  SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ USE_HASH( T1 T2 ) */ *
  3    FROM T1
  4           , T2
  5  WHERE T1.C1 = T2.C1
  6      AND T1.C2 = 1;
  
  SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY );

Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    14 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    14 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  7000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C1")
   2 - filter("T1"."C2"=1)
|

왜 NL Join과 Hash Join 에서 Access Predicate가 표현 되는 방식의 차이가 발생하는가??

  • NL : 선행 Table을 읽으면서 후행 Table을 한번씩 Access하는 방식이다. 따라서 실제 Join은 후행 Table에 대한 Access에서 발생한다.
    따라서 후행 Table을 읽는 단계가 Access Predicate가 된다.
  • Hash : 선행 Table을 먼저 Build한 후, 후행 Table 과 한번에 Join하는 방식이다.
    따라서 실제 Join이 발생하는 Hash Join 단계가 Access Predicate로 표현한다.
    DMBS_XPLAN.DISPLAY Function이 제공하는 여러가지 Parameter.. 
    SQL> EXPLAIN PLAN FOR
  2   SELECT *
  3  FROM T1
  4  WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

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

SQL> EXPLAIN PLAN FOR
  2   SELECT *
  3  FROM T1
  4  WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'TYPICAL', NULL ));

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

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

SQL> EXPLAIN PLAN
  2  SET STATEMENT_ID = 'TEST' FOR
  3   SELECT *
  4  FROM T1
  5  WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', 'TEST', 'TYPICAL', NULL ));

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

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

|

다양한 출력 Format 4가지

Basic Format : 실행 계획의 단계별 Operation과 Object이름만을 보여주는 말 그대로 매우 기본적인 Format 이다. ( 사용 거의 X ) 

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3   FROM T1
  4  WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'BASIC' ) );

Plan hash value: 1420382924

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|   2 |   INDEX RANGE SCAN          | T1_N1 |
---------------------------------------------

Typical Format : 가장 일반적인 용도 ( 기본값 )

  • Predicate 정보가 왜 그렇게 중요한가? 실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서
    정확하게 어떻게 상용되었는지가 매우 중요해진다. Query Transformation 이란는 특별한 과정 때문에 Predicate의 변형이
    발생할 때는 이 정보가 특히 중요하다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'TYPICAL' ) );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

All Format : 실행 계획을 분석하는데 있어서 없어서는 안될 중요한 두 가지 정보를 추가적으로 제공한다.

  • Query Block : SQL 문장을 Query Block이라는 단위로 나눈데 Transformation 및 Optimization의 기본 단위가 된다.
    Query Block 명은 Inline View 와 Subquery가 많이 사용되는 복작한 Query를 해석할 때 특히 유용하다.
  • Column Projection : 실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미한다.
    Query Transformation 을 Troubleshooting 할 때 유용한 정보가 된다.
    All Format 
    SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

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

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

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

|
    QB_NAME Hint : Query Block 명을 직접 조작 할 수 있다. 
    SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ QB_NAME( X ) */ *
  3    FROM T1
  4   WHERE C1 = 1 AND C2 = 'DUMMY';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - X / T1@X <--
   2 - X / T1@X

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)


PLAN_TABLE_OUTPUT
-----------------------------------------------------------

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

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

|
    그럼 한번 해볼까나.?? =_= 안되네 음.. 
    SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ FULL( @X ) NO_MERGE( A ) */ *
  3    FROM (
  4  SELECT /*+ QB_NAME( X ) */ *
  5    FROM T1
  6   WHERE C1 = 1 AND C2 = '1' --gather_plan_statistics
  7  ) A;

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - SEL$6CE2C157 / T1@X
   2 - SEL$6CE2C157 / T1@X

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

   1 - filter("C2"=1)
   2 - access("C1"=1)

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

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

27 개의 행이 선택되었습니다.
|
    ㅋㅋㅋ 
    SQL> EXPLAIN PLAN FOR
  2  SELECT /*+  QB_NAME( MAIN )NO_MERGE( A@MAIN ) */*
  3    FROM (
  4  SELECT /*+  QB_NAME( X ) */ *
  5    FROM T1
  6   WHERE C1 = 1 AND C2 = '1'
  7  ) A
  8  ;

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 2394988879

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  VIEW                        |       |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - X / A@MAIN
   2 - X / T1@X
   3 - X / T1@X

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

   2 - filter("C2"=1)
   3 - access("C1"=1)

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

   1 - "A"."C1"[NUMBER,22], "A"."C2"[NUMBER,22]
   2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
   3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
|
    InLine View FULL 안되네 음... 
    SQL> EXPLAIN PLAN FOR
  2  SELECT /*+  QB_NAME( MAIN ) FULL( T1@SUB) NO_MERGE( A@MAIN ) */*
  3    FROM (
  4  SELECT /*+  QB_NAME( SUB ) */ *
  5    FROM T1
  6   WHERE C1 = 1 AND C2 = '1'
  7  ) A
  8  ;

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 2394988879

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  VIEW                        |       |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - SUB / A@MAIN
   2 - SUB / T1@SUB
   3 - SUB / T1@SUB

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

   2 - filter("C2"=1)
   3 - access("C1"=1)

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

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

30 개의 행이 선택되었습니다.
|
    NO_UNNEST 
    SQL> EXPLAIN PLAN FOR
  2  SELECT /*+  NO_UNNEST( @SUB ) */ *
  3    FROM T1
  4   WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 )
  5     AND C2 = '1';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 1322727408

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     7 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1    |     1 |     7 |     3   (0)| 00:00:01 |
|*  3 |   FILTER           |       |       |       |            |          |
|*  4 |    INDEX RANGE SCAN| T1_N1 |     1 |     4 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SUB
   4 - SUB   / T1@SUB

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUB") */ 0 FROM
              "T1" "T1" WHERE :B1<=3 AND :B2>=1 AND "C1"=:B3 AND "C1"<=3 AND "C1">=1))
   2 - filter("C2"=1)
   3 - filter(:B1<=3 AND :B2>=1)
   4 - access("C1"=:B1)
       filter("C1"<=3 AND "C1">=1)

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

   1 - "C1"[NUMBER,22], "C2"[NUMBER,22]
   2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
|
    UNNEST 
    SQL> EXPLAIN PLAN FOR
  2  SELECT /*+  UNNEST( @SUB ) */ *
  3    FROM T1
  4   WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 )
  5     AND C2 = '1';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 3994169173

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    11 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |       |     1 |    11 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     3 |       |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | T1_N1 |     3 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - SEL$0C6FB14C
   2 - SEL$0C6FB14C / T1@SEL$1
   3 - SEL$0C6FB14C / T1@SEL$1
   4 - SEL$0C6FB14C / T1@SUB

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

   2 - filter("C2"=1)
   3 - access("C1">=1 AND "C1"<=3)
   4 - access("C1"="C1")
       filter("C1"<=3 AND "C1">=1)

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

   1 - (#keys=0) "C1"[NUMBER,22], "C2"[NUMBER,22]
   2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
   3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]

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

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+  UNNEST( @SUB ) HASH_SJ( @SUB ) */ *
  3    FROM T1
  4   WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 )
  5     AND C2 = '1';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) );

Plan hash value: 2980426711

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    11 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN SEMI              |       |     1 |    11 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     3 |       |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | T1_N1 |     3 |    12 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - SEL$0C6FB14C
   2 - SEL$0C6FB14C / T1@SEL$1
   3 - SEL$0C6FB14C / T1@SEL$1
   4 - SEL$0C6FB14C / T1@SUB

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

   1 - access("C1"="C1")
   2 - filter("C2"=1)
   3 - access("C1">=1 AND "C1"<=3)
   4 - access("C1">=1 AND "C1"<=3)

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

   1 - (#keys=1) "C1"[NUMBER,22], "C2"[NUMBER,22]
   2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
   3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
   4 - "C1"[NUMBER,22]

SQL>

|

Outline Format : 실행 계획을 수립 하는데 필요한 Hint들의 목록을 의미한다.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T1
  4   WHERE C1 = 1 AND C2 = 'DUMMY'
  5  ;

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'OUTLINE' ) );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1")) <--
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS <--
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

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

Advanced Format : All Format과 Outline Format을 합친 것과 같다.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T1
  4   WHERE C1 = 1 AND C2 = 'DUMMY'
  5  ;

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ADVANCED' ) );

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

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

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter("C2"=TO_NUMBER('DUMMY'))
   2 - access("C1"=1)

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

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

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

Plan Statistics : 실행 계획의 단계별 일량 정보를 의미 ( 세가지 조건 중 하나를 만족 할때 )

  • Statistics Level이 ALL 일때
  • GATHER_PLAN_STATISTICS Hint를 사용할 때, Oracle 10g 부터 지원
  • _ROWSOURCE_EXECUTION_STATISTICS Parameter 값을 true로 변경한 경우
기본적인 사용법 ( GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR ) 준비 
SQL> CREATE TABLE T_PLAN( C1 VARCHAR2(10));
SQL> CREATE INDEX T_PLAN_IDX ON T_PLAN(C1);

SQL> INSERT INTO T_PLAN
  2  SELECT 'Many1'
  3   FROM DUAL
  4  CONNECT BY LEVEL <= 10000;

SQL> COMMIT;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'T_PLAN', CASCADE => TRUE, NO_INVALIDATE => FALSE );

|
변경전 분포도 100% 
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T_PLAN
  4   WHERE C1 = 'Many1';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );

Plan hash value: 2521935493

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 | 60000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_PLAN | 10000 | 60000 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("C1"='Many1')

|
변경 후 분포도 50% 
SQL> INSERT INTO T_PLAN
  2  SELECT 'Many2'
  3   FROM DUAL
  4  CONNECT BY LEVEL <= 10000;
  
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM T_PLAN
  4   WHERE C1 = 'Many2';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );

Plan hash value: 1175009887

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |     6 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_PLAN_IDX |     1 |     6 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("C1"='Many2')

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

SQL>

|
  • 통계 정보 생성 이후에 추가된 'Many2' 값을 모르기 때문에 부정확한 실행 계획을 수립함
  • 실제 ROW 수에 비해 훨씬 낮은 예측 ROW 수를 가정 함
  • ( GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR )
실제 플랜 GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Many2';

  COUNT(*)
----------
     10000

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  bhxfgwb1bjqn2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = 'Many2'

Plan hash value: 2787416999

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |      34 |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |      1 |      1 |  10000 |00:00:00.01 |      34 |
------------------------------------------------------------------------------------------

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

   2 - access("C1"='Many2')

|
  • ALLSTATS : Plan Statistics에 있는 모든 정보를 보여 달라는 의미
  • LAST : 가장 최근에 수행한 정보만 보여달라는 의미
  • 첫번째 NULL : SQL_ID
  • 두번째 NULL : Child Number를 의미한다. ( 소프트 파싱인경우 라이브러리 캐쉬에 조건 크기에따라 여러개의 라이브러리가 존재 한다. 맞음 ?? )
  • E-Rows ( Estimated Row Counts ) : 예측 Row 수 ( 오라클의 옵티마이저는 통계 정보와 SQL 문의 WHERE 조건 값을 참조해서 특정 조건을 만족하는 로우수 계산 )
  • A-Rows ( Actual Row Counts ): 실제 Row 수
  • A-Time : 실제 수행 시간
  • Buffers : Logical Reads
    Logical Reads는 Buffer Cache를 경유해 읽은 블록 수(이것을 Conventional Path Read라고 합니다)와
    Buffer Cache를 경유하지 않고 Process가 직접 읽은 블록 수(이것을 Direct Path Read라고 부릅니다)를 합친 값입니다.
    즉, 간단한게 말하면 특정 작업을 수행하는 과정에서(가령 쿼리를 실행하기 위해)
    읽는 데이터베이스 블록(Database Block) 수라고 보면 되겠습니다
  • Starts : 말 그대로 해당 오퍼레이션이 "시작"된 횟수를 의미합니다.
    이 개념은 Nested Loops Join을 생각하시면 쉽게 이해할 수 있습니다.
    Nested Loops Join은 선행 테이블에서 읽는 로우수만큼 후행 테이블을 탐색하는 구조입니다.
    만일 선행 테이블에서 100건이 나온다면 후행 테이블을 100번 액세스하게 됩니다.
    이럴 경우에 후행 테이블에서 대한 읽기 작업의 Starts 값이 "100"이 되는 것입니다
통계 재생성 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'T_PLAN', CASCADE => TRUE, NO_INVALIDATE => FALSE );

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Many2';

  COUNT(*)
----------
     10000

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

SQL_ID  bhxfgwb1bjqn2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 =
'Many2'

Plan hash value: 4286448300

---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |      38 |
|*  2 |   TABLE ACCESS FULL| T_PLAN |      1 |  10000 |  10000 |00:00:00.01 |      38 |
---------------------------------------------------------------------------------------

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

   2 - filter("C1"='Many2')


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

|
  • E-Rows와 A-Rows값이 일치 하며, 이로 인해 TFS을 선택함.
  • 참고로, Oracle 11g부터는 SQL*Trace에서도 동일한 기능을 제공한다. 아래 결과를 보면
    Tkprof Report의 Row Source Operation에서 실제 일량 외에 Cost, Cardinality 같은 정보를 추가 적으로 제공 ( P.56 맨위 )

다양한 출력 Format

Basic Format 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Few';

  COUNT(*)
----------
         0

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'BASIC' ));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1
= 'Few'

Plan hash value: 2787416999

----------------------------------------
| Id  | Operation         | Name       |
----------------------------------------
|   0 | SELECT STATEMENT  |            |
|   1 |  SORT AGGREGATE   |            |
|   2 |   INDEX RANGE SCAN| T_PLAN_IDX |
----------------------------------------


15 개의 행이 선택되었습니다.
|
Typical Format 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Few';

  COUNT(*)
----------
         0

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'TYPICAL' ));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1
= 'Few'

Plan hash value: 2787416999

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("C1"='Few')


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

|
ALL Format 
SQL>  SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2     FROM T_PLAN
  3    WHERE C1 = 'Few';

  COUNT(*)
----------
         0

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALL' ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  1a4gm5z0swug1, child number 0
-------------------------------------
 SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )    FROM T_PLAN   WHERE
C1 = 'Few'

Plan hash value: 2787416999

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T_PLAN@SEL$1

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

   2 - access("C1"='Few')

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

   1 - (#keys=0) COUNT(*)[22]


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

|
ALLSTATS 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Few';

  COUNT(*)
----------
         0

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS' ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = 'Few'

Plan hash value: 2787416999

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |      3 |      1 |      3 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |      3 |      1 |      0 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------------

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

   2 - access("C1"='Few')


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

SQL>
|
Allstats Last 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Few';

  COUNT(*)
----------
         0

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = 'Few'

Plan hash value: 2787416999

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |      1 |      1 |      0 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------

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

   2 - access("C1"='Few')


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

|
  • Allstats Format : Allstats Format = Iostats Format + Memstats Format
Iostats Format : I/O Statistics 즉, Read/Write 정보를 보여주겠다는 것을 의미 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Few';

  COUNT(*)
----------
         0

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'IOSTATS LAST' ));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = 'Few'

Plan hash value: 2787416999

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |      1 |      1 |      0 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------

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

   2 - access("C1"='Few')
|
  • Buffers : Logical Reads
    Logical Reads는 Buffer Cache를 경유해 읽은 블록 수(이것을 Conventional Path Read라고 합니다)와
    Buffer Cache를 경유하지 않고 Process가 직접 읽은 블록 수(이것을 Direct Path Read라고 부릅니다)를 합친 값입니다.
    즉, 간단한게 말하면 특정 작업을 수행하는 과정에서(가령 쿼리를 실행하기 위해)
    읽는 데이터베이스 블록(Database Block) 수라고 보면 되겠습니다
  • Read : Physical Reads ( Buffer Cache에 데이터가 존재하지 않아서 Disk I/O를 한 경우 )
  • Writes : Physical Writes
Memstats Format : Memory Statistics 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3       , T_PLAN
  4   WHERE ROWNUM <= 100000;

  COUNT(*)
----------
    100000

SQL>  SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'MEMSTATS LAST' ));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  38uradzrgj5b6, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN      , T_PLAN  WHERE ROWNUM <= 100000

Plan hash value: 4051414749

------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |        |      1 |      1 |      1 |00:00:00.03 |       |       |       |
|*  2 |   COUNT STOPKEY        |        |      1 |        |    100K|00:00:00.12 |       |       |       |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
|   3 |    MERGE JOIN CARTESIAN|        |      1 |    400M|    100K|00:00:00.02 |       |       |       |
|   4 |     TABLE ACCESS FULL  | T_PLAN |      1 |  20000 |      5 |00:00:00.01 |       |       |       |
|   5 |     BUFFER SORT        |        |      5 |  20000 |    100K|00:00:00.01 |   568K|   461K|  504K (0)|
|   6 |      TABLE ACCESS FULL | T_PLAN |      1 |  20000 |  20000 |00:00:00.01 |       |       |       |
------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=100000)


|
  • OMem : Optimal 연산에 필요한 예상 Memory를 의미한다. ( Sort Area의 크기가 568K 정도면 Optimal 소트가 예상되고 )
  • 1Mem : One Pass 연산에 필요한 예상 메모리 ( 461K 정도면 One Pass가 예상된다 )
  • Used-Mem : 실제 사용된 Memory 크기를 의미한다. ( (0)이라 One Pass 실패 했다 만약.. 1보다 큰값은 Multi Pass연산을 의미 )
  • Onepass : ?
  • multipass : ?
+Peeked binds : Bind Peeking이 활성화 되어 있을 경우에만 동작한다. =_= 

SQL> var b1 varchar2(10);
SQL> exec :b1 := 'Few';

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = :b1;

  COUNT(*)
----------
         0

SQL>  SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS' ));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  2jt5zbafx7dz7, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = :b1

Plan hash value: 4286448300

---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |      38 |
|*  2 |   TABLE ACCESS FULL| T_PLAN |      1 |  10000 |      0 |00:00:00.01 |      38 |
---------------------------------------------------------------------------------------

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

   2 - filter("C1"=:B1)


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


|
Bind Peeking 

SQL> SELECT A.KSPPINM  NAME,
  2         B.KSPPSTVL VALUE,
  3         B.KSPPSTDF DEF_YN,
  4         A.KSPPDESC DESCRIPTION
  5  FROM   X$KSPPI  A,
  6         X$KSPPSV B
  7  WHERE  A.INDX = B.INDX
  8    and a.ksppinm like '%_optim_peek_user_binds%'
  9  ;

NAME
--------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
DEF_YN
---------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------------------------------------
_optim_peek_user_binds
TRUE
TRUE
enable peeking of user binds

SQL>   alter session set "_optim_peek_user_binds"=true;

세션이 변경되었습니다.

SQL> var b1 varchar2(10);
SQL> exec :b1 := 'Few';

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = :b1;

  COUNT(*)
----------
         0

SQL>  SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +peeked_binds' ));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  2jt5zbafx7dz7, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = :b1

Plan hash value: 2787416999

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |      1 |      1 |      0 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=846): 'Few'

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

   2 - access("C1"=:B1)

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

|
  • Peeked Binds : Bind Peeking 을 사용하는 경우, Query가 최초로 실행되는 시점의 실제 Bind 값을 이용해서(Peeking해서) 실행 계획을 세우는 것을 의미한다.
    Bind 변수를 사용하는 경우 실제로 실행되는 시점에 어떤 값이 들어오는지 알 수 없기 때문에 컬럼의 평균적인 분포도만 가지고
    비용을 계산할 수 밖에 없다. 대부분의 경우에는 큰 문제가 언되지만 다음과 같은 경우에는 치명적인 단점을 가지고있다.
    Ex )
  • c1 = 1 : 99% ( Full Table Scan을 하는 것이 유리하다. )
  • c1 = 99 : 1% ( Index Scan을 하는 것이 유리하다.)
  • Bind Capture : Bind 변수의 값을 Capture 하되 SQL Cursor 마다 정해진 Memory 크기만큼(_CURSOR_BIND_CAPTURE_AREA_SIZE Parameter )
    정해진 간격( _CURSOR_BIND_CAPTURE_INTERVAL Parameter ) 마다 Capture하는 기능이다.
    Capture 된 Bind 값은 V$SQL_BIND_CAPTURE View 를 통해 조회 가능하다.
SQL Cursor 

SQL> SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT
  2    FROM V$SQL
  3  WHERE SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = :b1%'
  4    AND ROWNUM <= 1  ;

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------
2jt5zbafx7dz7            1
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = :b1


SQL> COLUMN SQL_ID NEW_VALUE V_SQL_ID;
SQL> COLUMN CHILD_NUMBER NEW_VALUE V_CHILD_NUMBER;
SQL>   SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&V_SQL_ID', '&V_CHILD_NUMBER', 'ALLSTATS' ));
구   1:   SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&V_SQL_ID', '&V_CHILD_NUMBER', 'ALLSTATS' ))
신   1:   SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '2jt5zbafx7dz7', '         1', 'ALLSTATS' ))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  2jt5zbafx7dz7, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1 = :b1

Plan hash value: 2787416999

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |      1 |      1 |      0 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------

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

   2 - access("C1"=:B1)


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

|
Dictionary View와 연동 라이브러리 캐쉬에서 밀려난듯 
SQL> EXPLAIN PLAN FOR
  2  SELECT COUNT( * )
  3    FROM T_PLAN
  4   WHERE C1 = 'Few';

해석되었습니다.

SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2787416999

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("C1"='Few')

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

SQL> SELECT P.PLAN_TABLE_OUTPUT
  2    FROM (SELECT DISTINCT SQL_ID, CHILD_NUMBER
  3            FROM V$SQL_PLAN S
  4           WHERE S.OBJECT_NAME = 'T_PLAN_IDX') S,
  5  TABLE( DBMS_XPLAN.DISPLAY_CURSOR( S.SQL_ID, S.CHILD_NUMBER, 'TYPICAL' ) ) P;

선택된 레코드가 없습니다.

|
한번 실행해서 올려놓구.. 
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
  2    FROM T_PLAN
  3   WHERE C1 = 'Few';

  COUNT(*)
----------
         0

SQL> SELECT P.PLAN_TABLE_OUTPUT
  2    FROM (SELECT DISTINCT SQL_ID, CHILD_NUMBER
  3            FROM V$SQL S
  4           WHERE SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS%') S,
  5  TABLE( DBMS_XPLAN.DISPLAY_CURSOR( S.SQL_ID, S.CHILD_NUMBER, 'TYPICAL' ) ) P;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1
= 'Few'

Plan hash value: 2787416999

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |            |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T_PLAN_IDX |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("C1"='Few')

SQL_ID  2jt5zbafx7dz7, child number 2


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )   FROM T_PLAN  WHERE C1
= :b1

NOTE: cannot fetch plan for SQL_ID: 2jt5zbafx7dz7, CHILD_NUMBER: 2
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)


SQL_ID  akh267xpw2cyf, child number 0


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )        FROM T_PLAN
                                 WHERE C1 = :b1

NOTE: cannot fetch plan for SQL_ID: akh267xpw2cyf, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)



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


|
Parallel Execution ( X ) 
SQL> ALTER TABLE T_PLAN PARALLEL 4;

테이블이 변경되었습니다.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)
  2   FROM T_PLAN
  3  ;

  COUNT(*)
----------
     20000

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID  cbkdc4b0d9ntk, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)  FROM T_PLAN

Plan hash value: 1491809887

---------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |          |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   PX COORDINATOR       |          |      1 |        |      4 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |
|   4 |     SORT AGGREGATE     |          |      0 |      1 |      0 |00:00:00.01 |       0 |
|   5 |      PX BLOCK ITERATOR |          |      0 |  20000 |      0 |00:00:00.01 |       0 |
|*  6 |       TABLE ACCESS FULL| T_PLAN   |      0 |  20000 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------

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

   6 - access(:Z>=:Z AND :Z<=:Z)


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

|
  • Parallel Slave가 수행한 단계의 실제 일량( A-Rows, Buffers )이 전혀 수립되지 않는다
Parallel DML ( X ) 
SQL> ALTER SESSION ENABLE PARALLEL DML;

세션이 변경되었습니다.

SQL> INSERT /*+ GATHER_PLAN_STATISTICS */ INTO T_PLAN SELECT * FROM T_PLAN T;

20000 개의 행이 만들어졌습니다.

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID  guvtztakgk4jt, child number 0
-------------------------------------
INSERT /*+ GATHER_PLAN_STATISTICS */ INTO T_PLAN SELECT * FROM T_PLAN T

Plan hash value: 3480535512

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  PX COORDINATOR         |          |      1 |        |      4 |00:00:01.43 |       8 |       |       |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |      0 |  20000 |      0 |00:00:00.01 |       0 |       |       |          |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
|   3 |    LOAD AS SELECT       |          |      0 |        |      0 |00:00:00.01 |       0 |   256K|   256K|          |
|   4 |     PX RECEIVE          |          |      0 |  20000 |      0 |00:00:00.01 |       0 |       |       |          |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 |      0 |  20000 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       PX BLOCK ITERATOR |          |      0 |  20000 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |        TABLE ACCESS FULL| T_PLAN   |      0 |  20000 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

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

   7 - access(:Z>=:Z AND :Z<=:Z)

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------


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

SQL> ROLLBACK;

롤백이 완료되었습니다.

|

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.