[DB튜닝]DBMS_XPLAN 이용한 정보 얻기

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 들과 친해질 필요가 있다.

 

 

오라클10g의 dbms_xplan 패키지를 활용하면 튜닝에 필요한 보다 많은 정보를 얻을 수 있다.

 

1. /*+ gather_plan_statistics */ 힌트와

   dbms_xplan.display_cursor 패키지를 이용한 플랜보기
   ---------------------------------------------------------------------------------------
   - statistics_level = all 인 경우에는 Hint 불필요
   - SQL 실행 시 Row Source 레벨의 통계 정보 수집
   - E-Rows(예측 Row 수)와 A-Rows(실제 Row 수)의 비교를 통해 통계정보의 오류를

     파악할 수 있음
   - Optimizer가 얼마나 합리적인 실행 계획을 세우느냐는 Cardinality, 즉 예상 Row수의

     정확성에 달려 있음

   SQL> select /*+ gather_plan_statistics */ * from tb_test where id < 1000;
   또는
   SQL> alter session statistics_level = ALL;
   SQL> select * from tb_test where id < 1000; 

   SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
   ---------------------------------------------------------------------------------------------------------
   | Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
   ---------------------------------------------------------------------------------------------------------
   |   1 |  TABLE ACCESS BY INDEX ROWID|  TB_TEST        |      1 |      1 |   1000 |00:00:00.01 |     140 |
   |*  2 |   INDEX RANGE SCAN          |  TB_TEST_IDX    |      1 |      1 |   1000 |00:00:00.01 |      70 |
               :
   - 주요 항목 설명
     . E-Rows: 예측 Row 수
     . A-Rows: 실제 Row 수
     . A-Time: 실제 소요 시간
     . Buffers: Logical Reads

참고) dbms_xplan.display_cursor(sql_id, child_number, format)의 format 종류
   - Basic
   - Typical
   - Outline
   - All
   - Advanced
   * allstats last
   * +peeked_binds :

     예) dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +peeked_binds');

 

2. 실행중인 SQL을 SID로 찾아 PLAN 보기
   ------------------------------------
   SQL> select 'select * from TABLE(dbms_xplan.display_cursor('''||sql_id||''','||SQL_CHILD_NUMBER||')) ;'
          from  v$session
         where  sid = 4194;

   결과 : select * from TABLE(dbms_xplan.display_cursor('bqxzbkrtt26gj',0)) ; 

   -- 결과를 실행
   SQL> select * from TABLE(dbms_xplan.display_cursor('bqxzbkrtt26gj',0)) ;