[Oracle] 실행계획(Execution Plan) 보는 법

1. 실행계획이란?

옵티마이저가 SQL을 어떤 순서로, 어떤 방법으로 처리할지 정해놓은 것, 말 그대로 실행계획

구체적으로는 아래 세 가지를 결정한 결과물이다.

• 어떤 테이블을 먼저 읽을지 (액세스 순서)
• 각 테이블을 인덱스로 읽을지 전체를 훑을지 (액세스 방법)
• 조인을 어떤 방식으로 할지 (조인 방법)

SQL을 실행했을 때 데이터를 "어떻게 가져올지"는 내가 정하는 게 아니라 옵티마이저가 정하고, 그 결과가 실행계획이다. (힌트를 적용해 특정 방법으로 유도할 수 있지만 힌트가 없다면 옵티마이저가 정한다)

※ 옵티마이저(Optimizer): 실행계획을 만드는 DBMS 내부 엔진. Oracle은 비용 기반(CBO, Cost Based Optimizer)으로 동작하고, 통계정보를 근거로 여러 후보 계획 중 비용이 가장 적을 것 같은 걸 고른다.

 

2. 실행계획 확인하는 방법

SQL을 실제로 실행하는지와 실제 수치가 나오는지가 다르다.

방법	실행	수치	비고
EXPLAIN PLAN	X	X	예상 계획만
AUTOTRACE	O	일부 (통계)	계획 자체는 예상치
DBMS_XPLAN.DISPLAY_CURSOR	O	O	실제 수행된 계획
SQL 트레이스 + TKPROF	O	O	정보량 가장 많음, 파일 접근 필요

 

(1) EXPLAIN PLAN — 실행 X, 계획만 O

EXPLAIN PLAN FOR
SELECT * FROM EMP WHERE DEPTNO = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL을 실제로 돌리지 않기 때문에 부담이 없다.

운영 DB에서 무거운 쿼리의 계획만 보고 싶을 때 쓴다.

단점은 어디까지나 예상이라는 것. 실제로 실행됐을 때 다른 계획이 나올 수 있다.

※ PLAN_TABLE이 없다는 에러가 나면 $ORACLE_HOME/rdbms/admin/utlxplan.sql 을 실행해야 한다. (10g 이후로는 기본 제공되므로 보통은 그냥 된다)

 

(2) AUTOTRACE — 실행 O, 계획은 예상치

SQL*Plus에서 쓴다.

SET AUTOTRACE ON;                -- 결과 + 실행계획 + 통계
SET AUTOTRACE TRACEONLY;         -- 결과는 안 뿌리고 계획 + 통계만
SET AUTOTRACE TRACEONLY EXPLAIN; -- 실행 안 하고 계획만
SET AUTOTRACE OFF;

실행 후 아래처럼 Statistics가 같이 나온다.

Statistics
----------------------------------------------------------
          0  recursive calls          ← 이 SQL을 처리하려고 내부적으로 더 돌린 SQL 횟수
          0  db block gets            ← current 모드로 읽은 블록 수 (주로 DML)
          7  consistent gets          ← 일관성 모드로 읽은 블록 수 = 논리적 I/O (핵심 지표)
          0  physical reads           ← 디스크까지 가서 읽어온 블록 수 = 물리적 I/O
          0  redo size                ← 발생한 리두 로그 양(byte). 조회만 하면 0
        512  bytes sent via SQL*Net to client       ← 서버 → 클라이언트 전송량
        400  bytes received via SQL*Net from client ← 클라이언트 → 서버 전송량
          2  SQL*Net roundtrips to/from client      ← 네트워크 왕복 횟수
          0  sorts (memory)           ← 메모리에서 처리된 정렬 횟수
          0  sorts (disk)             ← 디스크까지 쓴 정렬 횟수. 0이 아니면 문제
          3  rows processed           ← 실제 처리된 행 수

여기서 중요하게 볼 것은 consistent gets 값이다.

같은 결과를 내는 SQL 두 개를 비교할 때, 실행시간은 캐시 상태에 따라 들쭉날쭉할 수 있지만

consistent gets는 안정적이라 비교하기 좋다.

※ 논리적 I/O: 버퍼 풀(메모리)에 있든 디스크에 있든 상관없이, 쿼리를 처리하면서 읽으려고 접근한 총 블록 수. 엄밀히는 db block gets + consistent gets 지만, 조회 위주 SQL은 db block gets가 거의 0이라 consistent gets만 봐도 된다.

※ 물리적 I/O (physical reads): 그 중 버퍼 풀에 없어서 실제로 디스크까지 가서 읽어온 블록 수. 캐시가 따뜻해지면 자연히 줄어들기 때문에, 튜닝의 근본 지표는 논리적 I/O 쪽이다.

※ sorts (disk) 가 1 이상이면 정렬 작업이 메모리를 넘쳐서 디스크(임시 테이블스페이스)까지 갔다는 뜻이다. 이것만으로도 쿼리가 크게 느려질 수 있다.

※ 주의: AUTOTRACE가 보여주는 실행계획도 내부적으로는 EXPLAIN PLAN 방식이다. 즉 통계는 실제 값이지만 계획 자체는 예상치다. 바인드 변수를 쓰면 실제 수행된 계획과 달라질 수 있다.

※ Statistics가 안 나오면 PLUSTRACE 롤 권한이 없는 것이다.

 

(3) DBMS_XPLAN.DISPLAY_CURSOR — 실행 O, 실제 계획 O

가장 정확한 방법. 예상치와 실제치를 나란히 보여준다.

SELECT /*+ GATHER_PLAN_STATISTICS */ *
  FROM EMP
 WHERE DEPTNO = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

GATHER_PLAN_STATISTICS 힌트를 붙여야 실제 수치가 수집된다.

ALLSTATS LAST 는 마지막 실행 기준으로 보여달라는 옵션.

이렇게 하면 아래 컬럼들이 추가로 나온다.

 

컬럼	의미
Starts	해당 오퍼레이션이 몇 번 수행됐는지
E-Rows	옵티마이저가 예상한 건수
A-Rows	실제로 나온 건수
A-Time	실제 소요시간
Buffers	논리적 I/O 블록 수

E-Rows와 A-Rows의 차이가 크면 옵티마이저가 헛다리를 짚었다는 뜻이다. 통계정보를 의심해봐야 한다.

※ V$SQL, V$SQL_PLAN 조회 권한이 필요하다.

 

(4) 툴에서 보기

• SQL Developer : F10 (실행계획), Ctrl+Enter 로 자동추적
• Orange, Toad : 대부분 F5 또는 Ctrl+E

편하긴 한데 툴마다 보여주는 방식이 조금씩 다르고, 예상 계획인지 실제 계획인지 구분이 안 될 때가 있다.

정확하게 봐야 할 때는 (3)번을 쓰는 게 낫다.

 

3. 실행계획 읽는 순서

이게 처음에 제일 헷갈리는 부분이다.

위에서부터 순서대로 실행되는 게 아니다.

규칙은 두 개다.

1. 들여쓰기가 더 깊은 것(자식)을 먼저 실행한다
2. 들여쓰기가 같은 것끼리는 위에서 아래로 실행한다

즉 안쪽부터, 위에서 아래로.

Id | Operation                     | Name
------------------------------------------------------------------------------
 0 | SELECT STATEMENT              |                  ← 최종 결과 반환 (제일 마지막)
 1 |  NESTED LOOPS                 |                  ← 2번과 4번의 결과를 조인
 2 |   TABLE ACCESS BY INDEX ROWID | EMP              ← 선행(바깥) 테이블
 3 |    INDEX RANGE SCAN           | EMP_DEPTNO_IDX   ← 가장 안쪽. 여기서 시작
 4 |   TABLE ACCESS BY INDEX ROWID | DEPT             ← 후행(안쪽) 테이블
 5 |    INDEX UNIQUE SCAN          | DEPT_PK          ← EMP 행 하나마다 반복 수행

이 계획의 실행 순서는

3 → 2 → 5 → 4 → 1 → 0

말로 풀면 이렇다.

3. EMP_DEPTNO_IDX 인덱스를 훑어서 ROWID를 얻는다
2. 그 ROWID로 EMP 테이블을 읽는다
   ↓ (여기서 나온 행 하나하나마다 아래를 반복)
5. DEPT_PK 인덱스로 부서를 찾는다
4. 그 ROWID로 DEPT 테이블을 읽는다
1. 둘을 조인한다 (NL 조인)
0. 결과를 반환한다

트리 구조로 그리면 이해가 빠르다.

        0. SELECT STATEMENT
                 │
        1. NESTED LOOPS
           ┌─────┴─────┐
     2. TABLE(EMP)  4. TABLE(DEPT)
           │             │
   3. INDEX RANGE  5. INDEX UNIQUE

부모는 자식이 다 끝나야 실행된다.

형제 중에서는 위쪽이 먼저다. (NL 조인에서 위쪽 자식이 선행 테이블, 아래쪽이 후행 테이블)

 

4. 실행계획 컬럼별 설명

-------------------------------------------------------------------------------
| Id | Operation                   | Name    | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |    3 |   114 |     2   (0)| 00:00:01
|  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |    3 |   114 |     2   (0)| 00:00:01
|* 2 |   INDEX RANGE SCAN          | IDX_EMP |    3 |       |     1   (0)| 00:00:01
-------------------------------------------------------------------------------

 

컬럼	의미
Id	오퍼레이션 번호. 앞에 * 가 붙으면 아래 Predicate Information에 해당 조건이 있다는 표시
Operation	무슨 작업을 하는지
Name	대상 객체 이름 (테이블 / 인덱스)
Rows	그 단계에서 나올 거라고 예상한 건수
Bytes	예상 데이터 크기
Cost	옵티마이저가 계산한 내부 비용 추정치
Time	예상 소요시간

여기서 중요한 것.

Cost는 초 단위가 아니고, 서로 다른 SQL끼리 비교하는 지표도 아니다.

옵티마이저가 같은 SQL의 여러 후보 계획 중 하나를 고르려고 매긴 점수일 뿐이다.

A쿼리 Cost 100, B쿼리 Cost 5000 이라고 해서 B가 50배 느린 게 아니다.

Rows랑 Time도 전부 예상치다. 통계정보가 낡았으면 이 숫자들은 그냥 틀린 값이다.

그래서 Cost보다 consistent gets / Buffers(논리적 I/O) 가 더 유효한 지표라고 볼 수 있다

 

5. (cr=... pr=...) 형태의 실행계획

실행계획 자료를 찾다 보면 이렇게 생긴 것도 자주 만난다.

Rows     Row Source Operation
-------  ------------------------------------------------------------------
     14  NESTED LOOPS  (cr=32 pr=0 pw=0 time=1250 us)
     14   TABLE ACCESS BY INDEX ROWID EMP (cr=18 pr=0 pw=0 time=430 us)
     14    INDEX RANGE SCAN EMP_DEPTNO_IDX (cr=4 pr=0 pw=0 time=120 us)
     14   TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=610 us)
     14    INDEX UNIQUE SCAN DEPT_PK (cr=7 pr=0 pw=0 time=280 us)

이건 SQL 트레이스(10046 이벤트) 결과를 TKPROF로 정리했을 때 나오는 형식이다.

앞에서 본 DBMS_XPLAN 형식과 내용은 같고 표기만 다르다.

표기 의미 DBMS_XPLAN에서는

Rows (맨 앞)	실제로 반환된 행 수	A-Rows
cr	consistent reads. 일관성 모드로 읽은 블록 수 = 논리적 I/O	Buffers
pr	physical reads. 디스크에서 읽어온 블록 수	Reads
pw	physical writes. 디스크에 쓴 블록 수	Writes
time	해당 오퍼레이션 수행 시간 (us = 마이크로초)	A-Time

읽는 순서는 앞과 똑같다. 들여쓰기 깊은 것부터, 같은 깊이면 위에서 아래로.

 

주의할 점: 이 수치들은 누적값임.

자식 오퍼레이션의 값이 부모에 그대로 포함되어 있음.

NESTED LOOPS            cr=32   ← 아래 두 갈래의 합 (18 + 14)
 TABLE ACCESS EMP       cr=18   ← 인덱스(4)를 포함한 값. 테이블 자체는 14
  INDEX RANGE SCAN      cr=4
 TABLE ACCESS DEPT      cr=14   ← 인덱스(7)를 포함한 값. 테이블 자체는 7
  INDEX UNIQUE SCAN     cr=7

그래서 어느 단계가 병목인지 보려면 자식 값을 빼서 봐야함.

(맨 위는 전체 합계 / 32 = 18 + 14)

 

트레이스 뜨는 방법

-- 1. 트레이스 시작
ALTER SESSION SET SQL_TRACE = TRUE;

-- 2. 확인할 SQL 실행
SELECT ... ;

-- 3. 트레이스 종료
ALTER SESSION SET SQL_TRACE = FALSE;

-- 4. 트레이스 파일 위치 확인
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

그다음 OS에서 TKPROF로 정리한다.

tkprof <트레이스파일> <출력파일> sys=no

DBMS_XPLAN보다 손이 많이 가는 대신, 파싱/실행/페치 단계별 시간과 대기 이벤트까지 볼 수 있어서 정보량이 제일 많다.

다만 트레이스 파일이 서버에 생기기 때문에 파일 접근 권한이 없으면 못 쓴다.

 

6. Predicate Information 

실행계획 아래쪽에 붙어 나오는 부분이다.

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=10)
       filter("SAL">3000)

 

※ access 와 filter 의 차이

access	인덱스 스캔 범위 자체를 줄여주는 조건
filter	일단 다 읽어온 뒤에 버리는 조건

filter로 빠진 조건이 많다는 건, 읽을 필요 없는 데이터를 읽고 나서 버리고 있다는 뜻이다.

실행계획에 INDEX RANGE SCAN이 찍혀 있어도 조건이 전부 filter로 빠져 있으면 인덱스를 제대로 못 타고 있는 것이다.

"인덱스를 탔는데 왜 느리지?" 의 대표적인 원인이고, 보통은 결합 인덱스의 컬럼 순서 문제다.

※ 예를 들어 WHERE 부서코드 = 'A' AND 입사일 BETWEEN ... AND ... 라는 조건이 있을 때

• INDEX(부서코드, 입사일) → 부서코드로 스캔 범위를 좁히고 그 안에서 입사일 범위만 읽는다. 둘 다 access.
• INDEX(입사일, 부서코드) → 입사일 범위 전체를 훑으면서 부서코드를 하나씩 비교해 버린다. 부서코드가 filter로 빠진다.
• 두 경우 모두 실행계획에는 똑같이 INDEX RANGE SCAN으로 찍힌다. 그래서 Predicate Information을 봐야 구분이 된다.

 

7. 자주 보이는 오퍼레이션

오퍼레이션	의미	비고
TABLE ACCESS FULL	테이블 전체 스캔	큰 테이블에서 나오면 일단 의심
TABLE ACCESS BY INDEX ROWID	인덱스에서 얻은 ROWID로 테이블 접근	건수 많으면 이게 진짜 병목
INDEX UNIQUE SCAN	유니크 인덱스 = 조건, 1건 찾고 끝	가장 가벼움
INDEX RANGE SCAN	범위 스캔	Predicate에서 access/filter 확인
INDEX FULL SCAN	인덱스 전체를 순서대로	정렬 순서 유지됨
INDEX FAST FULL SCAN	인덱스 전체를 멀티블록 I/O로	빠르지만 정렬 순서 안 지켜짐
NESTED LOOPS	NL 조인	소량 데이터 조인에 적합
HASH JOIN	해시 조인	대량 데이터 조인에 적합
MERGE JOIN	정렬 병합 조인	양쪽 다 정렬 필요
SORT ORDER BY	정렬 수행	인덱스로 없앨 수 있는지 확인
SORT AGGREGATE	집계 함수 처리
FILTER	조건에 따라 하위 단계 수행 여부 결정
VIEW	인라인뷰/서브쿼리가 별도로 수행됨	뷰 머징이 안 된 상태