⚡ 쿼리 튜닝 팁 모음

현장에서 겪는 느린 쿼리의 8할은 몇 가지 패턴의 반복입니다. before/after 코드 쌍으로 정리한 Oracle 튜닝 실전 치트시트 — 필요할 때 바로 찾아 쓰는 빠른 참조 페이지입니다.

팁 1. 튜닝의 시작은 실행계획 — E-Rows vs A-Rows

느린 SQL을 받았을 때 첫 동작은 언제나 같습니다: 실제 실행 통계가 붙은 실행계획을 봅니다. 예상 계획(EXPLAIN PLAN)이 아니라 실제입니다.

-- ① 문제 SQL에 힌트를 붙여 실행 (또는 ALTER SESSION SET statistics_level=ALL)
SELECT /*+ GATHER_PLAN_STATISTICS */ o.order_no, c.cust_name
FROM   orders o JOIN customers c ON o.cust_id = c.cust_id
WHERE  o.order_status = 'SHIPPED';

-- ② 방금 실행한 커서의 "실제" 계획 조회
SELECT * FROM TABLE(
  DBMS_XPLAN.display_cursor(NULL, NULL, 'ALLSTATS LAST')
);

-- ③ 출력에서 볼 것: E-Rows(예측) vs A-Rows(실제)
--------------------------------------------------------------------
| Id | Operation                    | Name   | E-Rows | A-Rows |
--------------------------------------------------------------------
|  1 |  NESTED LOOPS                |        |     12 |   985K | ← 격차!
|* 2 |   TABLE ACCESS FULL          | ORDERS |     12 |   985K | ← 원흉
|  3 |   TABLE ACCESS BY INDEX ROWID| CUSTO..|      1 |   985K |
--------------------------------------------------------------------

읽는 법은 세 줄로 요약됩니다.

• E-Rows ≈ A-Rows — 옵티마이저는 정확히 알고 결정했습니다. 그런데도 느리다면 SQL 구조나 인덱스 설계 자체의 문제입니다.
• E-Rows ≪ A-Rows (수십 배 이상) — 옵티마이저가 속았습니다. 낡은 통계, 히스토그램 부재, 컬럼 가공에 의한 추정 실패를 의심하세요. 위 예시처럼 12건 예상이 98만 건이면, 그 판단으로 고른 NL 조인이 98만 번 반복된 것입니다.
• 격차가 시작되는 가장 안쪽 단계를 찾으세요. 아래(자식)에서 생긴 오차는 위(부모)로 전파·증폭됩니다.

팁 2. 인덱스를 못 타는 6대 원인 — before / after

"인덱스가 있는데 풀스캔이에요"의 원인은 놀랄 만큼 정형화되어 있습니다. 아래 6개 패턴이 대부분을 커버합니다.

2-1. 암시적 형변환 — 조용한 인덱스 킬러 1위

문자 컬럼을 숫자와 비교하면 Oracle은 컬럼 쪽에 TO_NUMBER()를 씌워 비교합니다. 컬럼이 가공되는 순간 그 컬럼의 인덱스는 쓸 수 없습니다. 눈에 안 보이게 일어나므로 가장 위험합니다.

-- ❌ before: cust_no VARCHAR2(10) — 숫자와 비교
SELECT * FROM customers WHERE cust_no = 100;
--   → filter(TO_NUMBER("CUST_NO")=100) : TABLE ACCESS FULL

-- ✅ after: 타입을 맞춘다
SELECT * FROM customers WHERE cust_no = '100';
--   → access("CUST_NO"='100') : INDEX RANGE SCAN

2-2. 컬럼 가공 — 함수는 컬럼이 아니라 상수 쪽에

-- ❌ before: 인덱스 컬럼을 SUBSTR로 가공
SELECT * FROM orders WHERE SUBSTR(order_no, 1, 4) = '2026';
-- ✅ after: 컬럼은 그대로 두고 LIKE 범위 조건으로
SELECT * FROM orders WHERE order_no LIKE '2026%';

-- ❌ before: 날짜 컬럼을 TRUNC로 가공 (당일 주문 조회)
SELECT * FROM orders WHERE TRUNC(order_dt) = DATE '2026-07-11';
-- ✅ after: 컬럼은 그대로, 조건을 범위로 변형
SELECT * FROM orders
WHERE  order_dt >= DATE '2026-07-11'
AND    order_dt <  DATE '2026-07-11' + 1;

원칙은 하나입니다: 가공은 상수/변수 쪽에서 하고, 컬럼은 날것으로 남긴다. 도저히 컬럼 가공을 피할 수 없는 요건(대소문자 무시 검색 등)이라면 함수기반 인덱스가 차선책입니다. 참고로 파티션 키 가공이 pruning을 깨는 것(11장)도 정확히 같은 원리입니다.

2-3. 선행 와일드카드 LIKE '%…'

-- ❌ before: 시작점이 없어 B*Tree 탐색 불가 → FULL SCAN
SELECT * FROM customers WHERE cust_name LIKE '%길동';

-- ✅ after 1: 가능하다면 전방 일치로 요건 변경
SELECT * FROM customers WHERE cust_name LIKE '홍길%';
-- ✅ after 2: 부분 일치 검색이 핵심 요건이면 Oracle Text 인덱스 등 전문 검색 도구를 검토

B*Tree는 "왼쪽부터 정렬"된 자료구조입니다. 첫 글자를 모르면 탐색 시작점을 정할 수 없습니다. 중간/후방 일치가 진짜 요건이라면 B*Tree로 해결하려 하지 말고 전문 검색(Oracle Text, 또는 Elasticsearch 같은 별도 엔진)을 검토하세요.

2-4. NOT / != — 부정형은 범위를 못 좁힌다

-- ❌ before: "SHIPPED가 아닌 것" = 나머지 전부 → 인덱스로 좁힐 수 없음
SELECT * FROM orders WHERE order_status != 'SHIPPED';

-- ✅ after: 실제로 원하는 값을 긍정형으로 나열
SELECT * FROM orders WHERE order_status IN ('NEW', 'PAYING', 'PACKING');

부정형 자체가 문법 위반은 아닙니다 — 문제는 "제외한 나머지 전부"가 대개 대부분의 행이라서 인덱스가 의미를 잃는다는 점입니다. 반대로 원하는 값 목록이 소수이고 선택도가 높다면 IN으로 뒤집는 순간 인덱스가 살아납니다.

2-5. OR 조건 — UNION ALL로 분리하거나 IN으로

-- ❌ before: 서로 다른 컬럼의 OR — 옵티마이저가 풀스캔을 택하기 쉬움
SELECT * FROM orders
WHERE  cust_id = :cid OR order_no = :ono;

-- ✅ after: 각각 자기 인덱스를 타도록 UNION ALL로 분리
SELECT * FROM orders WHERE cust_id = :cid
UNION ALL
SELECT * FROM orders WHERE order_no = :ono AND (cust_id != :cid OR cust_id IS NULL);

-- 같은 컬럼의 OR라면 그냥 IN으로 (인덱스 정상 사용)
SELECT * FROM orders WHERE order_status IN ('NEW', 'PAYING');

2-6. 통계 부족 — 인덱스가 아니라 지도가 문제

-- 증상: SQL도 인덱스도 멀쩡한데 풀스캔/이상한 조인 선택
-- 확인: 통계 수집 시점과 실제 행 수 비교
SELECT table_name, num_rows, last_analyzed
FROM   user_tables WHERE table_name = 'ORDERS';

SELECT COUNT(*) FROM orders;   -- num_rows와 크게 다르면 → 팁 7로

팁 3. 바인드 변수 — 하드파싱 폭탄 방지

Oracle은 SQL 텍스트가 완전히 같아야 공유 풀의 커서(파싱 결과 + 실행계획)를 재사용합니다. 값을 문자열로 이어붙인 리터럴 SQL은 값마다 다른 SQL이 되어, 실행할 때마다 하드 파싱(최적화 전 과정)을 반복합니다.

팁 4. 조인 튜닝 요점 — 작은 집합을 먼저, 방법은 규모로

조인 튜닝의 대원칙 두 가지:

• 조인 순서: 필터 조건으로 가장 작게 줄어드는 집합(driving set)부터 시작한다. 이후 모든 조인 반복 횟수가 이 크기에 비례합니다.
• 조인 방법: 결합 규모가 소량이면 NL(inner 인덱스 필수), 대량이면 Hash. 동작 원리는 10장 애니메이션 참고.

-- ❌ before: 1000만 건 orders를 driving으로 NL → 인덱스 탐색 1000만 회
SELECT o.order_no, c.cust_name
FROM   orders o, customers c
WHERE  o.cust_id = c.cust_id
AND    c.vip_grade = 'PLATINUM';   -- 플래티넘 고객은 단 50명!

-- ✅ after: "50명"이 driving이 되도록 힌트로 유도 (통계가 정확하면 대개 자동 선택)
SELECT /*+ LEADING(c) USE_NL(o) INDEX(o orders_cust_id_ix) */
       o.order_no, c.cust_name
FROM   customers c, orders o
WHERE  o.cust_id = c.cust_id
AND    c.vip_grade = 'PLATINUM';
-- 고객 50명 → 각자의 주문만 인덱스로 — NL 반복이 50회로 축소

-- 대량 결합(월 배치 등)이라면 반대로 Hash를 명시할 수도:
--   /*+ LEADING(c) USE_HASH(o) FULL(o) */

팁 5. 페이지네이션 — 정렬을 인덱스에게 맡겨라

목록 화면의 "1~20번째만"을 구현하는 두 가지 방법:

-- 전통 패턴 (11g 이하): ROWNUM 이중 래핑
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT order_no, order_dt, amount
    FROM   orders
    ORDER BY order_dt DESC          -- 정렬 먼저!
  ) a
  WHERE ROWNUM <= 20                 -- 상위 20건에서 컷 (COUNT STOPKEY)
)
WHERE rn > 10;                       -- 11~20건만

-- 12c+ 표준 문법: OFFSET / FETCH (내부적으로 유사하게 변환됨)
SELECT order_no, order_dt, amount
FROM   orders
ORDER BY order_dt DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

진짜 성능 포인트는 문법이 아니라 정렬 생략입니다. ORDER BY 컬럼에 인덱스가 있으면 옵티마이저는 인덱스를 정렬된 순서 그대로 읽다가 20건에서 멈춥니다(실행계획에 SORT 없이 COUNT STOPKEY). 인덱스가 없으면? 조건에 맞는 전체를 정렬한 뒤 20건을 버리는, 페이지가 뒤로 갈수록/데이터가 쌓일수록 느려지는 쿼리가 됩니다.

팁 6. 대량 작업 — 건별 처리를 버려라

6-1. 커밋은 모아서

-- ❌ before: 루프 안 건별 커밋 → log file sync 폭탄 (12장 사례 그대로)
FOR r IN (SELECT ...) LOOP
  UPDATE ...; COMMIT;
END LOOP;

-- ✅ after: N건 단위 배치 커밋 (또는 작업 전체에 1회)
FOR r IN (SELECT ...) LOOP
  UPDATE ...;
  cnt := cnt + 1;
  IF MOD(cnt, 10000) = 0 THEN COMMIT; END IF;
END LOOP;
COMMIT;

6-2. INSERT는 direct-path로

-- ✅ APPEND 힌트: 버퍼 캐시를 우회해 HWM 위에 블록을 통째로 기록
INSERT /*+ APPEND */ INTO sales_hist
SELECT * FROM sales WHERE sale_date < DATE '2026-01-01';
COMMIT;   -- direct-path 후에는 커밋 전까지 같은 세션도 그 테이블 조회 불가

direct-path insert는 기존 빈 공간을 재활용하지 않고 HWM(High Water Mark) 위에 이어 쓰는 대신, 블록 단위 기록으로 대량 적재가 훨씬 빠릅니다. 대상 테이블에는 배타적 락이 걸려 동시 DML과 공존할 수 없으니 배치 전용 기법입니다.

6-3. PL/SQL은 BULK COLLECT / FORALL

-- ❌ before: 행마다 SQL엔진↔PL/SQL엔진을 오가는 컨텍스트 스위치 100만 번
FOR r IN (SELECT id, amt FROM src) LOOP
  INSERT INTO dst VALUES (r.id, r.amt);
END LOOP;

-- ✅ after: 배열로 묶어서 왕복 횟수를 1만분의 1로
DECLARE
  TYPE t_rows IS TABLE OF src%ROWTYPE;
  v_rows t_rows;
  CURSOR c IS SELECT * FROM src;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_rows LIMIT 10000;  -- 1만 건씩 메모리로
    EXIT WHEN v_rows.COUNT = 0;
    FORALL i IN 1..v_rows.COUNT                     -- 1만 건을 한 번에 INSERT
      INSERT INTO dst VALUES v_rows(i);
    COMMIT;
  END LOOP;
  CLOSE c;
END;
/

팁 7. 통계 관리 — DBMS_STATS 기본기와 히스토그램

-- 기본형: 대량 변경 배치의 마지막 단계에 넣어두면 사고가 준다
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'ORDERS',
    cascade          => TRUE,                          -- 인덱스 포함
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',   -- 히스토그램 자동 판단
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE     -- 샘플링도 자동
  );
END;
/

-- 특정 skew 컬럼에 히스토그램을 명시적으로 만들고 싶다면
--   method_opt => 'FOR COLUMNS order_status SIZE 254'

-- 히스토그램 보유 현황 확인
SELECT column_name, num_distinct, histogram
FROM   user_tab_col_statistics WHERE table_name = 'ORDERS';

히스토그램은 "값별 분포"를 담는 추가 통계입니다. 히스토그램이 없으면 옵티마이저는 균등 분포를 가정합니다 — order_status의 99%가 '완료'인 테이블에서 = '오류' 조건의 cardinality를 "행수 ÷ 값 종류수"로 계산해 크게 틀리는 식입니다. SIZE AUTO는 "그 컬럼이 WHERE에 쓰인 적이 있고 분포가 치우쳤을 때" 자동으로 만들어 줍니다.

팁 8. 자주 쓰는 진단 쿼리 모음

장애 대응 시 복사해 쓰는 세 가지입니다. 배경 개념은 12장 참고.

8-1. 지금 활동 중인 세션과 대기 이벤트

SELECT s.sid, s.serial#, s.username, s.sql_id,
       s.event, s.state, s.seconds_in_wait, s.blocking_session
FROM   v$session s
WHERE  s.status = 'ACTIVE'
AND    s.username IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

8-2. 무거운 SQL Top-N (v$sql)

-- 누적 경과시간 기준 Top 10 — "실행당" 지표도 함께 봐야 한다
SELECT *
FROM (
  SELECT sql_id,
         executions,
         ROUND(elapsed_time/1e6, 1)                   AS elapsed_sec_total,
         ROUND(elapsed_time/NULLIF(executions,0)/1e3, 1) AS ms_per_exec,
         buffer_gets, disk_reads,
         SUBSTR(sql_text, 1, 80) AS sql_head
  FROM   v$sql
  ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 10;

8-3. 락 대기 체인 — 누가 누구를 막고 있나

-- 블로킹 당하는 세션과 그 원인 세션을 한 줄로
SELECT w.sid       AS waiting_sid,
       w.event     AS waiting_event,
       w.sql_id    AS waiting_sql,
       b.sid       AS blocker_sid,
       b.username  AS blocker_user,
       b.status    AS blocker_status,
       b.last_call_et AS blocker_idle_sec
FROM   v$session w
JOIN   v$session b ON b.sid = w.blocking_session
WHERE  w.blocking_session IS NOT NULL;
-- blocker가 INACTIVE + idle 수천 초 = "커밋 안 하고 퇴근" 패턴 (8장 참고)

✍️ 이해도 체크