Display_cursor

DISPLAY_CURSOR displays the execution plans for one or several cursors
in the shared SQL area

DISPLAY_CURSOR는 shared SQL area안에 있는 하나또는 몇몇 커서에 대한 실행 계획을 표시한다.

 Display_cursor Table Function 함수설명

 function display_cursor(sql_id           varchar2 default null,
         cursor_child_no  integer default 0,
         format           varchar2 default 'TYPICAL')

  - sql_id:
        해당 SQL의 SQL_ID를 지정한다.
        (V$SQL.SQL_ID, V$SESSION.SQL_ID, V$SESSION.PREV_SQL_ID 참조)
        SQL_ID를 명시하지 않으면 해당 세션의 마지막 실행문장을 의미한다.

  - cursor_child_no:
        해당 SQL CURSOR의 child number를 지정한다.
        (V$SQL.CHILD_NUMBER, V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER 참조)
        SQL_ID의 설정에 따라 고려되어야 한다.

  - format:
        실행된 Plan을 어떻게 보여줄지 결정한다.

Display_cursor의 포멧

Basic

가장 기본적인 포맷이다


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'basic'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1,
a.col2   from t_plan a   where (a.col1, a.col2) in (select /*+
parallel(b,8) qb_name(sub_query) */
b.col1, b.col2                                from t_plan2 b)

Plan hash value: 641105310

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  PX COORDINATOR              |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |
|   3 |    HASH JOIN SEMI            |          |
|   4 |     BUFFER SORT              |          |
|   5 |      PX RECEIVE              |          |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 |
|   7 |        TABLE ACCESS FULL     | T_PLAN   |
|   8 |     PX PARTITION RANGE ALL   |          |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |
-------------------------------------------------


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

경   과: 00:00:00.21

Serial

Cost정보와 예측정보과 함께 파티션 정보가 표시된다.
가장 중요한 Predicate정보가 나타난다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'serial'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a
where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
             b.col1, b.col2                                from t_plan2 b)

Plan hash value: 641105310

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     7 (100)|          |       |       |
|   1 |  PX COORDINATOR              |          |       |       |            |          |       |       |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |     1 |    29 |     7  (15)| 00:00:01 |       |       |
|*  3 |    HASH JOIN SEMI            |          |     1 |    29 |     7  (15)| 00:00:01 |       |       |
|   4 |     BUFFER SORT              |          |       |       |            |          |       |       |
|   5 |      PX RECEIVE              |          | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |
|   7 |        TABLE ACCESS FULL     | T_PLAN   | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |
|   8 |     PX PARTITION RANGE ALL   |          |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |
---------------------------------------------------------------------------------------------------------

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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


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

경   과: 00:00:00.23

Typical

Default포멧이다. 파티션, 패러럴정보가 표시된다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'typical'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a   where (a.col1, a.col2) in
(select /*+ parallel(b,8) qb_name(sub_query) */                                     b.col1, b.col2
    from t_plan2 b)

Plan hash value: 641105310

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     7 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR              |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN SEMI            |          |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE              |          | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        | S->P | PART (KEY) |
|   7 |        TABLE ACCESS FULL     | T_PLAN   | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        |      |            |
|   8 |     PX PARTITION RANGE ALL   |          |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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


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

경   과: 00:00:00.16

Cost

All

Typical정보와 함께 Query Block Name과 Column Projection이 표시된다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'all'));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a   where (a.col1, a.col2) in
(select /*+ parallel(b,8) qb_name(sub_query) */                                     b.col1, b.col2
    from t_plan2 b)

Plan hash value: 641105310

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     7 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR              |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN SEMI            |          |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE              |          | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        | S->P | PART (KEY) |
|   7 |        TABLE ACCESS FULL     | T_PLAN   | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        |      |            |
|   8 |     PX PARTITION RANGE ALL   |          |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$DBE3B336
   7 - SEL$DBE3B336 / A@MAIN_QUERY
   9 - SEL$DBE3B336 / B@SUB_QUERY

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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

   1 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   2 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   3 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   4 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   5 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   6 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
   9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]

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


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

경   과: 00:00:00.24

Outline

Typical정보와 함께 아우트라인정보를 확인할 수 있다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'outline'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a   where (a.col1, a.col2) in
(select /*+ parallel(b,8) qb_name(sub_query) */                                     b.col1, b.col2
    from t_plan2 b)

Plan hash value: 641105310

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     7 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR              |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN SEMI            |          |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE              |          | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        | S->P | PART (KEY) |
|   7 |        TABLE ACCESS FULL     | T_PLAN   | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        |      |            |
|   8 |     PX PARTITION RANGE ALL   |          |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"SEL$DBE3B336")
      UNNEST(@"SUB_QUERY")
      OUTLINE(@"MAIN_QUERY")
      OUTLINE(@"SUB_QUERY")
      FULL(@"SEL$DBE3B336" "A"@"MAIN_QUERY")
      FULL(@"SEL$DBE3B336" "B"@"SUB_QUERY")
      LEADING(@"SEL$DBE3B336" "A"@"MAIN_QUERY" "B"@"SUB_QUERY")
      USE_HASH(@"SEL$DBE3B336" "B"@"SUB_QUERY")
      PQ_DISTRIBUTE(@"SEL$DBE3B336" "B"@"SUB_QUERY"PARTITION NONE)
      END_OUTLINE_DATA
  */

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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


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

경   과: 00:00:00.29

Advanced

All포멧 + Outline포멧 형식이다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a   where (a.col1, a.col2) in
(select /*+ parallel(b,8) qb_name(sub_query) */                                     b.col1, b.col2
    from t_plan2 b)

Plan hash value: 641105310

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     7 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR              |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN SEMI            |          |     1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     BUFFER SORT              |          |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   5 |      PX RECEIVE              |          | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        | S->P | PART (KEY) |
|   7 |        TABLE ACCESS FULL     | T_PLAN   | 10000 | 90000 |     4   (0)| 00:00:01 |       |       |        |      |            |
|   8 |     PX PARTITION RANGE ALL   |          |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |  9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$DBE3B336
   7 - SEL$DBE3B336 / A@MAIN_QUERY
   9 - SEL$DBE3B336 / B@SUB_QUERY

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"SEL$DBE3B336")
      UNNEST(@"SUB_QUERY")
      OUTLINE(@"MAIN_QUERY")
      OUTLINE(@"SUB_QUERY")
      FULL(@"SEL$DBE3B336" "A"@"MAIN_QUERY")
      FULL(@"SEL$DBE3B336" "B"@"SUB_QUERY")
      LEADING(@"SEL$DBE3B336" "A"@"MAIN_QUERY" "B"@"SUB_QUERY")
      USE_HASH(@"SEL$DBE3B336" "B"@"SUB_QUERY")
      PQ_DISTRIBUTE(@"SEL$DBE3B336" "B"@"SUB_QUERY"PARTITION NONE)
      END_OUTLINE_DATA
  */

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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

   1 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   2 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   3 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   4 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   5 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   6 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
   9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]

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


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

경   과: 00:00:00.24

Iostats

io와 관련된 buffer, pysical read pysical write정보가 표시된다. Plan Statistics정보가 출력된다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'iostats'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from
t_plan a   where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
                              b.col1, b.col2                                from t_plan2 b)

Plan hash value: 641105310

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  PX COORDINATOR              |          |      1 |        |   9999 |00:00:02.08 |      55 |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  3 |    HASH JOIN SEMI            |          |      2 |      1 |   9999 |00:00:06.28 |      42 |
|   4 |     BUFFER SORT              |          |      3 |        |  10000 |00:00:06.11 |       0 |
|   5 |      PX RECEIVE              |          |      3 |  10000 |  10000 |00:00:06.05 |       0 |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 |      0 |  10000 |      0 |00:00:00.01 |       0 |
|   7 |        TABLE ACCESS FULL     | T_PLAN   |      1 |  10000 |  10000 |00:00:00.05 |      46 |
|   8 |     PX PARTITION RANGE ALL   |          |      3 |   9999 |   9999 |00:00:00.09 |      42 |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |      2 |   9999 |   9999 |00:00:00.03 |      42 |
---------------------------------------------------------------------------------------------------

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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


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

경   과: 00:00:00.30

Memstats

Sort 및 Hast 영역을 사용할 때의 메모리정보를 표시한다. Plan Statistics정보가 출력된다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'memstats'));

PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a   where
(a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
b.col1, b.col2                                from t_plan2 b)

Plan hash value: 641105310

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------
|   1 |  PX COORDINATOR              |          |      1 |        |   9999 |00:00:02.08 |       |       |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |      0 |      1 |      0 |00:00:00.01 |       |       |          |
|*  3 |    HASH JOIN SEMI            |          |      2 |      1 |   9999 |00:00:06.28 |   921K|   921K|     3/0/0|
|   4 |     BUFFER SORT              |          |      3 |        |  10000 |00:00:06.11 | 93184 | 93184 |     3/0/0|
|   5 |      PX RECEIVE              |          |      3 |  10000 |  10000 |00:00:06.05 |       |       |          |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 |      0 |  10000 |      0 |00:00:00.01 |       |       |          |
|   7 |        TABLE ACCESS FULL     | T_PLAN   |      1 |  10000 |  10000 |00:00:00.05 |       |       |          |
|   8 |     PX PARTITION RANGE ALL   |          |      3 |   9999 |   9999 |00:00:00.09 |       |       |          |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |      2 |   9999 |   9999 |00:00:00.03 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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


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

경   과: 00:00:00.18

Allstats

Hash Value가 같은 SQL의 누적통계를 표시한다. Plan Statistics정보가 출력된다.

Allstats Last

해당 SQL이 마지막 실행된 통계정보를 표시한다. Plan Statistics정보가 출력된다.


WOONG@ORCL>select * from table(dbms_xplan.display_cursor('6mq5vfpcam4jm',0,'advanced allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID  6mq5vfpcam4jm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a   where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
           b.col1, b.col2                                from t_plan2 b)

Plan hash value: 641105310

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  PX COORDINATOR              |          |      1 |        |       |            |          |       |       |        |      |            |   9999 |
|   2 |   PX SEND QC (RANDOM)        | :TQ10001 |      0 |      1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |      0 |
|*  3 |    HASH JOIN SEMI            |          |      0 |      1 |    29 |     7  (15)| 00:00:01 |       |       |  Q1,01 | PCWP |            |      0 |
|   4 |     BUFFER SORT              |          |      0 |        |       |            |          |       |       |  Q1,01 | PCWC |            |      0 |
|   5 |      PX RECEIVE              |          |      0 |  10000 | 90000 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |      0 |
|   6 |       PX SEND PARTITION (KEY)| :TQ10000 |      0 |  10000 | 90000 |     4   (0)| 00:00:01 |       |       |        | S->P | PART (KEY) |      0 |
|   7 |        TABLE ACCESS FULL     | T_PLAN   |      1 |  10000 | 90000 |     4   (0)| 00:00:01 |       |       |        |      |            |  10000 |
|   8 |     PX PARTITION RANGE ALL   |          |      0 |   9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWC |            |      0 |
|   9 |      TABLE ACCESS FULL       | T_PLAN2  |      0 |   9999 |   195K|     2   (0)| 00:00:01 |     1 |     3 |  Q1,01 | PCWP |            |      0 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------
00:00:02.08 |      55 |       |       |          |
00:00:00.01 |       0 |       |       |          |
00:00:00.01 |       0 |   921K|   921K| 1246K (0)|
00:00:00.01 |       0 | 93184 | 93184 |  104K (0)|
00:00:00.01 |       0 |       |       |          |
00:00:00.01 |       0 |       |       |          |
00:00:00.05 |      46 |       |       |          |
00:00:00.01 |       0 |       |       |          |
00:00:00.01 |       0 |       |       |          |
--------------------------------------------

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

   1 - SEL$DBE3B336
   7 - SEL$DBE3B336 / A@MAIN_QUERY
   9 - SEL$DBE3B336 / B@SUB_QUERY

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OUTLINE_LEAF(@"SEL$DBE3B336")
      UNNEST(@"SUB_QUERY")
      OUTLINE(@"MAIN_QUERY")
      OUTLINE(@"SUB_QUERY")
      FULL(@"SEL$DBE3B336" "A"@"MAIN_QUERY")
      FULL(@"SEL$DBE3B336" "B"@"SUB_QUERY")
      LEADING(@"SEL$DBE3B336" "A"@"MAIN_QUERY" "B"@"SUB_QUERY")
      USE_HASH(@"SEL$DBE3B336" "B"@"SUB_QUERY")
      PQ_DISTRIBUTE(@"SEL$DBE3B336" "B"@"SUB_QUERY"PARTITION NONE)
      END_OUTLINE_DATA
  */

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

   3 - access("A"."COL2"="B"."COL2" AND "A"."COL1"="B"."COL1")

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

   1 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   2 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   3 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   4 - (#keys=0) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   5 - "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   6 - (#keys=2) "A"."COL2"[NUMBER,22], "A"."COL1"[VARCHAR2,10]
   7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
   9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]

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


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

경   과: 00:00:00.29

\+Peeked binds

해당 SQL에서 사용된 Bind변수값을 추출할 수 있다.


WOONG@ORCL>var ag_bind varchar2(5);
WOONG@ORCL>exec :ag_bind := 'Many2';

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

경   과: 00:00:00.22
WOONG@ORCL>select /*+ gather_plan_statistics qb_name(main_query)*/
  2         a.col1, a.col2
  3    from t_plan a
  4   where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
  5                                     b.col1, b.col2
  6                                from t_plan2 b
  7                               where col1 = :ag_bind
  8                                 and col2 <= 100);

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

경   과: 00:00:02.13
WOONG@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all allstats last +peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  cjtzqad54bcmy, child number 2
-------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a  where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
  b.col1, b.col2                               from t_plan2 b                              where col1 = :ag_bind                                and col2 <= 100)

Plan hash value: 1032089627

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
| Id  | Operation                         | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |
Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
|   1 |  PX COORDINATOR                   |            |      1 |        |       |            |       |  |       |        |      |            |      0 |00:00:02.12 |      32 |       |       |
 |
|   2 |   PX SEND QC (RANDOM)             | :TQ10002   |      0 |      1 |    18 |     5  (20)| 00:00:01 |       |       |  Q1,02 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |       |       |
         |
|*  3 |    HASH JOIN SEMI BUFFERED        |            |      0 |      1 |    18 |     5  (20)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      0 |00:00:00.01 |       0 |  1068K|  1068K|
         |
|   4 |     BUFFER SORT                   |            |      0 |        |       |            |       |  |       |  Q1,02 | PCWC |            |      0 |00:00:00.01 |       0 | 73728 | 73728 |
 |
|   5 |      PX RECEIVE                   |            |      0 |      1 |     9 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      0 |00:00:00.01 |       0 |       |       |
         |
|   6 |       PX SEND HASH                | :TQ10000   |      0 |      1 |     9 |     2   (0)| 00:00:01 |       |       |        | S->P | HASH       |      0 |00:00:00.01 |       0 |       |       |
         |
|*  7 |        TABLE ACCESS BY INDEX ROWID| T_PLAN     |      1 |      1 |     9 |     2   (0)| 00:00:01 |       |       |        |      |            |      0 |00:00:00.01 |      29 |       |       |
         |
|*  8 |         INDEX RANGE SCAN          | T_PLAN_IDX |      1 |      1 |       |     1   (0)| 00:00:01 |       |       |        |      |            |      0 |00:00:00.01 |      29 |       |       |
         |
|   9 |     PX RECEIVE                    |            |      0 |      1 |     9 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      0 |00:00:00.01 |       0 |       |       |
         |
|  10 |      PX SEND HASH                 | :TQ10001   |      0 |      1 |     9 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |      0 |00:00:00.01 |       0 |       |       |
         |
|  11 |       PX BLOCK ITERATOR           |            |      0 |      1 |     9 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWC |            |      0 |00:00:00.01 |       0 |       |       |
         |
|* 12 |        TABLE ACCESS FULL          | T_PLAN2    |      0 |      1 |     9 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |      0 |00:00:00.01 |       0 |       |       |
         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------

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

   1 - SEL$DBE3B336
   7 - SEL$DBE3B336 / A@MAIN_QUERY
   8 - SEL$DBE3B336 / A@MAIN_QUERY
  12 - SEL$DBE3B336 / B@SUB_QUERY

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

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

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

   3 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2")
   7 - filter("A"."COL2"<=100)
   8 - access("A"."COL1"=:AG_BIND)
  12 - access(:Z>=:Z AND :Z<=:Z)
       filter(("COL1"=:AG_BIND AND "COL2"<=100))

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

   1 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   2 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   3 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   4 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   5 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   6 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   8 - "A".ROWID[ROWID,10], "A"."COL1"[VARCHAR2,10]
   9 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
  10 - (#keys=2) "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
  11 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22]
  12 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22]


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

경   과: 00:00:03.31


추가TEST : Peeked binds는 Bind Peeking을 사용하는 경우만 의미가 있다.(바인드피킹 enable = false하면 안된다는 뜻??)

WOONG@ORCL>alter session set "_optim_peek_user_binds" = false;

세션이 변경되었습니다.

경   과: 00:00:00.07
WOONG@ORCL>var ag_bind varchar2(5);
WOONG@ORCL>exec :ag_bind := 'Many2';

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

경   과: 00:00:00.00
WOONG@ORCL>select /*+ gather_plan_statistics qb_name(main_query)*/
  2         a.col1, a.col2
  3    from t_plan a
  4   where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
  5                                     b.col1, b.col2
  6                                from t_plan2 b
  7                               where col1 = :ag_bind
  8                                 and col2 <= 100);

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

경   과: 00:00:02.42
WOONG@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'all allstats last +peeked_binds'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  cjtzqad54bcmy, child number 19
--------------------------------------
select /*+ gather_plan_statistics qb_name(main_query)*/        a.col1, a.col2   from t_plan a  where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_n
            b.col1, b.col2                               from t_plan2 b                              where col1 = :ag_bind

Plan hash value: 2253543560

-------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib | A-Rows |

-------------------------------------------------------------------------------------------------------------------------------------------------------

|   1 |  PX COORDINATOR           |          |      1 |        |       |            |          |       |       |        |      |            |      0 |0

|   2 |   PX SEND QC (RANDOM)     | :TQ10002 |      0 |      1 |    18 |     9  (12)| 00:00:01 |       |       |  Q1,02 | P->S | QC (RAND)  |      0 |0

|*  3 |    HASH JOIN SEMI BUFFERED|          |      0 |      1 |    18 |     9  (12)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      0 |0

|   4 |     BUFFER SORT           |          |      0 |        |       |            |          |       |       |  Q1,02 | PCWC |            |      0 |0

|   5 |      PX RECEIVE           |          |      0 |    100 |   900 |     6   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      0 |0

|   6 |       PX SEND HASH        | :TQ10000 |      0 |    100 |   900 |     6   (0)| 00:00:01 |       |       |        | S->P | HASH       |      0 |0

|*  7 |        TABLE ACCESS FULL  | T_PLAN   |      1 |    100 |   900 |     6   (0)| 00:00:01 |       |       |        |      |            |      0 |0

|   8 |     PX RECEIVE            |          |      0 |    100 |   900 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |      0 |0

|   9 |      PX SEND HASH         | :TQ10001 |      0 |    100 |   900 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |      0 |0

|  10 |       PX BLOCK ITERATOR   |          |      0 |    100 |   900 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWC |            |      0 |0

|* 11 |        TABLE ACCESS FULL  | T_PLAN2  |      0 |    100 |   900 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |      0 |0

-------------------------------------------------------------------------------------------------------------------------------------------------------



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

   1 - SEL$DBE3B336
   7 - SEL$DBE3B336 / A@MAIN_QUERY
  11 - SEL$DBE3B336 / B@SUB_QUERY

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

   3 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2")
   7 - filter(("A"."COL2"<=100 AND "A"."COL1"=:AG_BIND))
  11 - access(:Z>=:Z AND :Z<=:Z)
       filter(("COL2"<=100 AND "COL1"=:AG_BIND))

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

   1 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   2 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   3 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   4 - (#keys=0) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   5 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   6 - (#keys=2) "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   7 - "A"."COL1"[VARCHAR2,10], "A"."COL2"[NUMBER,22]
   8 - "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
   9 - (#keys=2) "B"."COL1"[VARCHAR2,10], "B"."COL2"[NUMBER,22]
  10 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22]
  11 - "COL1"[VARCHAR2,10], "COL2"[NUMBER,22]


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

경   과: 00:00:00.45
WOONG@ORCL>

바인드변수의 확인

Trace10053, v$sql_bind_capture정보를 통해서도 확인할 수 있다.

v$sql_bind_capture저장량을 조절하는 방법

\_cursor_bind_capture_area_size :
일정량의 bind변수 저장공간을 조절하여 보다 많은 변수를 저장할 수 있다.
\_cursor_bind_capture_interval :
값을 줄여 Bind Capture의 주기를 좀더 짧은 간격으로 Capture를 할 수 있으나
약간의 성능 저하를 예상해야 한다.

예제 및 응용


--특정 SID를 가진 세션의 직전 Query의 실행계획을 추출
 select t.*
 from v$session s,
 table(dbms_xplan.display_cursor(s.prev_sql_id,
                                 s.prev_child_number)) t
 where s.sid=9;

--SH유저가 파싱한 sql문장 중 'sAleS'문자열이 있는 내용있는 Query의 실행계획을 추출
 select t.*
 from v$sql s, dba_users u,
      table(dbms_xplan.display_cursor(s.sql_id, s.child_number,
                                      'RUNSTATS_TOT')) t
 where s.sql_text like '%sAleS%'
 and u.user_id=s.parsing_user_id
 and u.username='SH';

--Shared Pool(v$sql)에 Cache되어 있는 Query들 중 buffer_gets(logical reads) 수치가 높은 순으로 Runtime 실행 계획을 추출한다.
select plan_table_output
from
  (select * from
    (select s.sql_id, s.child_number
      from v$sql s
      where exists(select 1 from v$sql_plan p where p.plan_hash_value = s.plan_hash_value)
      order by s.buffer_gets desc)
    where rownum <= 10
  ) s,
  table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last'));

Display_awr

DISPLAY_AWR displays the execution plans for SQL statements stored in
the Automatic Workload Repository (AWR)

DISPLAY_AWR는 Automatic Workload Repository (AWR)에 저장된 SQL문장의 실행계획을 표시한다.

DISPLAY_AWR Function를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다

DBA_HIST_SQL_PLAN
DBA_HIST_SQLTEXT

Display_awr Table Function 함수설명

  function display_awr(sql_id varchar2,
         plan_hash_value integer default null,
         db_id integer default null,
         format varchar2 default 'TYPICAL')

sql_id:
AWR에 저장되어 있는 SQL_ID를 지정한다.
(DBA_HIST_SQL_PLAN.SQL_ID 참조)

plan_hash_value:
저장된 SQL의 식별자인 hash_value를 지정한다.(Optional Parameter)

db_id:
특정 database id를 지정한다.
생략 시 Default로 local database id가 지정된다.

format:
Display Table Function과 같다.

DBMS_XPLAN.DISPLAY_AWR() 사용예


select t.\*
from dba_hist_sqltext ht,
table(dbms_xplan.display_awr(ht.sql_id, null, null,
'-PREDICATE \+ALIAS')) t
where ht.sql_text like '%sAleS%';

Display_sqlset

DISPLAY_SQLSET displays the execution plans for SQL statements stored
in a SQL tuning set

DISPLAY_SQLSET은 SQL tuning set에 저장되어 있는 SQL문장에 대한 실행계획을 표시한다.

DISPLAY_AWR Function를 사용하기 위하여 다음 객체에 대한 SELECT privileges이 필요하다

ALL_SQLSET_PLANS
ALL_SQLSET_STATEMENTS
※ dbms_sqltune 패키지에 대한 설정이 되어야함

Display_awr Table Function 함수설명

 function display_sqlset(sqlset_name varchar2,
         sql_id varchar2,
         plan_hash_value integer default null,
         format varchar2 default 'TYPICAL',
         sqlset_owner varchar2 default null)

sqlset_name:
SQL tuning set에 지정된 이름

sql_id:
SQL tuning set에 저장된 SQL_ID를 지정

plan_hash_value:
저장된 SQL의 식별자인 hash_value를 지정한다.(Optional Parameter)

format:
Display Table Function과 같다.

sqlset_owner:
해당 SQL tuning set의 Owner를 지정한다.
Default는 현재 User이다

DBMS_XPLAN.DISPLAY_SQLSET사용예


select *
  from table(dbms_xplan.display_sqlset('my_sts',
                                       'gcfysssf6hykh',
                                       null,
                                       'ALL -NOTE -PROJECTION')) t

문서에 대하여

최초작성자 : 이지웅
최초작성일 : 2009년 2월 21일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*

Plan Statistics (by jongmali) [2009.04.30]

Display_cursor

&nbsp;Display_cursor Table Function 함수설명

Display_cursor의 포멧

Basic

Serial

Typical

Cost

All

Outline

Advanced

Iostats

Memstats

Allstats

Allstats Last

\+Peeked binds

예제 및 응용

Display_awr

Display_awr Table Function 함수설명

DBMS_XPLAN.DISPLAY_AWR() 사용예

Display_sqlset

Display_awr Table Function 함수설명

DBMS_XPLAN.DISPLAY_SQLSET사용예

문서에 대하여

Display_cursor Table Function 함수설명