검색 여닫기
메뉴 여닫기
46
0
9
2천
DB스터디
개인 메뉴 토글
로그인하지 않음
만약 지금 편집한다면 당신의 IP 주소가 공개될 수 있습니다.

SQL 튜닝 입문

DB스터디
다른 명령
ORACLE SQL TUNING · FOUNDATION COURSE
SQL 튜닝 입문
SQL이 처리되는 원리부터 실행계획 읽기, 인덱스·조인 튜닝, 진단 도구까지
Oracle 기반으로 처음 시작하는 사람을 위한 단계별 가이드
난이도 · 입문학습기간 · 12주기준 DBMS · Oracle 19c선수 학습 · SQL 기초, 데이터 모델링 입문
STEP 1
처리 원리
아키텍처, 파싱, 옵티마이저, 블록 I/O
STEP 2
실행계획 읽기
DBMS_XPLAN, 실측 통계, 읽는 순서
STEP 3
인덱스 · 조인
액세스 패턴, NL · Hash · Sort Merge
STEP 4
진단 · 실전
AWR · ASH · SQL Monitor, 튜닝 사례

1. SQL 튜닝이란

SQL 튜닝은 같은 결과를 내는 SQL이 더 적은 자원(I/O, CPU, 메모리, 대기)으로 처리되도록 SQL·인덱스·통계·구조를 개선하는 작업이다.

튜닝의 본질 — "읽지 않아도 되는 블록을 읽지 않게 하는 것"
대부분의 느린 SQL은 CPU가 부족해서가 아니라 필요 이상으로 많은 블록을 읽거나, 같은 블록을 반복해서 읽기 때문에 느리다.

1.1 튜닝의 대상과 순서

우선순위 대상 효과 예
1 설계 (모델) 매우 큼 잘못된 식별자, 과도한 반정규화, 이력 구조
2 SQL·애플리케이션 큼 불필요한 반복 호출, 비효율 조인, 바인드 변수 미사용
3 인덱스·통계 큼 조건에 맞는 인덱스, 정확한 옵티마이저 통계
4 저장 구조 중간 파티셔닝, 클러스터링 팩터, 압축
5 인스턴스·OS 제한적 SGA/PGA 크기, 파라미터, 스토리지
※ 하드웨어 증설이나 파라미터 조정은 마지막 수단이다. 비효율 SQL 하나가 시스템 전체 부하의 대부분을 차지하는 경우가 흔하다.

1.2 응답 시간 공식

응답 시간 (Response Time) = 서비스 시간 (CPU Time) + 대기 시간 (Wait Time)

  • 튜닝은 둘 중 어느 쪽이 큰가를 먼저 확인하는 데서 시작한다.
  • 대기 시간은 대기 이벤트(Wait Event)로 구분된다 — db file sequential read, db file scattered read, direct path read, enq: TX - row lock contention, log file sync 등

2. Oracle 아키텍처 핵심

2.1 튜닝에 필요한 최소 구조

  ┌──────────────────────── SGA (공유 메모리) ────────────────────────┐
  │  ┌───────────────┐   ┌────────────────────┐   ┌──────────────┐  │
  │  │ Shared Pool   │   │ Database Buffer    │   │ Redo Log     │  │
  │  │ - Library     │   │ Cache              │   │ Buffer       │  │
  │  │   Cache (SQL) │   │ (데이터 블록 캐시) │   │              │  │
  │  │ - Dictionary  │   │                    │   │              │  │
  │  └───────────────┘   └────────────────────┘   └──────────────┘  │
  └──────────────────────────────────────────────────────────────────┘
        ▲ 파싱/실행계획 공유       ▲ Logical Read            │ LGWR
        │                          │                         ▼
  ┌─────┴─────┐  PGA (정렬·해시) ┌─┴───────────┐       ┌───────────┐
  │ Server    │─────────────────►│ Data Files  │       │ Redo Logs │
  │ Process   │  Physical Read   └─────────────┘       └───────────┘
  └───────────┘
구성 요소 튜닝 관점의 의미
Library Cache 파싱된 SQL과 실행계획 보관. 재사용되면 Soft Parse, 없으면 Hard Parse
Buffer Cache 블록을 메모리에서 읽으면 Logical Read, 디스크에서 읽으면 Physical Read
PGA 정렬(Sort), 해시 조인(Hash Area) 작업 공간. 부족하면 Temp 사용 (direct path write temp)
Redo 변경 기록. 커밋 빈도가 과도하면 log file sync 대기 증가

2.2 블록 I/O 단위

  • Oracle은 행 단위가 아니라 블록 단위(보통 8KB)로 읽는다. 한 행만 필요해도 블록 전체를 읽는다.
  • Single Block I/O — 인덱스 탐색, 테이블 랜덤 액세스 (db file sequential read)
  • Multi Block I/O — Full Table Scan, Index Fast Full Scan (db file scattered read, direct path read)
  • 튜닝의 핵심 지표는 Logical Read (= buffer gets, consistent gets + db block gets)이다. Physical Read는 캐시 상태에 따라 달라지지만 Logical Read는 SQL과 실행계획이 같으면 거의 일정하다.

3. SQL 처리 과정

① PARSE
파싱
문법·권한 검사
Library Cache 탐색
➜
② OPTIMIZE
최적화
통계 기반 비용 계산
실행계획 선택
➜
③ ROW SOURCE
실행 엔진 생성
실행 가능한
코드로 변환
➜
④ EXECUTE / FETCH
실행·인출
블록 읽기
결과 전송

3.1 Hard Parse vs Soft Parse

구분 동작 비용
Hard Parse Library Cache에 없음 → 최적화·실행계획 생성 높음 (CPU, Shared Pool 래치/뮤텍스 경합)
Soft Parse Library Cache에 있음 → 기존 실행계획 재사용 낮음

3.2 바인드 변수

-- 나쁜 예: 리터럴 → 값마다 다른 SQL로 인식되어 매번 Hard Parse
SELECT * FROM TB_ORDER WHERE ORDER_NO = 'O20261104001';
SELECT * FROM TB_ORDER WHERE ORDER_NO = 'O20261104002';

-- 좋은 예: 바인드 변수 → 하나의 SQL로 공유
SELECT * FROM TB_ORDER WHERE ORDER_NO = :order_no;
# Python (oracledb / cx_Oracle) — 바인드 변수 사용
cur.execute("SELECT * FROM TB_ORDER WHERE ORDER_NO = :order_no", order_no=v_order_no)

# 대량 처리 — executemany로 Array 처리 (네트워크 왕복·파싱 최소화)
cur.executemany("INSERT INTO TB_LOG (ID, MSG) VALUES (:1, :2)", rows)

💡 바인드 변수의 그림자 — 값 분포가 심하게 치우친 컬럼(예: 상태코드 '완료' 99%, '오류' 1%)은 하나의 실행계획이 모든 값에 최적이 아니다. Oracle은 Bind Peeking과 Adaptive Cursor Sharing(11g+)으로 이를 보완한다. 이런 컬럼에는 히스토그램 통계가 중요하다.

4. 옵티마이저와 통계

4.1 비용 기반 옵티마이저 (CBO)

CBO는 통계 정보를 이용해 가능한 실행계획들의 예상 비용(Cost)을 계산하고 가장 낮은 것을 선택한다.

개념 의미 실행계획 컬럼
선택도 (Selectivity) 조건을 만족하는 행의 비율 (0~1) —
카디널리티 (Cardinality) 예상 결과 행 수 = 총 행 수 × 선택도 Rows (E-Rows)
비용 (Cost) 예상 I/O + CPU를 단일 블록 I/O 단위로 환산한 값 Cost

⚠ 튜닝의 출발점 — 실행계획이 나쁜 원인의 대부분은 카디널리티 추정 오류다. 예상 행 수(E-Rows)와 실제 행 수(A-Rows)가 수십 배 이상 차이 나는 단계를 찾으면 원인이 보인다.

4.2 옵티마이저 통계

대상 주요 항목 딕셔너리 뷰
테이블 행 수(NUM_ROWS), 블록 수, 평균 행 길이 DBA_TAB_STATISTICS
컬럼 Distinct 값 수(NDV), NULL 수, 최소/최대값, 히스토그램 DBA_TAB_COL_STATISTICS
인덱스 높이(BLEVEL), 리프 블록 수, 클러스터링 팩터 DBA_IND_STATISTICS
시스템 CPU 속도, I/O 성능 SYS.AUX_STATS$ 
-- 테이블 통계 수집 (19c 기본 권장값 사용)
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'APP', tabname => 'TB_ORDER', cascade => TRUE);

-- 통계 확인
SELECT table_name, num_rows, blocks, last_analyzed, stale_stats
  FROM dba_tab_statistics
 WHERE owner = 'APP' AND table_name = 'TB_ORDER';

-- 컬럼 통계와 히스토그램
SELECT column_name, num_distinct, num_nulls, histogram
  FROM dba_tab_col_statistics
 WHERE owner = 'APP' AND table_name = 'TB_ORDER';

5. 실행계획 읽기

5.1 실행계획 확인 방법

방법 특징 실제 실행 여부
EXPLAIN PLAN + DBMS_XPLAN.DISPLAY 예상 계획. 바인드 타입·값 미반영으로 실제와 다를 수 있음 ✕
SET AUTOTRACE TRACEONLY (SQL*Plus) 예상 계획 + 실행 통계(consistent gets 등) ○
DBMS_XPLAN.DISPLAY_CURSOR 실제 사용된 계획 + 단계별 실측(A-Rows, Buffers) ○ (권장)
SQL Trace + TKPROF 이벤트 10046, 대기 이벤트까지 상세 추적 ○
Real-Time SQL Monitor 장시간·병렬 SQL의 진행 상황 실시간 확인 (Tuning Pack 라이선스) ○ 
-- 실측 실행계획 확인 (가장 많이 쓰는 방법)
SELECT /*+ GATHER_PLAN_STATISTICS */ o.order_no, c.cust_nm
  FROM tb_order o
  JOIN tb_customer c ON c.cust_no = o.cust_no
 WHERE o.order_dt >= DATE '2026-11-01';

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

-- 특정 SQL_ID 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

5.2 실행계획 예시와 읽는 순서

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |    120 |     375 |
|   1 |  NESTED LOOPS                 |              |      1 |    118 |    120 |     375 |
|   2 |   NESTED LOOPS                |              |      1 |    118 |    120 |     255 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TB_ORDER     |      1 |    118 |    120 |      15 |
|*  4 |     INDEX RANGE SCAN          | IX_ORDER_02  |      1 |    118 |    120 |       3 |
|*  5 |    INDEX UNIQUE SCAN          | PK_CUSTOMER  |    120 |      1 |    120 |     240 |
|   6 |   TABLE ACCESS BY INDEX ROWID | TB_CUSTOMER  |    120 |      1 |    120 |     120 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
   4 - access("O"."ORDER_DT">=TO_DATE(' 2026-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("C"."CUST_NO"="O"."CUST_NO")
읽는 원칙 설명
안쪽(들여쓰기 깊은 곳)부터 가장 깊이 들여쓴 단계가 먼저 실행된다 (4 → 3)
같은 깊이면 위에서 아래로 형제 단계 중 위쪽이 먼저 (NL 조인의 Outer → Inner)
부모는 자식 결과를 받는다 3·5 결과를 2(NL)가, 2·6 결과를 1(NL)이 결합
컬럼 의미 체크 포인트
Starts 해당 단계가 실행된 횟수 NL 조인 Inner 쪽은 Outer 행 수만큼 반복
E-Rows 옵티마이저 예상 행 수 (1회 실행당) E-Rows × Starts 와 A-Rows 비교
A-Rows 실제 행 수 (전체 누적) 예상과 큰 차이 → 통계·조건 확인
Buffers 읽은 블록 수 (누적, Logical Read) 가장 크게 증가하는 단계가 튜닝 대상
Predicate access: 인덱스 탐색 범위 결정 / filter: 읽은 후 걸러냄 filter로 많이 버려지면 비효율

6. 인덱스 튜닝

6.1 B*Tree 인덱스 구조

                    [ Root ]
                   /        \
            [ Branch ]    [ Branch ]
            /    |    \        ...
      [Leaf]  [Leaf]  [Leaf]  ← 키 값 + ROWID, 정렬되어 양방향 연결
         │
         ▼  ROWID로 테이블 블록 접근 (Random Single Block I/O)
     [ Table Block ]
  • 인덱스 탐색 비용 = 수직 탐색(Root→Leaf) + 수평 스캔(Leaf 범위) + 테이블 랜덤 액세스
  • 일반적으로 테이블 랜덤 액세스가 가장 비싸다 → 인덱스에서 최대한 걸러내야 한다

6.2 인덱스 스캔 방식

방식 동작 발생 조건
Index Unique Scan 정확히 1건 탐색 UNIQUE/PK 전체 컬럼 = 조건
Index Range Scan 범위 탐색 (가장 일반적) 선두 컬럼 조건 존재
Index Full Scan 리프 전체를 순서대로 Single Block 읽기 정렬 대체, 선두 컬럼 조건 없음
Index Fast Full Scan 인덱스 전체를 Multi Block으로 읽기 (순서 무관) 필요한 컬럼이 모두 인덱스에 있음
Index Skip Scan 선두 컬럼을 건너뛰며 탐색 선두 컬럼 NDV가 매우 적을 때
Table Access By Index ROWID 인덱스 ROWID로 테이블 접근 인덱스에 없는 컬럼 필요

6.3 인덱스를 사용하지 못하는 경우

원인 나쁜 예 개선
인덱스 컬럼 가공 WHERE TO_CHAR(ORDER_DT,'YYYYMMDD') = '20261104' WHERE ORDER_DT >= DATE '2026-11-04' AND ORDER_DT < DATE '2026-11-05'
묵시적 형변환 WHERE CUST_NO = 1001 (CUST_NO는 VARCHAR2) WHERE CUST_NO = '1001'
부정형 조건 WHERE STAT_CD <> '90' 긍정형 IN 목록으로 변환 검토
앞쪽 와일드카드 WHERE CUST_NM LIKE '%철수' 업무 요건 재검토, Oracle Text 검토
NULL 검색 WHERE RETURN_DT IS NULL (단일 컬럼 인덱스) 결합 인덱스에 상수 추가 (RETURN_DT, 0)
선두 컬럼 누락 인덱스 (CUST_NO, ORDER_DT)에 WHERE ORDER_DT = ...만 인덱스 컬럼 순서 재설계
OR 조건 WHERE A = :1 OR B = :2 UNION ALL 분리 또는 OR-Expansion 확인

6.4 클러스터링 팩터 (Clustering Factor)

  • 인덱스 순서와 테이블 데이터 저장 순서가 얼마나 일치하는가를 나타내는 지표
  • 테이블 블록 수에 가까울수록 좋고, 행 수에 가까울수록 나쁘다
  • 같은 건수를 읽어도 클러스터링 팩터가 나쁘면 테이블 랜덤 I/O가 폭증한다
SELECT i.index_name, i.clustering_factor, t.blocks, t.num_rows
  FROM dba_indexes i JOIN dba_tables t
    ON t.owner = i.table_owner AND t.table_name = i.table_name
 WHERE i.table_owner = 'APP' AND i.table_name = 'TB_ORDER';

7. 조인 튜닝

7.1 세 가지 조인 방식

구분 Nested Loops Hash Join Sort Merge Join
동작 Outer 행마다 Inner를 인덱스로 탐색 작은 집합으로 해시 테이블(Build) → 큰 집합(Probe) 비교 양쪽을 조인 키로 정렬 후 병합
적합한 경우 소량 결과, OLTP, 부분 범위 처리 대량 데이터, 배치·DW, 조인 키 인덱스 없음 이미 정렬된 경우, 비동등(<, >) 조인
핵심 조건 Inner 쪽 조인 컬럼에 인덱스 Build 집합이 작아서 PGA에 들어갈 것 정렬 비용 감당 가능
조인 조건 모든 조건 = 조인만 비동등 조인 가능
힌트 USE_NL USE_HASH USE_MERGE

7.2 조인 순서가 성능을 결정한다

-- 조건: 특정 고객(1명)의 최근 주문
-- 좋은 순서: 고객(1건) → 주문(인덱스 CUST_NO+ORDER_DT) → NL
SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o IX_ORDER_01) */
       c.cust_nm, o.order_no, o.order_dt
  FROM tb_customer c
  JOIN tb_order    o ON o.cust_no = c.cust_no
 WHERE c.cust_no  = :cust_no
   AND o.order_dt >= ADD_MONTHS(SYSDATE, -1);
  • NL 조인에서 Outer(Driving) 집합은 결과 건수가 가장 적은 쪽을 선택한다.
  • Hash 조인에서 Build 집합은 작은 쪽을 선택한다 (SWAP_JOIN_INPUTS로 조정).

7.3 서브쿼리

  • 옵티마이저는 서브쿼리를 Unnesting(조인으로 변환)하거나 Filter(메인 행마다 실행, 결과 캐싱)로 처리한다.
  • 스칼라 서브쿼리는 메인 쿼리 행 수만큼 수행될 수 있다 → 입력값 종류가 많으면 조인으로 변환 검토
  • EXISTS / IN은 Semi Join, NOT EXISTS / NOT IN은 Anti Join으로 변환된다 (NOT IN은 NULL 처리에 주의)

8. 힌트 (Hint) 기초

⚠ 힌트는 옵티마이저의 판단을 강제한다. 데이터가 변하면 오히려 독이 될 수 있다. 먼저 통계·인덱스·SQL 구조로 해결하고, 힌트는 원인을 이해한 뒤 최소한으로 사용한다. 운영 환경에서 계획 고정이 필요하면 SQL Plan Baseline(SPM)을 검토한다.

분류 힌트 의미
최적화 목표 ALL_ROWS 전체 결과 처리량 최적화 (기본)
FIRST_ROWS(n) 첫 n건 응답 속도 최적화 (화면 페이징)
액세스 FULL(t) Full Table Scan
INDEX(t idx) 지정 인덱스 사용
INDEX_FFS(t idx) Index Fast Full Scan
조인 순서 LEADING(t1 t2 …) 조인 순서 지정
ORDERED FROM 절 순서대로 조인
조인 방식 USE_NL(t) NL 조인
USE_HASH(t) Hash 조인
SWAP_JOIN_INPUTS(t) Hash 조인 Build 집합 지정
쿼리 변환 UNNEST / NO_UNNEST 서브쿼리 Unnesting 제어
MERGE / NO_MERGE 뷰 병합 제어
진단 GATHER_PLAN_STATISTICS 실측 통계 수집 (DISPLAY_CURSOR용)
  • 테이블에 별칭(Alias)을 쓰면 힌트에도 반드시 별칭을 써야 한다. 잘못된 힌트는 오류 없이 무시된다.

9. 진단 도구

9.1 문제 SQL 찾기

-- 메모리(Shared Pool)에 있는 SQL 중 Logical Read 상위
SELECT *
  FROM (SELECT sql_id, executions,
               buffer_gets,
               ROUND(buffer_gets / NULLIF(executions,0)) AS gets_per_exec,
               ROUND(elapsed_time / NULLIF(executions,0) / 1000, 1) AS ms_per_exec,
               SUBSTR(sql_text, 1, 80) AS sql_text
          FROM v$sql
         WHERE parsing_schema_name NOT IN ('SYS','SYSTEM')
         ORDER BY buffer_gets DESC)
 WHERE ROWNUM <= 20;

9.2 도구별 용도

도구 범위 언제 쓰나 라이선스
V$SQL, V$SESSION 현재 지금 느린 SQL·세션 확인 기본
DBMS_XPLAN SQL 실행계획·실측 통계 기본
SQL Trace + TKPROF 세션·SQL Parse/Execute/Fetch 단계별 시간, 대기 이벤트 상세 기본
AWR 리포트 인스턴스 (스냅샷 구간) 특정 시간대 시스템 부하·Top SQL·대기 이벤트 Diagnostics Pack
ASH (V$ACTIVE_SESSION_HISTORY) 세션 (1초 샘플링) 짧은 순간의 장애·경합 원인 Diagnostics Pack
SQL Monitor SQL 5초 이상·병렬 SQL 진행 상황 Tuning Pack
SQL Tuning Advisor SQL 인덱스·프로파일 권고 Tuning Pack
Statspack 인스턴스 AWR 라이선스가 없을 때 대안 기본 (무료) 
-- SQL Trace (10046 이벤트, 대기·바인드 포함)
ALTER SESSION SET tracefile_identifier = 'TUNE01';
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
-- ... 대상 SQL 실행 ...
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;

-- OS에서
-- $ tkprof ORCL_ora_12345_TUNE01.trc tune01.txt sys=no sort=exeela,fchela

10. 튜닝 절차

① 식별
AWR·V$SQL로
대상 SQL 선정
② 측정
실측 실행계획
Buffers·시간 기록
③ 분석
E-Rows vs A-Rows
병목 단계 찾기
④ 개선
SQL·인덱스·통계
하나씩 변경
⑤ 검증
결과 동일성
전후 수치 비교
⑥ 적용·감시
배포 후
회귀 모니터링

💡 전후 비교는 숫자로 — "빨라졌다"가 아니라 Buffers 125,400 → 380, Elapsed 4.2s → 0.03s, Executions/일 30만 건 처럼 Logical Read와 실행 횟수로 기록한다. 응답 시간은 캐시 상태에 따라 흔들리지만 Logical Read는 재현된다.

11. 튜닝 사례 맛보기

사례 1. 컬럼 가공으로 인한 Full Scan 

-- Before : TABLE ACCESS FULL TB_ORDER, Buffers 182,330
SELECT order_no, cust_no FROM tb_order
 WHERE TO_CHAR(order_dt, 'YYYYMMDD') = :ymd;

-- After  : INDEX RANGE SCAN IX_ORDER_02 (+ Partition Pruning), Buffers 42
SELECT order_no, cust_no FROM tb_order
 WHERE order_dt >= TO_DATE(:ymd, 'YYYYMMDD')
   AND order_dt <  TO_DATE(:ymd, 'YYYYMMDD') + 1;

사례 2. 인덱스 컬럼 순서 — filter로 버려지는 행

  • 조건: WHERE ORDER_STAT_CD = '10' AND ORDER_DT BETWEEN :d1 AND :d2
  • 기존 인덱스 (ORDER_DT, ORDER_STAT_CD) → 범위 조건이 선두라 기간 내 모든 상태를 스캔 후 filter
  • 개선 인덱스 (ORDER_STAT_CD, ORDER_DT) → = 조건 선두, 범위 조건 후행으로 스캔 범위 최소화
  • 결과: 인덱스 Buffers 12,840 → 95

사례 3. 애플리케이션 반복 호출 (Row-by-Row) 

# Before: 고객 1만 명 → SQL 1만 번 호출 (네트워크 왕복 + Parse/Execute 반복)
for cust_no in cust_list:
    cur.execute("SELECT SUM(order_amt) FROM tb_order WHERE cust_no = :c", c=cust_no)

# After: 집합 처리 1번
cur.execute("""
    SELECT cust_no, SUM(order_amt)
      FROM tb_order
     WHERE order_dt >= :d
     GROUP BY cust_no""", d=v_from_dt)
  • 튜닝 효과가 가장 큰 영역은 종종 SQL 내부가 아니라 호출 방식이다.

12. 학습 커리큘럼 (12주)

학습 대상 SQL을 작성할 줄 아는 개발자·신입 DBA  |  주당 학습 이론 3시간 + 실습 3시간  |  목표 실행계획을 읽고 병목을 찾아 인덱스·SQL 수준에서 개선할 수 있다

주차 단계 학습 주제 세부 내용 실습 / 산출물
1 기초
처리 원리 		튜닝 개요와 아키텍처 응답시간 = CPU + Wait, SGA/PGA, 백그라운드 프로세스, 블록·익스텐트·세그먼트 실습 환경 구성, 샘플 스키마 대량 데이터 생성
2 SQL 처리 과정 Parse·Execute·Fetch, Hard/Soft Parse, Library Cache, 바인드 변수, Array 처리 리터럴 vs 바인드 Parse 횟수 비교
3 I/O 메커니즘 Logical/Physical Read, Single/Multi Block I/O, Buffer Cache, 주요 대기 이벤트 AUTOTRACE로 consistent gets 측정
4 분석
옵티마이저 		옵티마이저와 통계 CBO, 선택도·카디널리티·비용, DBMS_STATS, 히스토그램, 동적 샘플링 통계 유무에 따른 계획 변화 관찰
5 실행계획 읽기 DBMS_XPLAN 포맷, 읽는 순서, Predicate(access/filter), E-Rows vs A-Rows 실행계획 10개 해석 연습
6 핵심
인덱스·조인 		인덱스 튜닝 ① B*Tree 구조, 스캔 방식, 인덱스 사용 불가 패턴, 클러스터링 팩터 컬럼 가공·형변환 SQL 개선
7 인덱스 튜닝 ② 결합 인덱스 컬럼 순서, 커버링 인덱스, 테이블 랜덤 액세스 최소화, 인덱스 설계 절차 핵심 SQL 기반 인덱스 설계서
8 조인 튜닝 NL·Hash·Sort Merge 원리, 조인 순서, Semi/Anti 조인, 힌트 기초 조인 방식별 Buffers 비교
9 응용
SQL 구조 		쿼리 변환과 서브쿼리 Unnesting, View Merging, 조건 Pushdown, 스칼라 서브쿼리 캐싱 서브쿼리 → 조인 재작성
10 정렬·페이징·대량 처리 Sort 연산 제거, Top-N·페이징 쿼리, 부분 범위 처리, 파티션 프루닝, 병렬 처리 기초 화면 페이징 SQL 튜닝
11 실전
진단·사례 		진단 도구 SQL Trace/TKPROF, AWR 리포트 읽기, ASH, SQL Monitor, SPM 개요 AWR 리포트 Top SQL 분석 보고서
12 종합 튜닝 실습 문제 SQL 선정 → 측정 → 분석 → 개선 → 검증 → 문서화 튜닝 보고서 (전후 Buffers·시간 비교) 발표

12.1 단계별 학습 목표

🟥 1~3주 · 기초

  • SQL이 처리되는 과정을 설명한다
  • 바인드 변수의 필요성을 이해한다
  • Logical Read로 SQL 비용을 측정한다

🟧 4~5주 · 분석

  • 통계가 실행계획에 미치는 영향을 안다
  • 실행계획을 순서대로 읽는다
  • 카디널리티 오류 단계를 찾는다

🟦 6~8주 · 핵심

  • 인덱스를 못 타는 SQL을 고친다
  • 조건에 맞는 결합 인덱스를 설계한다
  • 상황에 맞는 조인 방식을 선택한다

🟪 9~12주 · 응용·실전

  • 쿼리 변환을 이해하고 SQL을 재작성한다
  • AWR·Trace로 문제 SQL을 찾는다
  • 튜닝 결과를 수치로 보고한다

12.2 실습 환경

구분 권장 비고
DB Oracle Database 23ai Free, 사내 개발 DB (19c) 운영 DB에서 실습 금지
클라이언트 SQL*Plus, SQL Developer, Toad, Orange DBMS_XPLAN 출력은 SQL*Plus가 가장 깔끔
샘플 데이터 SH / HR 샘플 스키마, CONNECT BY LEVEL로 수백만 건 생성 데이터가 적으면 튜닝 효과가 보이지 않는다
권한 SELECT_CATALOG_ROLE, ALTER SESSION, V$ 뷰 조회 권한 DISPLAY_CURSOR 사용에 필요 
-- 실습용 대량 데이터 생성 예 (약 500만 건)
CREATE TABLE tb_order_lab NOLOGGING AS
SELECT 'O' || LPAD(LEVEL, 10, '0')                          AS order_no,
       DATE '2024-01-01' + MOD(LEVEL, 1000)                AS order_dt,
       'C' || LPAD(MOD(LEVEL, 50000), 6, '0')               AS cust_no,
       DECODE(MOD(LEVEL, 100), 0, '90', 1, '30', '10')     AS order_stat_cd,
       ROUND(DBMS_RANDOM.VALUE(1000, 500000))               AS order_amt
  FROM dual CONNECT BY LEVEL <= 5000000;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TB_ORDER_LAB');

12.3 연계 자격증 · 추천 학습

  • SQLP (SQL 전문가) — 3과목 'SQL 고급 활용 및 튜닝'이 본 과정과 직결
  • Oracle Certified Professional — Database Administration / Performance Management and Tuning 계열
  • Oracle 공식 문서 SQL Tuning Guide, Database Performance Tuning Guide (19c)

13. 튜닝 체크리스트

✅ SQL 리뷰 시 확인할 항목 (펼치기)

영역 점검 항목
작성 □ 바인드 변수를 사용하는가 (리터럴 SQL 없음)
□ 인덱스 컬럼을 가공하거나 형변환하지 않는가
□ 반복 호출(Row-by-Row) 대신 집합 처리·Array 처리를 하는가
실행계획 □ 실측 계획(ALLSTATS LAST)으로 확인했는가
□ E-Rows와 A-Rows가 크게 다른 단계는 없는가
□ Buffers가 급증하는 단계, filter로 대량 버려지는 단계는 없는가
인덱스 □ 조건에 맞는 결합 인덱스 순서인가 (= 조건 선두)
□ 테이블 랜덤 액세스 후 버려지는 행이 많지 않은가
조인 □ Driving 집합이 가장 작은 쪽인가
□ NL Inner 쪽에 조인 인덱스가 있는가 / Hash Build 쪽이 작은가
통계·운영 □ 통계가 최신이며 Stale 상태가 아닌가
□ 결과 동일성 검증과 전후 수치 기록을 했는가

14. 용어집

용어 영문 설명
옵티마이저 Optimizer (CBO) 통계 기반으로 최저 비용 실행계획을 선택하는 엔진
실행계획 Execution Plan SQL을 처리하는 단계별 절차
카디널리티 Cardinality 각 단계의 예상 결과 행 수
선택도 Selectivity 조건을 만족하는 행의 비율
논리적 읽기 Logical Read (Buffer Gets) 버퍼 캐시에서 읽은 블록 수
물리적 읽기 Physical Read 디스크에서 읽은 블록 수
하드 파싱 Hard Parse 실행계획을 새로 생성하는 파싱
바인드 변수 Bind Variable 값 대신 변수를 사용해 SQL을 공유하는 방식
히스토그램 Histogram 컬럼 값 분포 통계
클러스터링 팩터 Clustering Factor 인덱스 순서와 테이블 저장 순서의 일치 정도
드라이빙 테이블 Driving (Outer) Table 조인에서 먼저 읽히는 집합
부분 범위 처리 Partial Range Processing 전체 결과가 아닌 첫 일부만 빠르게 반환
파티션 프루닝 Partition Pruning 조건에 해당하는 파티션만 읽는 것
대기 이벤트 Wait Event 세션이 자원을 기다리는 사유
AWR Automatic Workload Repository 성능 통계를 주기적으로 저장하는 저장소
ASH Active Session History 활성 세션 상태를 1초 단위로 샘플링한 이력
원본 주소 "https://dbstudy.co.kr/w/index.php?title=SQL_튜닝_입문&oldid=1991"