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

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