10장. 옵티마이저와 실행계획

SQL은 "무엇을"만 말하고 "어떻게"는 옵티마이저가 정합니다. 그 판단의 근거(통계와 cardinality)와 결과물(실행계획)을 읽을 수 있어야 튜닝이 시작됩니다.

10.1 CBO — 비용으로 계획을 고르는 옵티마이저

하나의 SQL을 실행하는 방법은 수없이 많습니다. 어느 테이블부터 읽을지, 인덱스를 탈지 풀스캔할지, 어떤 조인 방법을 쓸지… Oracle의 CBO(Cost-Based Optimizer)는 후보 계획들의 비용(cost) — 예상 I/O·CPU·메모리 사용량을 하나의 숫자로 환산한 값 — 을 계산해 가장 싼 계획을 고릅니다.

비용 계산의 원재료가 통계(statistics)입니다. 테이블의 행 수·블록 수, 컬럼의 distinct 값 개수(NDV)·최소/최대값·NULL 비율, 인덱스의 height·Clustering Factor, 그리고 값 분포가 치우친 컬럼의 히스토그램까지. 통계는 DBMS_STATS 패키지로 수집합니다.

-- 테이블 + 컬럼 + 인덱스 통계 수집 (19c 권장 기본형)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SALES_APP',
    tabname => 'ORDERS',
    cascade => TRUE   -- 인덱스 통계도 함께
  );
END;
/
-- 통계가 언제 수집됐는지 확인
SELECT table_name, num_rows, blocks, last_analyzed
FROM   user_tables WHERE table_name = 'ORDERS';

10.2 Cardinality — 플랜을 결정하는 단 하나의 숫자

Cardinality는 "이 단계에서 몇 행이 나올 것인가"라는 옵티마이저의 예측치입니다. 이 숫자가 거의 모든 것을 결정합니다 — 예상 행 수가 적으면 인덱스+NL 조인, 많으면 풀스캔+Hash 조인 쪽으로 기웁니다. 즉, cardinality 추정이 틀리면 플랜이 틀립니다. 그리고 추정이 틀리는 가장 흔한 이유가 낡은 통계입니다.

10.3 실행계획 보기 — EXPLAIN PLAN과 DBMS_XPLAN

실행계획을 보는 방법은 크게 두 가지이고, 둘의 차이를 아는 것이 중요합니다.

-- 방법 1: 예상 계획
EXPLAIN PLAN FOR
SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE  d.loc = 'DALLAS';

SELECT * FROM TABLE(DBMS_XPLAN.display);

-- 방법 2: 실제 실행 통계까지 (튜닝의 정석)
SELECT /*+ GATHER_PLAN_STATISTICS */ e.ename, d.dname
FROM   emp e JOIN dept d ON e.deptno = d.deptno
WHERE  d.loc = 'DALLAS';

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL, NULL, 'ALLSTATS LAST'));
-- NULL, NULL = "이 세션에서 방금 실행한 SQL"

10.4 실행계획 트리 읽는 법 — 들여쓰기가 곧 구조

DBMS_XPLAN 출력은 사실 트리입니다. 들여쓰기가 깊을수록 자식 노드이고, 자식이 먼저 실행되어 부모에게 행을 올려보냅니다. 읽는 규칙은 하나만 기억하면 됩니다: "위에서 아래로 내려가되, 자식이 있으면 가장 깊은 곳부터" — 같은 부모의 자식들은 위에 있는 쪽이 먼저입니다.

10.5 조인 방법 3종 — NL, Hash, Sort Merge

두 테이블을 잇는 물리적 방법은 셋뿐입니다. 옵티마이저는 cardinality와 인덱스 유무를 보고 고릅니다.

세 번째인 Sort Merge Join은 양쪽을 조인 키로 정렬한 뒤 병합하는 방식입니다. Hash Join이 등장한 이후 선택 빈도는 줄었지만, 부등호 범위 조인(t1.a BETWEEN t2.x AND t2.y 등)이나 이미 정렬된 입력에서는 여전히 유효합니다.

10.6 힌트 — 옵티마이저에게 보내는 지시서

힌트(hint)는 /*+ ... */ 주석 형태로 옵티마이저의 결정을 사람이 덮어쓰는 장치입니다.

SELECT /*+ LEADING(d) USE_NL(e) INDEX(e emp_deptno_idx) */
       e.ename, d.dname
FROM   dept d, emp e
WHERE  e.deptno = d.deptno
AND    d.loc = 'DALLAS';
-- LEADING(d): dept부터 읽어라(조인 순서)
-- USE_NL(e): emp와는 NL 조인으로
-- INDEX(e ...): emp 접근 시 이 인덱스를 써라

10.7 Bind Peeking과 Adaptive 기능 — 옵티마이저의 자기 보정

바인드 변수(:id)를 쓰면 옵티마이저는 값을 모른 채 계획을 세워야 합니다. 그래서 Oracle은 최초 하드 파싱 시점의 실제 바인드 값을 살짝 훔쳐봅니다(bind peeking). 문제는 데이터 분포가 치우친(skew) 컬럼 — 첫 실행이 "희귀한 값"이었다면 인덱스 플랜이 만들어져 캐시되고, 이후 "흔한 값"이 와도 같은 플랜을 재사용해 느려집니다.

이 부작용을 줄이기 위한 장치들이 버전을 거치며 추가됐습니다.

• Adaptive Cursor Sharing — 같은 SQL이라도 바인드 값의 선택도에 따라 커서(플랜)를 여러 개 유지하고 골라 씁니다.
• Adaptive Plan — 실행을 시작한 뒤 실제 행 수를 관찰해, 예상과 크게 다르면 실행 중에 조인 방법을 전환합니다(예: NL → Hash). 실행계획 Note 영역에 "this is an adaptive plan"으로 표시됩니다.
• 통계 피드백 — 실행에서 관찰한 실제 cardinality를 기억해 다음 파싱에 반영합니다.

✍️ 이해도 체크