꿈꾸는 개발자, DBA 커뮤니티 구루비

예측정보와 실측정보 비교

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_XPLANDISPLAY	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