공부저장소

📘SyncChecker

운영에서 SQL 성능을 볼 때 가장 흔한 오해 중 하나가 있습니다.

인덱스를 사용하면 무조건 빠를 거라는 생각입니다.

짧은 조건 조회에서는 대체로 맞는 말입니다.

하지만 데이터를 대량으로 검증하거나, 넓은 범위를 한 번에 읽어야 하는 쿼리에서는 이야기가 달라집니다.

실제로 운영 중 데이터 검증 작업에서, 인덱스를 타는 실행계획이 잡혔는데도 성능이 급격히 떨어지는 일을 겪었습니다.

겉으로 보면 이상합니다.

  • 인덱스를 사용했다
  • 실행계획에도 인덱스 접근이 보인다
  • 그런데 전체 처리 시간은 오히려 길어진다

원인을 따라가 보니 핵심은 단순했습니다.

이 쿼리는 “적게 찾는 조회”가 아니라,

대량 데이터를 끝까지 검증해야 하는 조회였고,

그런 상황에서는 INDEX FULL SCAN이 오히려 불리할 수 있었습니다.

이번 글은 그때 정리했던 판단과, 왜 FULL TABLE SCAN이 더 빨랐는지에 대한 이야기입니다.

문제 상황

운영 중 특정 데이터 검증 쿼리의 지연이 갑자기 커졌습니다.

이 쿼리는 단건 조회나 소량 탐색이 아니라,

검증 대상 데이터를 꽤 큰 범위로 읽어 와서 비교해야 하는 성격이었습니다.

즉, 핵심은 “빨리 찾는 것”보다 “많이 읽는 것” 에 가까웠습니다.

처음에는 인덱스를 타고 있으니 괜찮을 거라고 생각했습니다.

그런데 실제 실행계획을 보면 기대했던 INDEX RANGE SCAN이 아니라,

사실상 인덱스를 넓게 훑는 방향의 접근이 잡혀 있었고, 체감 성능도 매우 좋지 않았습니다.

운영에서 이런 상황은 꽤 위험합니다.

왜냐하면 실행계획에 “인덱스”라는 단어가 보이면 안심하기 쉽기 때문입니다.

하지만 인덱스를 어떤 방식으로 읽는지는 전혀 다른 문제입니다.

왜 RANGE SCAN이 아니라 INDEX FULL SCAN이 나왔을까

당시 상황을 보면, 암호화된 pk가 1개있고 해당 테이블 전체의 데이터를 가지고 와야 하는 상황이고, 암호화 처리나 함수 적용 때문에 일반적인 의미의 정렬 가능한 조건 검색이 어려웠습니다.

즉, 옵티마이저 입장에서는 인덱스를 “범위로 잘라 들어가는” INDEX RANGE SCAN을 선택하기 어려웠고,

결과적으로 Index Full Scan 쪽을 선택했을 가능성이 높았습니다.

여기서 중요한 건 이겁니다.

INDEX FULL SCAN은 이름만 보면 인덱스를 쓰니까 좋아 보입니다.

하지만 실제로는 인덱스를 처음부터 끝까지 거의 다 읽는 접근입니다.

그리고 검증 대상이 많다면 결국 인덱스에서 얻은 많은 ROWID를 따라 실제 테이블 블록까지 계속 점프해야 합니다.

문제는 이 다음부터입니다.

인덱스 리프 블록에서 얻는 ROWID는 보통 물리적으로 예쁘게 정렬되어 있지 않습니다.

특히 테이블 정렬 상태와 인덱스 정렬 상태가 잘 맞지 않거나,

클러스터링 팩터가 좋지 않은 경우라면 ROWID가 여기저기 흩어져 있게 됩니다.

그 결과 어떤 일이 생기냐면,

  • 인덱스를 끝까지 읽는다
  • 리프 블록마다 ROWID를 대량으로 얻는다
  • 각 ROWID를 따라 테이블 블록으로 계속 점프한다
  • 결국 랜덤 I/O가 폭증한다

즉, “인덱스를 사용했다”는 사실보다 중요한 건,

인덱스를 통해 테이블에 랜덤하게 뛰어다니는 비용이 너무 커졌다는 점이었습니다.

INDEX FULL SCAN이 느려지는 진짜 이유

운영에서 이런 유형의 쿼리가 느린 이유는 대체로 세 가지가 겹칩니다.

첫 번째는 읽는 양 자체가 많다는 점입니다.

원래 인덱스는 적은 양을 빠르게 찾는 데 강합니다.

그런데 검증 쿼리처럼 결과적으로 많은 row를 읽어야 한다면,

인덱스를 타는 이점이 점점 줄어듭니다.

두 번째는 ROWID 기반 점프 접근입니다.

인덱스만 보고 끝나는 쿼리가 아니라면, 결국 TABLE ACCESS BY ROWID가 뒤따릅니다.

이때 읽어야 할 row 수가 커질수록, 순차 접근이 아니라 랜덤 접근이 반복됩니다.

세 번째는 클러스터링 팩터 문제입니다.

클러스터링 팩터가 좋지 않으면 인덱스 순서대로 읽었을 때 테이블 블록 접근이 매우 비효율적입니다.

쉽게 말해, 인덱스에서는 가까운 값처럼 보여도 실제 테이블에서는 여기저기 흩어져 있는 셈입니다.

그러면 인덱스를 타는 순간 오히려 디스크 점프 비용이 커집니다.

이 조합이 붙으면 INDEX FULL SCAN은 “인덱스를 쓰는 접근”이 아니라,

실제로는 랜덤 I/O를 잔뜩 발생시키는 비싼 전체 탐색이 됩니다.

그럼 FULL TABLE SCAN은 왜 더 빨랐을까

처음엔 이게 직관에 안 맞을 수 있습니다.

인덱스를 쓰는 것보다 테이블 전체를 읽는 게 더 빠르다고?

그런데 대량 처리에서는 충분히 그렇습니다.

FULL TABLE SCAN은 테이블 세그먼트를 HWM까지 순차적으로 읽습니다.

즉, 여기저기 점프하지 않고 쭉 읽습니다.

이 방식의 장점은 분명합니다.

먼저, 랜덤 I/O가 아니라 순차 I/O 중심으로 동작합니다.

그리고 Oracle은 Full Scan 상황에서 Direct Path Read, 멀티블록 I/O, 병렬 처리 같은 최적화를 적극적으로 활용할 수 있습니다.

즉, “많이 읽는 작업”에서는 대역폭을 최대한 활용하는 구조가 됩니다.

비유하면 이렇습니다.

INDEX FULL SCAN + TABLE ACCESS BY ROWID는

책상 위에 흩어진 종이를 한 장씩 찾아가며 읽는 방식에 가깝습니다.

반면 FULL TABLE SCAN은

책 한 권을 처음부터 끝까지 넘기며 읽는 방식에 가깝습니다.

찾는 데이터가 몇 줄 안 되면 전자가 낫습니다.

하지만 어차피 책 대부분을 읽어야 한다면,

한 장씩 뛰어다니는 것보다 처음부터 끝까지 넘기는 편이 훨씬 낫습니다.

운영에서 우리가 부딪힌 것도 정확히 이 상황이었습니다.

이 쿼리는 “특정 소수 row를 찾는 조회”가 아니라

대량 데이터를 검증하기 위해 넓게 읽는 작업이었고,

이런 경우에는 INDEX FULL SCAN보다 FULL TABLE SCAN이 더 유리할 수 있었습니다.

핵심은 인덱스를 썼느냐가 아니라, 얼마나 비싸게 읽었느냐다

이 사건에서 가장 크게 얻은 교훈은 이것입니다.

실행계획에서 인덱스를 탔다는 사실만 보고 성능을 판단하면 안 됩니다.

정말 봐야 하는 건 다음입니다.

  • RANGE SCAN인지, FULL SCAN인지
  • 인덱스 이후 TABLE ACCESS BY ROWID가 얼마나 큰지
  • 읽는 건수가 얼마나 많은지
  • 클러스터링 팩터가 어떤지
  • 지금 이 쿼리가 “적게 찾는 쿼리”인지, “많이 읽는 쿼리”인지

대량 검증 작업은 본질적으로 “많이 읽는” 쿼리입니다.

이런 쿼리에서 인덱스를 억지로 타게 하면,

옵티마이저 입장에서는 좋아 보이는 선택이 실제 운영에서는 더 느릴 수 있습니다.

즉, 문제는 인덱스 유무가 아니었습니다.

랜덤 I/O 중심의 인덱스 접근이, 순차 대역폭 중심의 Full Scan보다 더 비쌌다

이게 본질이었습니다.

이 이슈를 통해 정리한 판단 기준

이후 비슷한 성격의 쿼리를 볼 때는 기준이 조금 달라졌습니다.

단순히 “인덱스가 있으니 타겠지”가 아니라,

먼저 이 쿼리가 어떤 종류의 읽기인지부터 봅니다.

조건으로 아주 적은 row만 찾는다면 인덱스 접근이 맞습니다.

하지만 조건 특성상 range scan이 어렵고,

결국 많은 데이터를 읽어야 하며,

검증/배치/비교 같은 성격이라면 오히려 Full Table Scan이 더 자연스러운 선택일 수 있습니다.

특히 아래 조건이 겹치면 INDEX FULL SCAN을 경계하게 됩니다.

  • 함수 적용, 암호화, 변형 컬럼 때문에 정상적인 range scan이 어렵다
  • 결과 row 수가 많다
  • 테이블 접근이 반드시 뒤따른다
  • 클러스터링 팩터가 좋지 않다
  • 검증성 배치처럼 어차피 넓게 읽어야 한다

이 상황에서 인덱스는 “빠른 길”이 아니라

랜덤 I/O를 늘리는 우회로가 될 수 있습니다.

참고