Oracle Tuning 강좌
사용방법 1 1 8,885

by 강정식 DBMS_XPLAN DBMS_XPLAN.DISPLAY_CURSOR [2010.07.29]


DBMS_XPLAN.DISPLAY_CURSOR 사용방법

아래 객체에 SELECT 권한이 있어야 한다
  • - V$SESSION
  • - V$SQL_PLAN
  • - V$SQL(optional)
  • - V$SQL_PLAN_STATISTICS_ALL

PLAN_STATISTICS 정보는 다음 조건 중 하나를 만족해야 기록된다.
  • - 'STATISTICS_LEVEL' Parameter 값을 ALL로 변경한 경우
  • - '_ROWSOURCE_EXECUTION_STATISTICS' Parameter 값을 TRUE로 변경한 경우
  • - GATHER_PLAN_STATISTICS HINT를 사용할 때, 10g부터 지원

WINDOW 환경에서 SQL*PLUS를 사용할 때
  • - 이 경우에 DBMS_XPLAN.DISPLAY_CURSOR 정보를 보려면 다음의 구문 사용해야
  • - SET SERVEROUTPUT OFF;

Runtime Execution Plan 확인방법

  DBMS_XPLAN.DISPLAY_CURSOR는 이미 수행된 SQL의 PLAN을 확인할 수 있는 장점이 있다.

  이미 수행된 SQL은 V$SQL을 조회하여 확인할 수 있다

1. SQL 실행
 
SQL> SELECT *
     FROM EMP E
     WHERE E.EMPNO = 9999999
     AND E.DEPTNO = 10

no rows selected
    

2. V$SQL에서 SQL_ID 확인
 
SQL> SELECT SUBSTR(SQL_TEXT, 1, 30) SQL_TEXT,
            SQL_ID, CHILD_NUMBER
     FROM V$SQL
     WHERE SQL_TEXT LIKE 'SELECT * FROM EMP E%';

SQL_TEXT                        SQL_ID          CHILD_NUMBER
------------------------------  -------------   ------------
SELECT * FROM EMP E WHERE E     ak7vxhj055996   0
    

3. DBMS_XPLAN.DISPLAY_CURSOR 조회
 
-- V$SQL에서 조회 한 SQL_ID, CHILD_NUMBER를 입력한다.    
SQL> SELECT * 
     FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ak7vxhj055996', 0, 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    26 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    26 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_U1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
                                                                                      
Predicate Information (identified by operation id):                                   
---------------------------------------------------                                   
                                                                                      
   1 - filter("E"."DEPTNO"=10)                                                        
   2 - access("E"."EMPNO"=9999999)                                                    


Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
    

수행한 SQL이 다음의 조건을 충족하지 않았기 때문에 기록이 안됨
  • - 'STATISTICS_LEVEL' Parameter 값을 ALL로 변경한 경우
  • - '_ROWSOURCE_EXECUTION_STATISTICS' Parameter 값을 TRUE로 변경한 경우
  • - GATHER_PLAN_STATISTICS HINT를 사용할 때, 10g부터 지원

4. STATISTICS_LEVEL 파라미터 값 변경 이후 추가 수행
 
-- 세션 레벨의 파라미터 변경    
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 1번 단계의 SQL문장을 다시 실행
SQL> SELECT *
     FROM EMP E
     WHERE E.EMPNO = 9999999
       AND E.DEPTNO = 10;
       
no rows selected

-- 2번 단계의 SQL_ID 확인
SQL> SELECT SUBSTR(SQL_TEXT, 1, 30) SQL_TEXT, 
            SQL_ID, CHILD_NUMBER
     FROM V$SQL
     WHERE SQL_TEXT LIKE 'SELECT * FROM EMP E%';

SQL_TEXT                        SQL_ID          CHILD_NUMBER
------------------------------  -------------   ------------
SELECT * FROM EMP E WHERE E     ak7vxhj055996   0 
SELECT * FROM EMP E WHERE E     ak7vxhj055996   1

-- 1번의 CHILD_NUMBER 추가 확인 됨.
-- 동일한 SQL을 ‘STATISTICS_LEVEL' 값을 변경하여 수행하였기 때문에
   CHILD_NUMBER 값으로 구분
    

5. DBMS_XPLAN.DISPLAY_CURSOR 다시 조회
 
SQL> SELECT * 
     FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ak7vxhj055996', 1, 'ALLSTATS LAST'));

-- 실제 수행한 결과
---------------------------------------------------------------------------------------
|Id  | Operation                  | Name   | E-Rows | A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP    | 1      | 0      |00:00:00.01 | 4       |
|* 2 | INDEX UNIQUE SCAN          | EMP_U1 | 1      | 1      |00:00:00.01 | 3       |
---------------------------------------------------------------------------------------

-- 예측 결과
-------------------------------------------------------
|Id  | Operation                  | Name   | E-Rows |
-------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP    | 1      |
|* 2 | INDEX UNIQUE SCAN          | EMP_U1 | 1      |
-------------------------------------------------------

-- 예측 실행계획과 실제 실행계획 비교
 - A-Rows, A-Time, Buffer 추가 정보 보여줌
 - E : Estimate, A : Actual
    

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

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

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

by 참된신자 [2014.08.14 09:10:21]

감사합니다 :)

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