Oracle Tuning 강좌
DBMS_XPLAN.DISPLAY vs 10046 Trace 비교 1 1 6,697

by 강정식 DBMS_XPLAN.DISPLAY 10046 Trace [2010.06.22]


예측정보와 실측정보 비교

  DBMS_XPLAN.DISPLAY는 예측정보를 제공하고, 10046 Trace는 실측 정보를 제공한다.

  예측정보와 실측정보는 예상과 실제 수행의 차이가 있기 때문에 값이 다를 수 있다.

  이 차이점은 Rows, Time 뿐만 아니라 Plan도 다르게 나올 수 있다. 그러므로 정확한 튜닝을 위해서는 10046 Trace를 이용하여 실측정보를 확인해야 한다.

  하지만 XPLAN에서는 10046 Trace에서 제공하지 않는 Predicate Information을 제공하기 때문에 10046 Trace로만 튜닝하기에는 한계가 있으므로 양쪽정보를 모두 참고해야 한다.

     
SQL> SELECT *
     FROM   EMP E
     WHERE  TRIM(E.EMPNO)  = 9999999     
       AND    E.DEPTNO       = 30;

-- 예측정보는 16,684 로우 예상하고, 1분 53초를 예상 하였다.
-------------------------------------------------------------------------- 
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      | 16684 |   293K|  9386   (4)| 00:01:53 | 
|*  1 |  TABLE ACCESS FULL| EMP  | 16684 |   293K|  9386   (4)| 00:01:53 | 
--------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("E"."DEPTNO"=30 AND TO_NUMBER(TRIM(TO_CHAR("E"."EMPNO")))=   
              9999999)                 

-- 실측정보는 0로우 액세스하고, 5.5초 수행 하였다.
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        1    2.680        5.551      33082      33299          0          0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        3    2.680        5.554      33082      33299          0          0

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      0   TABLE ACCESS FULL EMP (cr=33299 pr=33082 pw=0 time=5551325 us)
    

단일 SQL vs 다중 SQL

  DBMS_XPLAN.DISPLAY는 단일 SQL만 분석이 가능하나 Trace는 다중 SQL 분석이 가능하다.

 
-- SQL Trace는 아래와 같이 다중 SQL 분석이 가능하다.
SELECT E.JOB 
FROM
 EMP E WHERE E.DEPTNO = :B1 AND ROWNUM = 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.07         13          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.07         13          4          0           1

-- 생략

SELECT D.LOC 
FROM
 DEPT D WHERE D.DEPTNO = :B1 AND ROWNUM = 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

-- 생략
    

Function 정보

  DBMS_XPLAN.DISPLAY는 Function의 내용을 볼 수 없으나 Trace는 내용을 확인할 수 있다.

테스트 함수
 
CREATE OR REPLACE FUNCTION 
F1(p_num IN NUMBER) RETURN NUMBER IS
    v_num NUMBER;
BEGIN
    SELECT SUM(E.EMPNO)
    INTO   v_num
    FROM   EMP E
    WHERE  E.DEPTNO = p_num;
RETURN v_num;
END;
    

DBMS_XPLAN.DISPLAY 예제

  아래 실행계획에서 함수에 대한 정보를 확인 할 수 없다.

 
-- F1 함수가 사용 되었다.
SQL> EXPLAIN PLAN FOR
     SELECT D.DEPTNO,
            F1(D.DEPTNO) F_DEPTNO
     FROM   DEPT D;

-- 아래와 같이 함수에 대한 정보를 확인 할 수 없다.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |
|   1 |  TABLE ACCESS FULL| DEPT |     9 |
------------------------------------------
    

10046 Trace 예제

  10046 Trace에서는 아래와 같이 함수에 대한 정보를 확인 할 수 있다.

 
SELECT D.DEPTNO,
       F1(D.DEPTNO) F_DEPTNO
FROM   DEPT D

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.004          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2   21.710       25.440      43908     398200          0          9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4   21.710       25.445      43908     398200          0          9

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      9   TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=129 us)    
;

-- Function 안의 내용 확인 가능
-- 위 예제 F1함수의 SQL 문장을 확인 할 수 있다. 
SELECT SUM(E.EMPNO) FROM EMP E WHERE E.DEPTNO = :B1

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      9    0.000        0.004          0          0          0          0
Fetch        9   21.690       25.424      43908     398169          0          9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       19   21.690       25.429      43908     398169          0          9
    

전체 비교

항목 DBMS_XPLAN DISPLAY 10046 Trace
수행결과 예측 실측
SQL 수행 1개 1개 이상
Function 수행정보 X O
단계별 통계치
(Parse, Execute, Fetch)
X O
PLAN 정보 O O
추가 정보
(Predicate Information
Query Block
Column Projection Information
Outline Data )
O X

- 강좌 URL : http://www.gurubee.net/lecture/2132

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 참된신자 [2014.08.13 15:48:55]

감사합니다 :)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입