공부저장소

📘SyncChecker

운영 시스템에서 성능 문제를 만날 때마다 느끼는 게 있습니다.

느린 SQL은 인덱스 하나 더 만든다고 끝나는 경우보다, 조회 방식 자체가 잘못 설계된 경우가 더 많습니다.

이번 사례가 딱 그랬습니다.

기존 DML 최신 상태 조회 로직은

  • 10만 건 조회에 약 30초
  • OR 절에 PK를 크게 넣으면 10만 건 조회에 약 2분

정도까지 무너졌습니다.

반면 구조를 바꾼 뒤에는

  • 200만 건을 한 번에 가져와도 약 3초
  • OR 절 PK 개수도 조정해서 10만 건 조회를 1초 이내

수준까지 내려왔습니다.

이 글은 단순 튜닝 이야기가 아닙니다.

핵심은 최신 상태를 계산하는 방법을 바꿨다는 점입니다.

문제: “최신 상태”를 조회할 때 너무 많은 걸 SQL이 계산하고 있었다

DML 비교에서 진짜 어려운 부분은 단순 INSERT/UPDATE/DELETE가 아닙니다.

문제는 PK UPDATE 입니다.

PK UPDATE가 발생하면 같은 row를 식별하는 기준 자체가 바뀝니다.

즉, 단순히 “마지막 SCN 1건”만 고르면 끝나지 않고,

이전 PK(BEFORE PK)와 이후 PK(AFTER PK)의 관계를 따라가며 최신 상태를 계산해야 했습니다.

기존 구현은 이 문제를 꽤 정석적으로 풀고 있었습니다.

  • pk_chain이라는 재귀 구조를 만들고
  • anchor를 잡은 뒤
  • PK UPDATE를 따라 계속 연결하고
  • 마지막에 root PK 기준으로 최신 row를 고르는 방식이었습니다.

즉, 조회 시점마다 “이 row의 진짜 최종 PK가 무엇인가”를 체인 추적으로 계산하고 있었던 겁니다.

이 접근은 논리적으로는 깔끔합니다.

하지만 데이터가 커질수록 비용이 너무 비쌌습니다.

특히 PK UPDATE가 누적되면,

조회는 단순 필터링이 아니라 경로 탐색에 가까워집니다.

SCN, TRANSIND 정렬 조건까지 얹히면 결국 읽는 양도 많고, 조인도 무거워지고, 중간 집합도 커집니다.

문제의 본질은 SQL 문법이 아니라 이거였습니다.

최신 상태를 조회 시점에 계산하는 구조 자체가 비쌌다.

기존 방식: 재귀적으로 PK chain을 따라가며 최신 상태 계산

기존 쿼리의 핵심은 pk_chain이었습니다.

로직을 요약하면 이렇습니다.

  1. PK UPDATE가 아닌 row, 혹은 부모가 없는 PK UPDATE를 anchor로 잡는다.
  2. PK UPDATE를 따라가며 BEFORE PK → AFTER PK 관계를 재귀적으로 확장한다.
  3. 마지막에 더 이상 후속 PK UPDATE가 없는 row만 남긴다.
  4. root PK 기준으로 가장 최신 SCN / TRANSIND row를 선택한다.

이 방식은 “정확하게 다 따라간다”는 점에서는 좋습니다.

하지만 운영에서는 정확도 100%보다 더 중요한 게 있습니다.

지금 이 조회가 제 시간 안에 끝나느냐 입니다.

실제로 이 구조는 데이터 양이 커질수록 빠르게 무거워졌고,

특히 PK UPDATE 이력이 많은 구간에서는 병목이 명확했습니다.

전환점: 체인을 추적하지 말고, 아예 제외할 대상을 먼저 정하자

이번 개선의 핵심은 관점을 바꾼 데 있습니다.

기존에는 이렇게 물었습니다.

“이 PK가 어디까지 이어졌는지 끝까지 따라가서 최신 상태를 계산하자.”

개선 후에는 질문이 바뀌었습니다.

“PK UPDATE의 BEFORE PK라면, 그 PK는 이미 과거 상태다.

그럼 최신 상태 후보에서 미리 제거하면 되지 않을까?”

이게 설계의 핵심입니다.

새 구현은 DMLQueryBuilder에서 다음 구조로 바뀌었습니다.

  • base_data: SCN 구간과 TXTYPE로 먼저 대상 축소
  • pk_links: PK UPDATE의 BEFORE PK 집합만 추출
  • candidates: 현재 row의 AFTER PK가 pk_links에 존재하면 제거
  • 마지막으로 ROW_NUMBER()로 동일 PK의 최신 1건만 선택

실제 새 코드에서도 이 구조가 그대로 보입니다.

먼저 base_data에서 SCN 범위와 DML 타입을 제한하고, pk_links에서는 PK UPDATE의 B_PK 컬럼만 그룹핑합니다. 이후 candidates에서 base_data의 A_PK와 pk_links의 B_PK를 조인한 뒤, 매칭되는 경우를 버리고(LEFT JOIN ... IS NULL) PK별 최신 1건만 남깁니다.

즉, 예전처럼 “루트를 찾고 체인을 끝까지 따라가는 방식”이 아니라,

최신 후보가 될 수 없는 PK를 먼저 제거하는 방식으로 바뀐 겁니다.

이 차이가 큽니다.

재귀 추적은 데이터가 늘수록 경로 계산 비용이 붙습니다.

반면 이번 방식은 훨씬 단순합니다.

  • 먼저 읽을 범위를 줄이고
  • PK UPDATE의 BEFORE PK 목록을 만들고
  • 그 목록에 걸리는 PK를 제거하고
  • 남은 것 중 최신 1건만 고릅니다

운영 관점에서 보면,

이건 “정확한 그래프 탐색”에서 “실용적인 후보 제거”로의 전환입니다.

코드 레벨에서 보면 무엇이 달라졌나

새 DMLQueryBuilder를 보면 의도가 매우 선명합니다.

1) base_data에서 먼저 범위를 줄였다

새 쿼리는 시작부터 전체 Validation 테이블을 뒤지지 않고,

SCN 구간과 TXTYPE를 기준으로 필요한 DML만 base_data에 모읍니다.

이 단계는 단순해 보이지만 중요합니다.

대용량 비교에서는 “얼마나 똑똑하게 계산하느냐”보다 먼저,

애초에 얼마나 적게 읽느냐가 더 중요하기 때문입니다.

2) pk_links에서 BEFORE PK만 뽑는다

pk_links는 TXTYPE = 'PK UPDATE'인 row에서 B_PK... 컬럼만 추출합니다.

즉, “이 PK는 이후 다른 PK로 바뀐 적 있다”는 사실만 따로 모읍니다.

여기서 중요한 건, 더 이상 체인을 만들지 않는다는 점입니다.

이 단계는 연결 관계 전체를 복원하려는 게 아니라, 제거 대상 집합을 만드는 작업입니다.

3) candidates에서 AFTER PK 기준으로 제거한다

candidates는 base_data b와 pk_links r를 조인해서,

b.A_PK = r.B_PK 관계가 성립하는 row를 제거합니다.

그리고 PK별로 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY SCN DESC, TRANSIND DESC)를 적용해 최신 1건만 남깁니다.

말 그대로:

  • 누군가의 BEFORE PK로 등장한 PK는 버리고
  • 남은 것 중 같은 PK는 최신 1건만 남긴다

이렇게 바뀐 겁니다.

이 설계가 빨라진 이유

이번 개선은 SQL 트릭이 아니라, 계산량 자체를 줄인 것에 가깝습니다.

기존 방식은 조회 때마다 이런 일을 했습니다.

  • 부모가 있는가
  • 체인을 더 따라가야 하는가
  • 최종 도착점이 무엇인가
  • root PK 기준으로 어떤 것이 마지막인가

반면 새 방식은 이렇게 바뀌었습니다.

  • PK UPDATE가 만든 BEFORE PK 목록만 만든다
  • 그 목록에 걸리는 PK는 제거한다
  • 나머지 중 가장 최신 것만 본다

운영 성능에서 중요한 건 보통 “정확한 모델”보다 “싼 모델”입니다.

특히 데이터량이 커질수록 그 차이는 더 커집니다.

사용자 측정 기준으로, 이 변경 이후에는

PK UPDATE의 BEFORE PK를 AFTER PK 쪽으로 반영하면서 이전 PK를 제거하는 방식으로 바꾼 뒤 200만 건을 한 번에 가져와도 약 3초 수준까지 내려갔다고 하셨습니다.

이 수치는 단순한 쿼리 리라이트 이상의 의미가 있습니다.

조회 로직 자체를 다시 설계했기 때문에 나온 차이입니다.

물론 트레이드오프는 있었다

이 방식은 만능이 아닙니다.

제외하기로 한 케이스가 있습니다.

PK UPDATE가 발생한 BEFORE PK에 대해, 다시 INSERT / UPDATE / DELETE 레코드가 붙는 경우는 포함하지 않는다.

정석적으로는 이 희귀 케이스까지 모두 추적해야 합니다.

그렇게 하려면 결국 다시 체인 추적에 가까운 구조로 돌아가게 됩니다.

하지만 실운영 데이터를 보면 이 케이스는 거의 발생하지 않았고,

그 희귀 케이스를 위해 전체 조회 성능을 계속 희생하는 것은 더 나쁜 선택이었습니다.

이 판단이 중요합니다.

실무에서는 자주 이렇게 결정합니다.

  • 이론적으로 완전한가?
  • 운영에서 충분히 맞는가?
  • 비용 대비 가치가 있는가?

이번 사례는 정확도 100%의 일반해보다

운영에서 충분히 안전하고 훨씬 빠른 근사해를 택한 것입니다.

저는 이런 판단이 오히려 더 엔지니어링답다고 생각합니다.

두 번째 병목: OR 절에 PK를 너무 많이 넣고 있었다

최신 상태 조회 로직을 바꾼 뒤에도 또 하나의 병목이 있었습니다.

해시 조회 단계에서 PK 목록을 OR / IN 조건으로 만들어 한 번에 질의하는데,

여기에 너무 많은 PK를 넣으면 오히려 느려졌습니다.

상식적으로는 “한 번에 많이 넣으면 왕복 횟수가 줄어서 빠를 것” 같지만,

Oracle에서는 꼭 그렇지 않습니다.

복합 PK가 늘어나면 바인드 파라미터 수가 급격히 증가합니다.

그러면 다음 비용이 같이 커집니다.

  • SQL 길이 증가
  • 파싱 비용 증가
  • 옵티마이저 부담 증가
  • 실행 계획 불안정
  • OR 조건 평가 비용 증가

실제로 PK 개수에 따라 배치 크기를 동적으로 줄이도록 구현돼 있습니다.

  • PK 1개 미만: 10000
  • PK 2~3개: 2000
  • PK 4~5개: 800
  • PK 6~8개: 300
  • 그 이상: 200

이 값은 이론값이 아니라, 운영에서 Oracle이 버틸 수 있는 지점을 찾아가며 정한 값에 가깝습니다. 코드에도 그대로 들어 있습니다.

즉, 이건 단순히 “배치를 나눴다”는 이야기가 아닙니다.

복합 PK 수가 늘어날수록 SQL 한 건의 비용이 기하급수적으로 커지기 때문에, 네트워크 왕복보다 파싱/실행 비용이 더 비싸지는 지점이 있다.

사용자 측정 기준으로도 결과는 분명했습니다.

  • 기존: OR 절에 10000건 → 10만 건 조회 약 2분
  • 변경: OR 절에 800건 제한 → 10만 건 조회 1초 이내

이건 작은 팁 수준이 아니라,

대용량 비교 시스템에서는 꽤 중요한 운영 지식입니다.

제가 이번 개선에서 좋게 본 지점

이번 사례가 좋은 이유는 “쿼리를 예쁘게 바꿨다”가 아니라,

비즈니스 전제를 이용해 문제를 단순화했다는 데 있습니다.

정리하면 이렇습니다.

1. 복잡한 일반해를 버렸다

재귀 PK chain은 논리적으로는 완전하지만, 운영에서는 비쌌습니다.

2. 운영 데이터의 성격을 반영했다

희귀 케이스를 모두 잡기보다, 실제로 자주 발생하는 패턴에 맞춰 설계를 바꿨습니다.

3. 조회 비용을 계산 비용에서 제거 비용으로 바꿨다

“최종 상태를 찾아가는 방식”이 아니라

“최종 상태가 될 수 없는 후보를 미리 제거하는 방식”으로 바꿨습니다.

4. SQL 한 방이 항상 좋은 건 아니라는 걸 수치로 확인했다

OR 절 10000건이 800건보다 느렸다는 건,

배치 크기도 튜닝 대상이라는 걸 잘 보여줍니다.

마무리

성능 개선 글은 종종 “인덱스 추가했습니다”, “힌트 넣었습니다”에서 끝납니다.

하지만 실제로 인상 깊은 개선은 대개 이런 식입니다.

  • 병목을 정확히 본다
  • 지금 구조가 왜 느린지 설명할 수 있다
  • 정확도와 성능의 trade-off를 판단한다
  • 운영에서 버틸 수 있는 구조로 다시 설계한다

이번 건은 딱 그런 사례였습니다.

PK chain을 끝까지 추적하는 정석적 접근에서,

PK UPDATE의 BEFORE PK를 제거하는 운영형 접근으로 바꾸면서

조회 비용을 구조적으로 낮췄고,

추가로 PK 개수에 따라 OR 절 배치 크기까지 조정하면서

실제 처리량을 크게 끌어올렸습니다.

성능 개선은 결국 문법 싸움이 아니라

무엇을 계산하고, 무엇을 계산하지 않을지 결정하는 설계 문제라는 걸 다시 확인한 작업이었습니다.

참고