[펌] DBMS_XPLAN.DISPLAY_CURSOR

[출처] http://bysql.net/index.php?document_srl=18350&mid=w201102TA

오타 많네요. 오타 수정 완료.

 

** 실행계획 중요 **

이유 :  Query Transformation의 ?수행 전 / 후 의 실행 계획을 비교 하기 때문이다.

TOOL : 일반적으로 10046 Event Trace + tkprof

=> 10046 Event Trace + tkprof  무엇인가.?

trace : explain 보다 더 세밀하게 실행 정보를 가지고 있는 툴.

tkporf : trace 설정 후 실행 결과 파일

10046 event 가 갖지 못 한 장점

1.query block name / object alias : 쿼리 블럭 정보

2.outline data : 오라클 내부 ( internal ) hint

3. predicate information : Access 조건 및 조인 조건 , Filer 조건

4.Column Projection Information: Operation id 별로 select 된 컬럼 정보

5. format : 자신에게 맞는 format 설정이 자유로움

 

이런 이유로 우리는 DBMS_EPLAN.DISPLY_CURSOR / DBMS_XPAN.DISPLAY 사용 할 것이다.

=> DBMS_XPLAN 패키지란?

  DBMS_XPLAN 패키지는 EXPLAIN PLAN 구문보다 확장된 정보를 볼 수 있다.

  Oracle 9i에서 소개되었으며 Version Up에 따라 기능이 추가 되었다. 10g부터는 실행계획은 물론 Row Source별 수행 통계까지 출력 가능 하다

DBMS_EPLAN.DISPLY_CURSOR / DBMS_XPAN.DISPLAY  는 DBMS_XPLAN 패키지 function 중의 일부이다.

 

1) 사용법

 GATHER_PLAN_STATISTICS 힌트를 사용하면 SQL_ID 대입하지 않고 실행 계획을 볼수 있다.

** GATHER_PLAN_STATISTICS 란?

GATHER_PLAN_STATISTICS 힌트는 "Row source execution" 정보를 활성화한다.
Row source execution 이란 Execute Plan에서 보는 Row수의 예측 값이 아닌 실제 실행 과정에서 추출된 Row 수를 보여주는 것을 의미한다.

실행 쿼리 예 )

SELECT /*+GATHER_P:AN_STATISTICS*/

*

FROM

(SELECT e.*

FROM employee e

WHERE e.department_id = 50

ODER BY e.employee_id)

WHERE ROWNUM <= 100;

위 sql문 실행 후 연이어 다음 Query 실행 한다

 

SELECT *

FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR (null,null,'advanced allstats last'));

-- null, null 값은 가장 최근에 실행된 쿼리를 의미한다. 만일 특정 쿼리를 지정하고 싶다면 SQL ID와 Child Number를 주면 된다.

'advanced allstats last' 은 가장 많은 정보를 나타내는 옵션이다.

 

결과는 다음과 같다.

 --------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |        |       |     4 (100)|          |       |       |          |
|*  1 |  COUNT STOPKEY                 |                   |        |       |            |          |       |       |          |
|   2 |   VIEW                         |                   |     45 |  5985 |     4  (25)| 00:00:01 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY       |                   |     45 |  3105 |     4  (25)| 00:00:01 |  6144 |  6144 | 6144  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |     45 |  3105 |     3   (0)| 00:00:01 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     45 |       |     1   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------- 

 

 

** 쿼리 실행 결과 설명 **

1) Basics 항목

id : 각 operation Id / * 가 달려있는 경우는 predicate information 에 access 및 filter 에 관한 정보를 표시

Operation : 각각 실행되는 job

Name : Operation 의 엑세스 하는 테이블 및 인덱스

 

2) Query Optimizer Estimations 항목 ( 옵티마이저의 예상치 )

E-rows  : 각 Operation이 끝났을 때 return되는 건수

E-bytes : 각 Operation 이 return 한 byte 수

E-Temp : 각 Operation 이  Temporary Space 를 사용한 양

Cost(%CPU) : 각 Operation 의 Cost.( 괄호 안의 내용은 CPU Cost 의 백분율임 이 값은 Child Operation 의 Cost 를 합친 누적 치임 )

E-Time :  수행 시간

=> 위의 모든 리스트는 예상치를 나타내는 것이다.

 

3) Runtime statistics 항목 ( 실제 수행 시간 및 실제수행 건수 )

Starts : 각 Operation 을 반복 수행한 건수 ( nested loop join 이라면 조인을 시도한 횟수가 나타남)

A-Rows : 각 Operation이 Return 한 건수

A-Time : 실제 실행 시간 0.01 초 단위 까지. 나타남 => child Operation의 A-time을 합친 값

 

4) I/O statistics ( I/O 관련하여 READ / WRITE 한 Block 수 )

Buffers : 각 Operation이 Memory 에서 읽은 수

Reads :  각 Operation이 Disk 에서 Read 한 Block 수 ( 예지 plan엔 나타내지 않음 )

Writes  : 각 Operation이 Disk 에 Write 한 Block 수  ( 예지 plan엔 나타내지 않음 )

 

5) Memory utilization Statistics ( hash 작업이나 sort 작업 시 사용한 메모리 통계 )

OMem : Optimal Execution 에 필요한 Memory

1Mem : One-pass Execution 에 필요한 Memory

O/1 M : 각 Operation 이 실행한 Optimal/One-pass /Multipass 횟수가 순서대로 표시됨.( 예지에서는 나타나지 않음 )

Used - Mem : 마지막 실행 시 사용한 PGA Memory

Used - Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space 를 대신 사용할 때 나타남. 보이는 값에 1024 를 곱해야 합. 예를 들어 32K 로 나타나면 32MB를 의미함 ( 예지에서는 나타나지 않음 )

Mas-tmp : 메모리가 부족하여 Temporary Space 를 사용할 때 최대 Temp 사용량임 . Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것임 . 보이는 값에 1024 를 곱해야 함.

 

6) 쿼리블럭 정보 : Plan 상의 Id 별로 쿼리블럭 및 Alias 정보를 출력한다.

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

1- SEL$1

2- SEL$2 / from$_subquery$_001@SEL$1

3- SEL$2

4- SEL$2 / E@SEL$2

5- SEL$2 / E@SEL$2

 

7) Outline Data : 오라클이 내부적으로 사용한 힌트를 나타낸다.

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      INDEX_RS_ASC(@"SEL$2" "E"@"SEL$2" ("EMPLOYEE"."DEPARTMENT_ID"))
      END_OUTLINE_DATA
*/

8) Predicate Information : Plan 상의 Id별로 인덱스,액세스,Filter , 조인정보 등을 나타낸다.

Predicate Information ( identified by operation id ) :

1 - filter ( ROWNUM<=100 )

2 - filter ( ROWNUM<=100 )

5 - access(``E``.``DEPARTMENT_ID``=50 )

 

9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보를 나타낸다.

Column Projection Information ( identified by operation id ) :

1 - "from$_subquery$_100"."EMPLOYEE_ID"NUMBER,22, "from$-subquery$_001"."FIRST_NAME"VARCHAR2,20,"from$_subquery$_001"."LAST_NAME"VARCHAR2,25,

"from$_subquery$_001"."EMAIL"VARCHAR2,25, "from$_subquery$_001"."PHONE_NUMBER"VARCHAR2,20,"form$_subquery$_001"."HIRE_DATE"DATE,7

"from$_subquery$_001"."JOB_ID"VARCHAR2,10,"form$_subquery$_001"."SALARY"NUMBER,22,"from$_subquery$_001"."MANAGER_ID"NUMER,22,

2-"from$_SUBQUERY$_001"."employee_id"number,22

 

'advanced allstats last' 포맷은 출력되는 정보가 너무 많다.

>> DBMS_XPLAN.DISPAY_CURSOR 는 모든 정보를 출력

>> 튜닝 시 필요 없는 정보까지 출력

>> IT 핵심인 단순함을 추구하기가 힘듦 

 

*** 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화 하라***

SELECT *

FROM

TABLE(DBMS_XPAN.DISPLAY_CURSOR(null,null,'allstats last -rows +predicate'));

 

'allstats last -rows +predicate'  => Row 수 (E-row) 가 생략되고 실행 통계와 predicate Information만 출력

 

***쿼리변형이 발생하거나 복잡한 쿼리 튜닝 시 쿼리블럭과 힌트정보를 추가로 출력하라***

SELECT *

FROM

TABLE(DBMS_XPAN.DISPLAY_CURSOR(null,null,'allstats last -rows +alias +outline +predicate'));

'allstats last -rows +alias +outline +predicate' => Query Block Name / Object Alias 정보  / Outline Data정보가 추가 출력

+alias  => 쿼리블럭을 추가

 +outline => 오라클 내부적으로 사용한 힌트 정보 출력

쿼리변형이 발생한 경우나 뷰(혹은 인라인뷰)등을 튜닝할 경우 아주 적합한 옵션

다른 특이한 경우 +projection 포맷 추가 => Column Projection Information 이 필요한 경우

 

***자신만의 적절한 포맷이 필요해***

출력되는 정보의 양이 너무 많으면 소화하기 힘들다

반대로 정보가 너무 적어도 어려워진다.

그러나 일반적으로 SQL 아무리 복잡하고 다양한 경우가 있더라도 위에 경우처럼 5~6 가지의 포맷 조합으로도 충분하다.