실제 SQL 성능 문제를 발견하고 분석, 튜닝한 사례를 통해 실전 감각을 익힙니다. 문제 원인 파악 → 실행 계획 확인 → 인덱스/쿼리 개선 → 성능 검증 순으로 접근합니다.
1. 사례 개요
환경: Oracle 19c, 10만 건 이상 주문 데이터
문제: 특정 고객 주문 조회 쿼리 응답 속도 10초 이상
원인: 하드 파싱 과다, 비효율적 인덱스, 불필요 조인
2. 문제 SQL
SELECT o.order_id, o.order_dt, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id WHERE o.customer_id = 1001 AND o.order_dt BETWEEN '2025-01-01' AND '2025-01-31';
3. 분석 과정
- 실행 계획 확인: Nested Loops 조인, 테이블 풀 스캔 발생, 파싱 횟수 높음 → CPU 과부하
- 인덱스 점검: orders.customer_id, orders.order_dt 복합 인덱스 없음, products.product_id 인덱스 존재, customers.customer_id 인덱스 존재
- 바인딩 여부 확인: 애플리케이션에서 리터럴 직접 삽입 → 커서 재사용 불가, 하드 파싱 빈도 과다
4. 개선 전략
- 바인딩 변수 적용: SELECT o.order_id, o.order_dt, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id WHERE o.customer_id = :cust_id AND o.order_dt BETWEEN :from_dt AND :to_dt;
- 인덱스 추가: CREATE INDEX idx_orders_cust_dt ON orders(customer_id, order_dt);
- 실행 계획 확인 및 튜닝: Nested Loops → Index Range Scan 활용, 테이블 풀 스캔 제거, 소프트 파싱 비율 향상
5. 결과 비교
구분 | 응답 시간(ms) | 하드 파싱 수 | CPU 사용
튜닝 전 | 10,200 | 1500 | 높음
튜닝 후 | 320 | 5 | 낮음
바인딩 변수 + 적절한 인덱스 조합으로 응답 속도 30배 이상 개선, CPU 사용량 크게 감소
6. 핵심 포인트
- 문제 원인 정확히 진단: 실행 계획, 인덱스, 바인딩 여부
- SQL 리팩토링: 불필요한 풀 스캔 제거, 조인 순서 최적화
- 인덱스 설계: 조건 컬럼 우선, 복합 인덱스 활용
- 바인딩 변수 일관 적용: 커서 공유, 하드 파싱 최소화
- 검증 및 반복: 실행 계획 확인 + 성능 측정 → 필요시 재조정
7. 실전 적용 팁
- 애플리케이션에서 PreparedStatement/파라미터화 강제
- 자주 조회되는 컬럼 기준 통계 최신화 + 히스토그램 관리
- 복잡한 JOIN 시 조인 순서와 인덱스 우선순위 점검
- 트래픽 높은 테이블은 파티셔닝 고려
8. 마무리
- 실전 사례 분석은 문제 인식 → 원인 진단 → 개선 → 검증 순으로 접근
- 한 번 튜닝으로 끝나지 않고 주기적 모니터링과 재점검 필수
- 다음 학습: 통계 정보와 수집 전략 → 옵티마이저 효율 극대화