[Oracle SQL] 옵티마이저(Optimizer)와 실행 계획, 실전 SQL 튜닝 가이드
이 가이드에서는 오라클 데이터베이스의 핵심 엔진인 옵티마이저의 원리를 이해하고, DBeaver와 SQL Developer를 활용하여 실제 쿼리의 실행 계획을 분석 및 튜닝하는 전체 과정을 안내합니다.
Part 1: 옵티마이저(Optimizer)의 이해와 작동 원리
가장 먼저 옵티마이저가 쿼리의 '가장 빠르고 효율적인 길(실행 계획)'을 찾아내는 4단계 과정을 이해해야 합니다.
1단계: 쿼리 변환 및 후보군 생성
데이터베이스는 사용자가 작성한 SQL을 처리하기 쉬운 형태로 몰래 재작성(Query Transformation)한 뒤, 가능한 모든 조인 방식과 스캔 방식의 경우의 수(Plan Generation)를 도출합니다.
2단계: 비용 산정 및 실행 계획 선택
DB가 평소 수집해 둔 통계 정보를 바탕으로 각 경로의 예상 작업 비용(Cost)을 계산하고, 총비용이 가장 낮은 최적의 경로를 최종 실행 계획으로 확정합니다.
Part 2: 오라클 힌트(Hint) 실무 사용법
옵티마이저가 낡은 통계 정보 때문에 비효율적인 경로를 선택할 때, 개발자가 직접 올바른 길을 지시하는 '강제 명령어’입니다.
🚨 주의 사항:
FROM절에서 테이블에 별칭(Alias)을 주었다면, 힌트 안에서도 반드시 원본 테이블명이 아닌 별칭을 사용해야 적용됩니다.
1단계: 인덱스 강제 적용 (INDEX)
가장 자주 쓰이는 힌트로, 쿼리가 전체 스캔을 타고 있을 때 특정 인덱스를 타도록 강제합니다.
index_hint.sql
-- EMPLOYEES 테이블(E)의 IDX_EMP_NAME 인덱스를 사용하도록 강제
SELECT /*+ INDEX(E IDX_EMP_NAME) */
EMP_NO, EMP_NAME
FROM EMPLOYEES E
WHERE EMP_NAME = '홍길동';
#### **2단계: 테이블 전체 스캔 강제 (FULL)**
데이터량이 적거나, 조회해야 할 데이터가 전체의 15% 이상일 때는 인덱스보다 전체 스캔이 빠릅니다.
**`full_scan_hint.sql`**
SQL
– 인덱스를 타지 않고 BIG_SALES 테이블(S) 전체를 읽도록 강제
SELECT /*+ FULL(S) */
SALE_DATE, SUM(AMOUNT)
FROM BIG_SALES S
GROUP BY SALE_DATE;
#### **3단계: 조인 방식 제어 (USE_NL, USE_HASH)**
조인되는 데이터의 양에 따라 Nested Loop 조인(소량) 또는 Hash 조인(대량)을 강제합니다.
**`join_hint.sql`**
SQL
– O와 C 테이블을 대용량 처리에 유리한 해시 조인으로 연결
SELECT /*+ USE_HASH(O C) */
O.ORDER_ID, C.CUSTOMER_NAME
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUST_ID = C.CUST_ID;
### **Part 3: SQL 툴에서 실행 계획(Execution Plan) 확인**
작성한 쿼리와 힌트가 옵티마이저에게 잘 전달되었는지 확인하기 위해 실행 계획을 띄워봅니다.
#### **1단계: DBeaver에서 확인하기**
1. 실행할 쿼리를 마우스로 드래그하여 블록 지정합니다.
2. 단축키 **`Ctrl + Shift + E`** (Mac은 `Cmd + Shift + E`)를 누릅니다.
3. 하단 탭에 트리(Tree) 형태로 쿼리의 실행 순서와 Cost가 출력됩니다.
#### **2단계: SQL Developer에서 확인하기**
1. 실행할 쿼리를 클릭하거나 블록 지정합니다.
2. 단축키 `F10`을 누릅니다.
3. 하단 질의 결과 창에 '실행 계획' 탭이 열리며 상세 트리가 나타납니다.
#### **3단계: SQL 쿼리로 직접 확인하기 (툴이 없을 때)**
**`explain_plan.sql`**
SQL
– 1. 쿼리 앞에 EXPLAIN PLAN FOR를 붙여 메모리에 실행계획 생성
EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEES E WHERE DEPT_NO = 10;
– 2. 생성된 실행계획을 테이블 형태로 출력하여 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
### **Part 4: 실행 계획 읽는 법과 실전 튜닝 공식**
출력된 실행 계획 트리를 바탕으로 병목 구간을 찾고 튜닝을 진행합니다.
#### **1단계: 범인 찾기 및 실행 순서 파악**
- **순서 읽기:** 안에서 밖으로(들여쓰기가 깊은 것부터), 위에서 아래로(형제 노드 중엔 위에 있는 것부터) 읽습니다.
- **병목 찾기:** `Cost`가 비정상적으로 높거나, 대용량 테이블인데 `TABLE ACCESS FULL`(전체 스캔)이 발생하는 구간을 찾습니다.
#### **2단계: 상황 파악하기 (데이터 크기 확인)**
전체 스캔이 무조건 나쁜 것은 아닙니다. 테이블의 전체 데이터 건수와 WHERE 조건으로 필터링되어 나오는 실제 예상 추출 건수(Rows)의 비율을 확인합니다.
#### **3단계: 해결책 결정 (Index vs Full Scan)**
- **인덱스가 유리한 경우:** 조건에 맞는 데이터가 **전체 데이터의 10~15% 이하**일 때. (인덱스 힌트 부여 또는 인덱스 생성)
- **FULL SCAN이 유리한 경우:** 데이터가 몇천 건 이하로 아주 작거나, 추출 데이터가 **전체의 15% 이상**일 때.
### **Part 5: Cost(비용)의 진실과 통계 정보 갱신**
Cost 수치 자체보다 중요한 것은 예상 추출 건수와의 '가성비'입니다.
#### **1단계: 악성 쿼리 판별하기**
예상 추출 데이터(`Rows`)는 고작 5건인데, `Cost`(비용)가 50,000이라면 모래사장에서 바늘 찾기를 위해 DB 자원을 낭비하는 최악의 상태입니다. 반대로 Rows가 150만 건인데 Cost가 50,000이라면 정상적입니다.
#### **2단계: 테이블 통계 정보 수동 갱신 (최종 튜닝)**
인덱스도 정상이고 힌트도 줬는데 쿼리가 엉뚱하게 돈다면, 데이터베이스가 가진 지형도(통계 정보)가 옛날 버전일 확률이 높습니다. 이때는 DBA 권한으로 통계 정보를 최신화해줍니다.
**`gather_stats.sql`**
SQL
– XXXXX 테이블의 통계 정보를 최신 상태로 수집하여 옵티마이저에게 제공
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘계정명’, 'XXXXX ');
댓글 없음:
댓글 쓰기