[DB/Oracle] 오라클 SQL 옵티마이저

 

 

오늘은 오라클 데이터베이스에서 성능 최적화의 핵심인 조인 방식과 옵티마이저 처리 과정에 대해 정리해보려고 합니다.
Nested Loop Join, Sort-Merge Join, Hash Join 같은 물리적 조인부터, 인덱스 액세스 방식, 옵티마이저의 쿼리 처리 단계, 통계 관리까지 전부 한 자리에 모았습니다.

---

1. 논리적 조인 vs 물리적 조인

• 논리적 조인(Logical Join): SQL 레벨에서 기술하는 INNER JOIN, OUTER JOIN, SEMI JOIN, ANTI JOIN 같은 개념적 조인
• 물리적 조인(Physical Join): 옵티마이저가 실제 실행 시 선택하는 Nested Loop, Sort-Merge, Hash Join 같은 실행 알고리즘

👉 즉, 논리적 조인은 “무엇을” 나타내고, 물리적 조인은 “어떻게” 처리할지를 결정합니다.

---

2. 옵티마이저의 역할과 쿼리 처리 단계

SQL은 선언적 언어라 “무엇을”만 표현합니다. “어떻게”는 옵티마이저가 결정합니다.
오라클 옵티마이저의 쿼리 처리 단계는 다음과 같습니다.

1. Parse 단계: 문법/보안/시맨틱 검사 + 간단한 변환(Simple Transformations)
   • LIKE 'K%' → >= 'K' AND < 'L'
   • IN ↔ OR, = ANY ↔ IN, > ALL ↔ NOT EXISTS
   • BETWEEN → >= AND <= 등
2. Query Rewrite 단계:
   • View Merging: 뷰를 풀어 원본 테이블로 변환
   • Subquery Unnesting: IN/EXISTS → 조인 변환
   • OR Expansion: OR → UNION ALL
3. Optimization 단계: Access Path, Join Order, Join Method 후보 생성 후 CBO가 비용 기반으로 선택
4. QEP Generation 단계: 직렬/병렬 실행계획 생성
5. Execution 단계: 실행계획에 따라 SQL 실행

---

3. 데이터 블록 액세스 방식

디스크 블록 액세스가 가장 큰 비용입니다. 오라클은 블록 단위 I/O를 수행합니다.

 

방식	I/O 성격	특징	적합한 경우
Full Table Scan (FTS)	Multi Block I/O	테이블 전체 순차 스캔, 병렬화 가능	대량 조회, 저선택도
Index Scan (Unique/Range/Full)	Single Block I/O (랜덤)	인덱스→RowID→테이블, 소량 조회 유리	OLTP
Index Fast Full Scan (IFFS)	Multi Block I/O	인덱스만으로 처리 가능, 병렬화	커버링 인덱스
Index Skip Scan (ISS)	Single Block I/O	복합 인덱스 Leading Column 없을 때 활용 가능	Distinct 값 적을 때

 

👉 RowID 액세스가 가장 빠르며, 불필요한 블록 읽기를 줄이는 것이 핵심입니다.

---

4. Nested Loop Join (NLJ)

동작 방식

• Outer 테이블(Row 단위) → Inner 테이블 인덱스로 반복 Lookup
• 각 Row마다 종속적으로 Inner 탐색

특징

• 첫 Row 응답 빠름
• 종속적: Outer 크기에 따라 성능 좌우
• 랜덤 액세스 중심
• Inner 인덱스 없으면 비효율 (Row마다 Full Scan 발생)

비용 공식

Cost(NLJ) = Read(S) + rS × Read(B)

• S: Outer, B: Inner, rS: Outer 결과 Row 수

적합

• 소량 조회, OLTP, 고선택도 조건

주의

• Inner 인덱스 필수
• Outer 크기 커지면 성능 급락

---

5. Sort-Merge Join (SMJ)

동작 방식

1. 두 집합을 조인 키로 정렬 (메모리/PGA 활용)
2. 정렬된 집합을 병합(Merge)하며 매칭

특징

• 전체 처리형: 정렬 끝나야 첫 Row 반환
• Driving 개념 약함
• Full Scan + 정렬 비용
• CPU·TEMP 의존

비용 공식

Cost(SMJ) = Read(S)+Write(SortRuns(S)) 
          + Read(B)+Write(SortRuns(B)) 
          + Merge(S,B) + CPUSortCost(S+B)

적합

• 대량 범위 조인, DW/배치, 병렬 처리

주의

• PGA 부족 시 TEMP Spill → 성능 급락

---

6. Hash Join (HJ)

동작 방식

1. 작은 집합(Build Input) → 해시 테이블 생성
2. 큰 집합(Probe Input) → 해시 값 계산 → Build 해시 테이블 Lookup

특징

• Full Scan 위주 (인덱스 불필요)
• 조인 순서 영향 있음 (Build는 작은 집합)
• PGA 메모리 의존 (부족 시 TEMP Spill)
• 대용량 처리 최적

비용 공식

Cost(HJ) = Read(Build) + Build Hash(CPU)
         + Read(Probe) + Probe Match(CPU)

 

적합

• 대량 데이터, 인덱스 없음, DW/배치

주의

• Build 집합이 커지면 메모리 부족 발생
• Probe/Build 순서 잘못 잡으면 비효율

---

7. 실행계획 읽기

EXPLAIN PLAN FOR
SELECT e.emp_id, d.dept_name
FROM employees e
JOIN departments d
  ON e.dept_id = d.dept_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• 조인 방식: NESTED LOOPS, MERGE JOIN, HASH JOIN
• 액세스 방식: TABLE ACCESS FULL, INDEX RANGE SCAN, INDEX SKIP SCAN
• 실행 통계: 예상 vs 실제 Row 비교해 통계 정확성 점검

---

8. 튜닝 포인트

1. DBMS_STATS 최신화: 카디널리티 정확성 확보
2. 인덱스 전략: 조인 키, 고선택도 컬럼, 커버링 인덱스
3. 조인 순서 제어: LEADING, USE_NL/HASH/MERGE (최후의 수단)
4. 메모리·TEMP 관리: SMJ/HJ는 메모리 부족 시 성능 급락
5. 실행계획 검증: 실제 Row와 예상 Row 차이가 큰지 확인

---

9. NLJ/SMJ/HJ 비교 요약

항목	NLJ	SMJ	HJ
Row 첫 반환	빠름	느림(정렬 후)	보통(Build 후)
종속성	Outer 영향 큼	거의 없음	Build/Probe 영향
I/O 성격	랜덤(인덱스)	순차+정렬	순차+해시
메모리 의존	낮음	높음	높음
적합 환경	소량, OLTP	범위·대량·병렬	대용량, DW
주요 리스크	Inner 인덱스 無	Sort Spill	Hash Spill

 

10. 마무리

• 논리적 조인은 “무엇”, 물리적 조인은 “어떻게”
• 성능은 I/O 최소화, 정확한 통계, 적절한 조인 방식 선택에 달려 있습니다.
• 튜닝 순서:
  1. 실행계획 확인
  2. 통계 최신화
  3. 인덱스/쿼리 구조 개선
  4. 필요 시 힌트/메모리/TEMP 조정

👉 결국 좋은 성능은 옵티마이저가 올바른 선택을 하도록 돕는 과정에서 나옵니다.