다시 느려진 쿼리를 마주했을 때__레거시 프로시저 튜닝기

운영 중인 시스템에 고객 전환 현황을 보여주는 통계 화면이 있다.

2~3년 전에 한 번 튜닝했던 화면인데, 어느새 조회 버튼을 누르면 2분 41초가 걸리는 화면이 되어 있었다.

이 글은 그 화면을 두 번째로 튜닝한 기록이다.

무엇이 다시 느려지게 만들었는지, 이번에는 무엇을 다르게 했는지, 그리고 시도했다가 버린 것들까지.

TL;DR

한 번 튜닝했던 Oracle 통계 프로시저가 데이터가 쌓이면서 다시 2분 41초까지 느려졌다.

두 번째 판에서는 실측(A-Rows)으로 병목 다섯 개를 확정하고, 쿼리를 "항목이 늘어도 스캔이 늘지 않는 구조"로 다시 짰다 — 반복 스캔은 CTE 한 벌로, 행마다 돌던 서브쿼리는 조인으로, 대형 정렬은 고정폭 문자열 MAX로.

DBA 손을 빌리지 않고 쿼리 변경만으로 161.7초가 62.9초(−61%)가 됐다.

그리고 빨라졌다는 것보다 중요한 건, 결과가 한 행도 달라지지 않았다는 증명이었다.

---

들어가며

이 화면과의 인연은 2~3년 전으로 거슬러 올라간다.

처음 만났을 때 이 프로시저는 4천 줄에 가까웠다.

구조를 뜯어보니, 거대한 조인 쿼리로 결과를 물리 임시 테이블에 적재하고 → 총건수 커서를 열고 → 상세 리스트 커서를 여는 3단계.

문제는 세 단계가 각각 거의 같은 쿼리를 통째로 복사해 들고 있었다는 것. 같은 수천만 행 조인을 한 번의 조회에 사실상 세 번씩 하고 있었던 셈이다.

화면에 항목이 추가될 때마다 세 벌 모두에 인라인뷰가 복사·추가되면서 몸집도 함께 불어난, 전형적인 레거시였다.

당시의 튜닝은 이 겹을 걷어내는 것이었다.

중복된 쿼리를 한 벌로 통합하고(총건수 커서는 화면에서 실제로 쓰지 않아 더미로 정리), 물리 임시 테이블 적재를 없애고, 무거운 일부 집계는 배치로 미리 계산해 두고 조인하는 방식으로 돌리고, 문제 구간에는 옵티마이저 힌트를 넣었다.

효과는 분명했다. 튜닝 전에는 조회 한 번에 3~4분씩 걸리던 응답이 1분 30~40초대로 내려왔고, 한동안은 그걸로 버텼다.

그리고 다시 느려졌다

그런데 시간이 지나면서 상황이 달라졌다.

데이터는 계속 쌓여 이력 테이블들이 수천만 행 규모가 됐고, 그 사이 갱신 이력에는 줄이 계속 늘었다 — 보여줄 컬럼 추가, 집계 기준 변경, 예외 케이스 처리, 명칭 변경….

요구사항 하나하나는 작았지만, 이 프로시저에서 "항목 하나 추가"는 곧 "수천만 행 이력 테이블을 다시 읽는 인라인뷰 하나 추가"를 의미했다.

2~3년치가 쌓이자 1차 튜닝으로 벌어둔 여유는 다 소진됐고, 조회 한 번에 2분 41초가 걸리게 됐다.

겉을 다듬는 걸로는 한계라는 뜻이었다.

이번에는 쿼리의 구조 자체 — 정확히는 "항목이 늘어도 스캔은 늘지 않는 구조"로 바꿔보기로 했다.

제약 조건이 몇 가지 있었다.

결과셋은 완전히 동일해야 한다. 운영 화면이므로 한 행, 한 값도 달라지면 안 된다.

쿼리 변경만 가능하다. PGA 파라미터 조정이나 PARALLEL 같은 DBA 영역의 개입은 쓸 수 없는 상황이었다.

환경은 Oracle 19c.

결과부터 먼저 보자.

단계	전체 시간	논리 I/O
원본	161.7초	15M 버퍼
1라운드 (구조 개선)	77.7초 (−52%)	7.5M (−50%)
2라운드 (정렬 제거)	62.9초 (−61%)	7.5M

진단 — 추정하지 말고 실측하기

제일 먼저 한 일은 "어디가 느린지"를 실측으로 못 박는 것이었다.

-- 힌트를 넣어 실행한 뒤
SELECT /*+ GATHER_PLAN_STATISTICS */ ...

-- 실제 실행 통계를 본다
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', 0, 'ALLSTATS LAST'));

이렇게 하면 플랜의 각 단계마다 예상 행수(E-Rows)와 실제 행수(A-Rows), 실제 소요 시간(A-Time), temp 사용량(Used-Tmp)을 볼 수 있다.

이 프로시저에서는 예상치가 실제와 수십 배씩 어긋나 있었다.

예상 Cost만 보고 판단했다면 엉뚱한 곳을 고쳤을 것이다.

그렇게 잡아낸 병목은 다섯 가지였다.

1. 같은 이력 테이블을 여러 번 스캔 — 2천만~4천만 행짜리 이력 테이블 4종을 각각 4~6회씩 풀스캔하고 있었다. "고객별 최신(또는 최초) 1건"을 뽑는 ROW_NUMBER() ... RN=1 인라인뷰가 항목마다 따로 있었기 때문이다.
2. 행마다 실행되는 상관 서브쿼리 — 담당 조직을 판정하는 IN-서브쿼리가 후보 행마다(26만 회) 실행되어, 누적 6천만 행을 스캔하고 있었다. 첫 구간 39초의 대부분.
3. 기간 필터가 맨 마지막에 적용 — 조회 기간 조건이 특수 분기 CASE 안에 싸여 있어 스캔 단계에서 못 거르고, 272만 행이 조인 체인을 전부 통과한 뒤에야 수십만 행으로 줄었다.
4. OR 분기 안의 NOT IN 서브쿼리 — unnest가 되지 않아 행별 FILTER로 남아, 인덱스 스킵스캔이 5.9만 회 실행됐다(11.5초).
5. 대형 정렬의 디스크 스필 — RN=1용 정렬이 500MB급이라 워크에어리어 한도(~97MB)를 넘어 temp로 넘치고 있었다.

1라운드 — 구조 개선 (161.7초 → 77.7초)

반복 스캔을 공통 CTE 하나로

가장 큰 병목은 1번이었다.

원본은 이런 모양이었다.

SELECT ...
FROM 고객후보 T
LEFT JOIN (SELECT 고객ID, 값A, ROW_NUMBER() OVER (PARTITION BY 고객ID ORDER BY 처리일 DESC) RN
             FROM 이력테이블) X1 ON X1.고객ID = T.고객ID AND X1.RN = 1
LEFT JOIN (SELECT 고객ID, 값B, ROW_NUMBER() OVER (...) RN
             FROM 이력테이블) X2 ON ...  -- 같은 테이블을 또 스캔
LEFT JOIN (... 이력테이블 ...) X3 ON ...  -- 또 스캔

항목이 하나 늘 때마다 인라인뷰가 하나씩 복사·추가돼 온, 레거시에서 흔히 보는 증식 패턴이다.

이걸 WITH 절에서 한 번만 스캔해 항목별 컬럼으로 피벗하고, 쓰는 곳마다 그 결과를 조인하도록 바꿨다.

WITH W_INFO AS (
  SELECT /*+ MATERIALIZE */
         고객ID,
         MAX(DECODE(항목코드, 'A', 값)) AS 값A,
         MAX(DECODE(항목코드, 'B', 값)) AS 값B,
         ...
    FROM 이력테이블
   GROUP BY 고객ID
)
SELECT ... FROM 고객후보 T LEFT JOIN W_INFO ON W_INFO.고객ID = T.고객ID

두 방식을 그림으로 나란히 놓고 보면, 항목이 늘 때 왼쪽은 스캔이 늘고 오른쪽은 컬럼만 늘어난다.

이게 이번 튜닝의 뼈대다.

포인트 하나.

한 번만 쓰이는 테이블은 CTE로 빼지 않았다.

물리화는 temp에 쓰고 다시 읽는 비용이 있어서, 재사용할 곳이 없으면 순손해다.

상관 서브쿼리 → 조인

행마다 실행되던 IN-서브쿼리는 작은 매핑 집합(수백 행)을 미리 만들어 LEFT JOIN 1회로 풀었다.

단, 서브쿼리를 조인으로 펴려면 조인 상대가 1:1임을 먼저 증명해야 한다(키+유효기간 겹침 없음을 데이터로 확인).

1:N이면 행이 불어나 결과가 달라진다.

필터를 앞으로 — 그리고 밟았던 함정

기간 CASE 필터가 논리적으로 함의하는 조건을 뽑아 스캔 단계 WHERE에 추가했다.

272만 행이 39만 행으로 줄어든 뒤 조인이 시작되니 체인 전체가 가벼워졌다.

기존 CASE 필터는 그대로 두므로 결과는 불변이다.

여기서 함정을 하나 밟았다.

처음에 조건을 NVL(일자1, 일자2) BETWEEN ... 형태로 깔끔하게 묶어 넣었더니, 옵티마이저의 행수 추정(E-Rows)이 37만에서 4로 붕괴하면서 NL 조인이 남발돼 오히려 느려졌다.

함수로 감싸지 않은 순수 컬럼 조건의 OR 3분지로 풀어 쓰니 추정이 회복됐다.

선행 필터는 강력하지만, 옵티마이저가 계산할 수 있는 모양으로 넣어야 한다.

OR 분기 안의 NOT IN → 안티조인

OR 분기 안에 있는 NOT IN 서브쿼리는 옵티마이저가 unnest하지 못하고 행별 FILTER로 실행한다.

DISTINCT 집합을 LEFT JOIN하고 IS NULL로 거르는 안티조인으로 수동 치환했다(11.5초 → 1.5초).

이 방식은 해당 컬럼의 NOT NULL이 보장될 때만 동치라는 점을 먼저 짚어야 한다 — NULL이 끼면 NOT IN은 전체를 공집합으로 만들기 때문이다.

정렬 스필 줄이기 (1단계)

RN=1용 이중 WINDOW SORT는 GROUP BY + KEEP(DENSE_RANK) 집계로 고쳐 써서 정렬을 절반 이하로 줄였다(42.5초 → 21.0초 구간도 있었다).

동순위가 있을 수 있으면 타이브레이커 컬럼을 정렬키에 추가해 선택을 결정적으로 만들어야 한다.

여기까지가 1라운드.

161.7초가 77.7초가 됐다.

2라운드 — 정렬을 아예 없애기 (77.7초 → 62.9초)

플랜을 다시 떠 보니 KEEP(DENSE_RANK)도 결국 SORT GROUP BY로 실행되고 있었다.

정렬이 남아 있으니 스필도 남는다(세 구간 합계 약 600MB, 15초).

이걸 없애려면 "고객별 최신 1건 뽑기"에서 정렬이라는 작업 자체를 제거해야 했다.

고정폭 문자열 MAX 트릭

발상은 단순하다.

그룹마다 필요한 건 1등 한 행뿐인데, 정렬은 전체를 줄 세운다.

1등만 찾는 데는 MAX면 충분하고, MAX는 해시 집계(HASH GROUP BY)로 실행할 수 있어 그룹당 한 줄의 메모리만 쓴다.

문제는 MAX가 값 하나만 비교한다는 것.

MAX(상태), MAX(성별)처럼 컬럼별로 따로 쓰면 서로 다른 행의 값이 섞인다.

그래서 행 전체를 문자열 하나로 포장한다.

-- 정렬키(고정폭)를 앞에, 가져올 컬럼(페이로드)을 뒤에 붙인다
MAX( TO_CHAR(처리일, 'YYYYMMDD') || TO_CHAR(기준일, 'YYYYMMDD')  -- 정렬키
     || RPAD(상태코드, 4) || RPAD(성별, 1) || ... )              -- 페이로드

문자열 비교는 왼쪽부터이므로, 정렬키가 앞이면 문자열 대소 = 날짜 대소다.

MAX가 최신 행의 문자열을 통째로 남기고(페이로드가 같은 행에서 딸려오니 섞일 수 없다), 바깥에서 SUBSTR 고정 위치로 컬럼을 복원하면 끝.

고정폭이 필수인 이유는 두 가지다.

폭이 다르면 비교가 어긋나고(숫자로 치면 '9' > '10' 문제), SUBSTR로 되찾으려면 각 컬럼의 시작 위치가 항상 같아야 한다.

그래서 이 기법에는 사람이 보증해야 하는 전제 조건이 붙는다.

• 정렬키가 NOT NULL일 것 (NULL이면 연결·비교가 깨진다)
• 정렬키가 고정폭으로 표현 가능할 것 (여기서는 일자 컬럼의 시분초가 전부 0임을 데이터로 확인하고 8자리를 썼다 — 시분초가 생기면 14자리로 바꿔야 한다)
• 그룹 내 정렬키 조합이 유일할 것 (PK 구성으로 확인 — 동순위가 있으면 선택 행이 비결정적이 된다)

오해 방지 — 문자열이라서 빠른 게 아니다

비교 1회만 보면 DATE 비교가 문자열 비교보다 싸고, TO_CHAR·연결 비용도 행마다 추가된다.

이긴 지점은 작업의 구조다.

	정렬 방식 (SORT GROUP BY)	MAX 방식 (HASH GROUP BY)
하는 일	수천만 행 전체를 줄 세우기	행마다 "지금까지 최대값"과 1회 비교·갱신
비교 횟수	N log N	N
필요 메모리	데이터 전체 크기 → 한도 초과 시 디스크 스필	그룹 수 × 한 줄

가장 큰 구간에서는 스필 270MB가 깨끗이 사라지면서 빌드가 21.0초 → 11.2초가 됐다.

뒤집어 말하면, 정렬이 메모리 안에서 끝나는 작은 집계라면 이 트릭은 이득이 없거나 가공 비용만큼 손해다.

스필이 나는 대형 집계에만 쓰는 게 맞다.

옵티마이저의 재량 봉인하기 — 힌트 이야기

이 작업에서 힌트는 "빠르게 하는 마법"이 아니라 "만들어 놓은 좋은 구조가 무너지지 않게 잠그는 장치"로 썼다.

통계 추정이 수십 배씩 틀리는 쿼리에서는, 평균적으로 이로운 옵티마이저의 재작성 기능이 잘못 발동하면 재앙이 되기 때문이다.

실제로 튜닝 중 옵티마이저가 피벗 뷰를 멋대로 병합·재작성해 262억 행짜리 카테시안을 만든 적이 있다.

힌트	잠그는 재량	강제되는 동작
MATERIALIZE	CTE를 인라인해 참조마다 재스캔할까?	한 번 계산해 temp에 저장 후 재사용
NO_MERGE	뷰 경계를 허물고 바깥 조인에 흡수할까?	뷰를 독립적으로 완성한 뒤 결과만 조인
NO_PLACE_GROUP_BY	GROUP BY를 조인 앞뒤로 옮길까?	집계를 쓴 자리에서 그대로 실행
USE_HASH_AGGREGATION	GROUP BY를 정렬로 할까 해시로 할까?	해시 집계 (정렬·스필 제거)

한 가지 팁.

19c부터는 실행계획 하단에 Hint Report가 붙어서 힌트가 무시됐는지(U - Unused)를 알려준다.

힌트를 넣었으면 여기까지 봐야 끝난 것이다.

이 환경에서도 특정 힌트 하나가 조용히 무시되고 있었고, Hint Report 덕에 발견해 다른 방식으로 우회했다.

검증 — "빨라졌다"보다 중요한 "같다"

결과셋 동일성은 네 단계로 검증했다.

1. 원본과 튜닝본의 결과를 각각 임시 테이블에 저장
2. 총 건수 비교
3. 양방향 MINUS — 이때 단순 MINUS는 중복 행 수의 차이를 못 잡으므로, GROUP BY 전체컬럼 + COUNT(*) 형태로 비교해 같은 행이 몇 번 나오는지까지 대조
4. 순서가 보장되지 않는 컬럼(ORDER BY 없는 ROWNUM 기반 순번)은 비교에서 제외 — 원본부터 순서 비보장이므로 제외가 맞다

이 검증을 통과한 버전만 반영 대상으로 삼았다.

특히 2라운드의 문자열 MAX 트릭은 전제 조건을 사람이 보증하는 재작성이라, 검증 없이는 쓸 수 없는 기법이다.

시도했지만 버린 것들

같은 길을 다시 걷지 않도록 기각 기록도 남긴다.

• PARALLEL 힌트 — 인스턴스 설정상 병렬이 꺼져 있어 불가.
• 워크에어리어(PGA) 상향 — 효과는 있었지만 DBA 협의가 필요해 범위에서 제외. (이게 가능했다면 스필 문제의 상당 부분은 튜닝 없이도 해소된다)
• CARDINALITY 힌트로 추정 보정 — 대상 행수가 파라미터에 따라 수천~수십만으로 요동쳐서, 고정값은 어느 한쪽에서 반드시 틀린다. 기각.
• 인덱스 NL 강제 — 좁은 조건에서는 빨랐지만 넓은 조건에서 물리 읽기가 폭발해 30초 악화. 한쪽 시나리오만 보고 힌트를 박으면 안 된다.
• 중간 결과의 물리 temp 테이블화 — 만들어 봤지만 빌드 비용은 물리화해도 그대로라 이득이 없었다.

정리

	원본	최종
전체 시간	161.7초	62.9초 (−61%)
논리 I/O	15M 버퍼	7.5M (−50%)
DBA 개입	—	없음 (쿼리 변경만)

교훈을 몇 줄로 남긴다.

1. 판단은 항상 실측(A-Rows)으로. 예상 Cost는 이 쿼리에서 수십 배씩 틀렸다.
2. 레거시 통계 쿼리의 단골 병목은 "같은 대형 테이블의 RN=1 인라인뷰 복붙" — 공통 CTE 1스캔 통합이 1순위다.
3. 필터는 최대한 앞으로. 단 옵티마이저가 추정할 수 있는 순수 컬럼 조건으로.
4. 정렬 스필이 크면 ROW_NUMBER → KEEP → 고정폭 문자열 MAX 순으로 정렬을 줄여갈 수 있다. 뒤로 갈수록 빨라지지만 전제 조건도 늘어난다.
5. 힌트는 가속 장치가 아니라 플랜 고정 장치로. Hint Report로 무시 여부까지 챙길 것까지가 한 세트다.
6. 마지막은 언제나 결과 동일성 검증. 빨라졌는데 다르면 튜닝이 아니라 장애다.

한 번의 튜닝으로 끝나는 시스템은 없다.

2~3년 전의 1차 튜닝이 그랬듯, 이번에 벌어둔 여유도 데이터가 쌓이고 로직이 바뀌면 언젠가 다시 소진될 것이다.

다만 두 가지는 달라졌다.

이제 이 쿼리는 항목이 늘어도 스캔이 늘지 않는 구조라 예전만큼 빠르게 낡지 않을 것이고, "어디가 왜 느렸고 무엇을 왜 버렸는지"까지 기록해 뒀으니 다음은 3분 가까이 되는 곳에서 시작하지 않아도 된다.