Chapter 3. 실행계획 | Chapter 4. 실행계획 종류

실행 계획 (Execution Plans)

SQL을 실행하기 위한 처리 순서도
Optimizer에 의해 생성되며 Shared Pool의 Library Cache에 저장
문장 실행 전 예측되는 실행 계획은 PLAN_TABLE에 저장
실제 사용된 실행 계획이 아닌 현재 시점의 예상되는 실행 계획을 PLAN_TABLE에 저장 함
$ORACLE_HOME/rdbms/admin/utlxplan.sql 파일 이용하여 PLAN_TABLE 생성 가능 (10g DB부터는 기본 제공)
DBMS_XPLAN.DISPLAY 이용하여 결과 확인

EXPLAIN PLAN FOR

아래와 같이 SQL 구문 앞에 EXPLAIN PLAN FOR 명령을 추가하게 되면 SQL문의 예상 실행계획을 생성하고 이 실행계획을
PLAN_TABLE에 INSERT하게 된다.

EXPLAIN PLAN FOR
 SELECT * FROM emp
 WHERE job = 'CLERK' ; 

이렇게 생성, 저장된 실행 계획을 확인하려면 아래 명령을 수행하면 된다.

SELECT * FROM table(dbms_xplan.display) ;

예상 실행 계획 확인 : dbms_xplan.display()

파라미터를 사용하지 않을 경우 PLAN_TABLE을 대상으로 하며, 마지막 생성된 실행 계획을 TYPICAL 포멧으로 불러온다.
아래와 같이 파라미터를 지정할 경우 지정된 실행계획을 출력한다.

SELECT * FROM table(dbms_xplan.display({실행 계획 테이블}, {PLAN_TABLE.STATEMENT_ID}, {실행 계획 포멧})); 

예상 실행 계획 <-> 실제 실행 계획

옵티마이저가 예상한 실행계획은 표본을 통해 도출한 실행 계획이며 전체를 대상으로 도출한 예상 실행 계획이라도
당시 실행계획을 도출한 상황과 현재 데이터는 크게 달라져있을 수 있으므로 실제 실행 계획과 동일시 하면 안된다.

그러므로 쿼리 튜닝과 튜닝 포인트 확인을 위해서는 실제 실행 쿼리의 실행 계획을 확인하는 것이 선행되어야 한다.

실제 실행 계획 확인 : dbms_xplan.display_cursor()

최근에 실행된 실행 계획을 검사할 때 V$SQL_PLAN 뷰를 검색하여 확인한다.
- V$SQL_PLAN 뷰만 조회하면 너무 많은 정보가 조회되어 필요한 실행계획만 확인이 어렵다.
V$SQL_PLAN_STATISTICS_ALL, V$SQL_PLAN_MONITOR 뷰를 통해 추가적인 정보 검색 가능하다.
DBMS_XPLAN.DISPLAY_CURSOR 이용하여 결과 확인

아래 패키지 함수를 사용하여 가장 최근 실행된 쿼리의 실행 계획을 확인할 수 있다.

// 파라미터를 사용하지 않으면 실제로 수행된 마지막 쿼리의 첫 번째 CURSOR_CHILD_NO 값으로 실행계획을 `TYPICAL` 포멧을 통해 가져온다.
SELECT * FROM table(dbms_xplan.display_cursor) ;

파라미터를 통해 SQL_ID, CURSOR_CHILD_NO, 실행 계획 출력 FORMAT을 지정하여 실행 계획을 확인할 수 있다.

SELECT * FROM table(dbms_xplan.display_cursor({SQL_ID}, {CURSOR_CHILD_NO}, {실행 계획 출력 FORMAT})) ;

실행 통계 확인

DBMS_XPLAN.DISPLAY_CURSOR의 출력 결과는 실제 사용된 실행 계획을 보여 주긴 하나,
그 이외의 칼럼들(Rows, Bytes, Cost, Time)의 값 등은 모두 Parse 단계에서 생성된 예상 값을 보여준다.
실제 문장의 실행 도중 사용된 자원의 사용량을 확인하기 위해서는 IOSTATS, MEMSTATS 등의 추가적인 출력 포맷을 사용한다.

// 사용 가능한 추가적 포맷
IOSTATS   I/O 사용 횟수
MEMSTATS  메모리 사용량
ALLSTATS  모든 통계 포함

하지만 모든 실행 쿼리를 대상으로 통계를 수집할 경우 서버 프로세스의 부하가 크게 가중되므로
이를 수집하기 위해서는 아래와 같은 추가 설정이 필요하다.

1. 문장 레벨의 실행 통계 수집(힌트 사용)

SELECT /*+ gather_plan_statistics */ * FROM dept
 WHERE deptno = 10;

2. 세션 레벨의 실행 통계 수집

ALTER SESSION SET statistics_level = ALL ;

3. 시스템 레벨의 실행 통계 수집

시스템 레벨의 통계 수집은 관련 모든 쿼리를 수집하므로 사용하지 않는 것을 추천

ALTER SYSTEM SET statistics_level = ALL ; 

[ALLSTAT 실행 통계 예시]

A-Time : 응답 시간 = CPU사용 시간 + CPU 경합 대기 시간

SQL_ID  9navctnqnq3qx, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */ channel_id, count(*)  from sales  
where channel_id = 9  group by channel_id
 
Plan hash value: 541818681
 
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |      1 |        |      1 |00:00:00.01 |       7 |
|   1 |  SORT GROUP BY NOSORT|                  |      1 |    229K|      1 |00:00:00.01 |       7 |
|*  2 |   INDEX RANGE SCAN   | SALES_CHANNEL_IX |      1 |    229K|   2074 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CHANNEL_ID"=9)

SQL Trace

Server Process 가 수행하는 모든 작업 내용 확인 가능
파라미터를 통해 생성되는 파일의 위치 설정 가능
실제 사용된 실행 계획 및 실행 도중 사용한 자원의 사용량 확인 가능
TKPROF 유틸리티를 이용하여 보고서 파일을 생성 (Trace 파일 자체는 가독성이 떨어지므로…)
DB Server 가 위치한 OS에 접근 권한 필요 (Server OS 에 저장되기 때문)

SQL Monitoring (11g New Features)

Oracle 튜닝 라이센스 필요
Parameter 설정 시 자동 활성화
STATISTICS_LEVEL => TYPICAL or ALL 설정
CONTROL_MANAGEMENT_PACK_ACCESS => DIAGNOSTIC+TUNING 설정
1초 간격으로 병렬로 실행되는 SQL 문장 및 5초 이상 CPU, I/O 시간을 소모하는 SQL 문장 자동 수집
MONITOR, NO_MONITOR 힌트 사용 가능
V$SQL_MONITOR, V$SQL_PLAN_MONITOR 뷰를 통해 실행 정보 확인
DBMS_SQLTUNE.REPORT_SQL_MONITOR 사용하여 리포트 가능

문장 단위 SQL Monitoring

SELECT /*+ monitor */ * FROM emp WHERE deptno = 10 ;