About DBMS_XPLAN - 2.포맷 설정하기

DBMS_XPLAN 패키지의 장점은 포맷을 자유로이 설정한다는것
  작년 겨울에 About DBMS_XPLAN - 1.실행계획 이라는 글에서 실행계획의 세부항목을 소개한바 있다.
이 패키지의가장 뛰어난 특징은 사용자가 출력 포맷을 설정하여 원하는 정보만 얻을수 있다는 것이다.
따라서 이번에는 DBMS_XPLAN 패키지 사용시 포맷설정을 자유롭게 하기 위한 Format Controller를 소개하려 한다.

Format Controller는 아래와 같이 3가지 종류가 있다.

1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller 이다.
                                   적용하지 않더라도 자동으로 기본값으로 적용된다.

2)세부 Format Controller: 기본 포맷정보에 의해서 표시되거나 생략되는 되는 세부적인 포맷을 Control 한다.
                                   이 Control은  + 표시로 추가하거나 - 표시로 생략이 가능하다.

3)실행통계 Format Controller: 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

이제 한가지씩 상세히 살펴보자.

1)  기본 Format Controller
1.basic      : 가장 기본적인 포맷으로서 id, Operation, Object Name을 출력한다.
2.typical     : basic 옵션에서 한발더 나아가서 옵티마이져가 에상할수 있는 모든것들을 보여준다.
                  출력되는 정보로는 예상 row, 예상 bytes, 예상 temporary space 사용량, cost, 예상시간,
                  Predicate Information(Operation 별로 access 및 filter 정보) 이다.
3.serial      : typical 과 같으나 parallel 쿼리사용시 관련 정보가 나오지 않는다.
4.all           : plan 정보는 typical 과 같으나 plan 이외의 정보중에서 Outline Data 정보를 제외하고 전부 출력한다.
5.advanced : all 과 같지만 Peeked Binds, Outline Data, note 등을 더보여준다.

2)  세부 Format Controller
1.alias  :Operation 별로 쿼리블럭명과 object alias 를 control 한다.
             plan 의 하단에 위치하며 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.        
2.bytes : plan 상의 E-Bytes 정보를 control 한다.
3.cost  :plan 상의 Cost (%CPU)를 control 한다.
4.note  : 결과중 가장 마지막에 위치하며 여러가지 유용한 정보를 보여준다.
            예를 들면 dynamic sampling 이 사용되었는지의 혹은 plan_table 이 old 버젼이므로 새로만들어야
            한다는 등의 유용한 정보를 나타낸다.     
5.outline : Outline Data를 control 한다. USER 가 작성한 힌트와 옵티마이져가 추가한 내부적인 힌트들이
               포함된다. 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.   
6.parallel : PARALLEL 쿼리인경우 TQ, IN-OUT, and PQ Distrib 등의 정보를 control 한다.
7.partition :파티션 ACCESS 가 포함된경우 Pstart(시작 파티션) and Pstop(종료 파티션) 등의 정보를 control 한다.
8.peeked_binds : BIND 변수의 값을 control 한다. 단 _optim_peek_user_binds 파라미터의 값이 TRUE 로
                          되어있는 경우만 해당되며  파라미터는 세션단위로 수정이 가능하다. EXPLAIN PLAN 을
                          사용한 경우에는 나타나지 않는다.
9.predicate : Predicate Information을 control 한다. Operation 별로 access 및 filter 정보를 나타낸다.
                   일반적인 튜닝시 가장 눈여겨 보아야할 정보이다.
10.projection : projection information을 control 한다. Operation 별로 select 되는 컬럼정보를 나타낸다.
11.remote : DBLINK 를 사용힐때 REMOTE 쿼리의 수행정보를 control 한다.
12.rows : plan 상의 E-Rows수를 control 한다.

3)  실행통계 Format Controller
  이정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다. 왜냐하면 explain plan 은 쿼리가 실제 수행되는것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 나 DBMS_XPLAN.DISPLAY_AWR 등의 함수 수행시에도 GATHER_PLAN_STATISTICS 힌트를 주거나 아니면 파라미터 STATISTICS_LEVEL = ALL 로 되어 있어야 출력이 가능하다.

1.allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp,
                Max-Tmp 등)를 동시에 control 한다.
2.iostats : I/O 통계정보(Buffers, Reads, Writes)를 control 한다.
3.last : 실행통계 출력시 이 control을 명시하면 가장마지막에 수행된 실행통계를 출력한다.
          이 control을 명시하지 않으면 실행통계의 누적치를 출력하므로 주의가 필요하다.
4.memstats :PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp, Max-Tmp 등)를 control 한다.
5.runstats_last : iostats control 과 last control 을 합친것과 같다.
                       이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
6.runstats_tot : iostats control과 동일하다.  이 control은 Oracle 10g Release 1 에서만 사용할수 있다.

주의사항 : runstats_last 와 runstats_tot 를 제외한 4가지의 control은 Oracle 10g Release 2 에서만 사용할수 있다.

그럼 이제 적용해볼까?
  위에서 설명한 Controller 를 이용하여 Format 을적용해보자.

SElECT  /*+ gather_plan_statistics */  *
FROM EMP E
WHERE E.DEPTNO = :B1
  AND ROWNUM <= 100
ORDER BY EMPNO;

이후로는 위의 SQL 은 동일하므로 생략된다.

SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'advanced allstats last' ) );

아래의 plan 은 지면관계상 잘려서 2줄로 나타내었음을 이해해주기 바란다.

----------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows |E-Bytes|E-Temp |
----------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |    100 |  2600 |   153M|
|*  2 |   COUNT STOPKEY               |        |      1 |        |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |   2002K|    49M|       |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |   2003K|       |       |
----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
 Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------
 19034   (1)| 00:03:49 |      9 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
            |          |      9 |00:00:00.01 |       4 |       |       |          |
  4126   (1)| 00:00:50 |      9 |00:00:00.01 |       4 |       |       |          |
   989   (1)| 00:00:12 |      9 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "E"."EMPNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7]
   2 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   4 - "E".ROWID[ROWID,10], "E"."DEPTNO"[NUMBER,22]
 

 'advanced allstats last' 포맷은 출력되는 정보가 너무많아
   'advanced allstats last' 포맷을 적용하였으므로 DBMS_XPLAN.DISPLAY_CURSOR 가 보여줄수 있는 모든
정보를 출력 하였다. 단 지면 관계상 가장 처음에 나오는 SQL TEXT 와 sql_id, child number, plan_hash_value 등은 생략하였다. 많은정보를 생략하였음에도 불구하고 일반적인 튜닝시 필요가 없는 정보가 모두 출력되고 말았다. 

 이제 위에서 정의된 각 Controller 를 이용하여 여러분만의 Format 을 만들어보자.
필자의 경우 가장 선호하는 포맷은 아래의 두가지 이다.
 
권장되는 포맷유형 2가지

1.쿼리변형이 없는 단순 쿼리 튜닝의 경우:

SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));

 포맷을 'allstats last -rows +predicate' 로 주었으므로 예측 row 수(E-row) 가 생략되고 실행통계와
Predicate Information 만을 출력한다.
 아래의 plan 또한 너무길어 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |      9 |00:00:00.01 |
|*  2 |   COUNT STOPKEY               |        |      1 |      9 |00:00:00.01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      9 |00:00:00.01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |      9 |00:00:00.01 |
-------------------------------------------------------------------------------

      -------------------------------------
       Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------
             4 |  2048 |  2048 | 2048  (0)|
             4 |       |       |          |
             4 |       |       |          |
             3 |       |       |          |
      -------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
 
깔끔하게 꼭필요한 정보만 출력 되었다.


2.쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우.

SELECT * FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));

 'allstats last -rows +alias +outline +predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
아래의 plan 도 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |      9 |00:00:00.01 |
|*  2 |   COUNT STOPKEY               |        |      1 |      9 |00:00:00.01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      9 |00:00:00.01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |      9 |00:00:00.01 |
-------------------------------------------------------------------------------

      -------------------------------------
       Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------
             4 |  2048 |  2048 | 2048  (0)|
             4 |       |       |          |
             4 |       |       |          |
             3 |       |       |          |
      -------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
  쿼리변형이 발생한경우나 뷰(혹은 인라인뷰) 등을 튜닝할경우 아주 적합한 옵션이다. 하지만 특이한경우
Column Projection Information 이 필요한경우도 있다. 이경우는 +projection 포맷을 추가해주면 된다.

  자신만의 적절한 포맷이 필요해
 출력되는 정보의 양이 너무 많으면 소화 하기가 힘들고 너무 적으면 튜닝하기가 어려워진다. SQL이 아무리 복잡하고 다양한 경우가 있더라도 2~3 가지의 Format 조합으로도 충분하다. 여러분 각자의 입맛에 맞는 Format 을 개발해보길 바란다. 물론 그러기 위해서는 각각의 Controller 들과 친해질 필요가 있다.