Oracle 19c에서 db file sequential read 대기가 증가하는 원인과 SQL 진단 방법

2026년 8월 22일 읽는 시간 약 9분

Oracle 데이터베이스 성능 튜닝 및 운영 환경에서 가장 흔하게 접하는 대기 이벤트(Wait Event) 중 하나가 바로 db file sequential read입니다. 이 대기 이벤트는 이름만 들었을 때 순차적인 읽기(Sequential Read)로 오인하기 쉽지만, 실제로는 디스크 또는 NVMe 스토리지로부터 단일 블록(Single Block)을 메모리 버퍼 캐시로 읽어들이는 Synchronous Single-Block I/O 동작을 의미합니다. 인덱스 탐색이나 ROWID를 통한 테이블 액세스 과정에서 주로 발생하며, 정상적인 OLTP 시스템에서 일정 수준 나타나는 것은 지극히 자연스러운 현상입니다. 그러나 특정 시점에 이 대기 시간이 급증하면서 데이터베이스 전체의 CPU 및 스토리지 I/O 자원을 고갈시키고 애플리케이션 지연을 유발한다면, 이는 비효율적인 SQL 실행 계획이나 스토리지 병목의 강력한 신호입니다. 본 글에서는 Oracle 19c 환경을 중심으로 db file sequential read 대기 이벤트가 폭증하는 세부 원인과 이를 신속하게 진단하고 해결하는 SQL 분석 절차를 다각도로 살펴봅니다.

db file sequential read 대기 이벤트의 아키텍처적 의미

Oracle 아키텍처 관점에서 db file sequential read는 프로세스가 인덱스 블록을 읽거나 인덱스에서 추출한 ROWID를 바탕으로 테이블 데이터 블록을 하나씩 버퍼 캐시에 적재할 때 발생하는 동기식 I/O 대기입니다. 대기 이벤트 명칭의 ‘sequential’은 물리적 디스크 트랙을 순차적으로 읽는다는 뜻이 아니라, 인덱스 구조를 따라 블록을 하나씩 순차적으로 요청(Single Block Read)한다는 동작 메커니즘에서 유래했습니다.

버퍼 캐시(Buffer Cache)에 찾고자 하는 데이터 블록이 존재하지 않을 때(Buffer Cache Miss) 데이터베이스 프로세스는 디스크에 I/O 요청을 보낸 후 해당 블록이 SGA에 로드될 때까지 실행을 멈추고 대기 상태에 들어갑니다. 따라서 이 대기 이벤트의 총 시간은 ‘발생 횟수(Waits)’와 ‘블록당 I/O 지연 시간(Average Wait Time)’의 곱으로 결정됩니다. 발생 횟수가 지나치게 많다면 비효율적인 SQL이 수많은 단일 블록을 호출하고 있음을 의미하며, 평균 대기 시간이 길다면 스토리지 서브시스템 자체의 성능 저하나 I/O 처리 대역폭 병목을 의심해야 합니다.

대기 시간이 급증하는 주된 원인과 메커니즘

db file sequential read 대기 시간이 갑자기 급증하는 원인은 데이터베이스 내부의 SQL 실행 구조적 요인과 메모리 및 스토리지 환경 요인으로 구분할 수 있습니다.

첫째, 비효율적인 인덱스 스캔과 Table Access By Index ROWID입니다. 인덱스 선택도(Selectivity)가 낮은 조건절임에도 불구하고 CBO(Cost Based Optimizer)가 테이블 풀 스캔(Full Table Scan) 대신 인덱스 스캔을 선택하는 경우입니다. 인덱스를 통해 수만 건 이상의 ROWID를 추출한 후 테이블 블록을 일일이 액세스할 때 버퍼 캐시 적재율이 낮으면 엄청난 수의 Single Block I/O가 단시간에 집중됩니다.

둘째, 클러스터링 팩터(Clustering Factor)의 악화입니다. 인덱스의 정렬 순서와 테이블 실제 데이터의 물리적 저장 순서가 일치하지 않으면, 동일한 테이블 블록을 반복해서 읽거나 수많은 서로 다른 블록에 개별적으로 접근해야 합니다. 이 경우 추출 건수가 적더라도 대량의 디스크 I/O가 발생하여 대기 시간이 폭증합니다.

셋째, Oracle 19c 옵티마이저 통계 정보의 부적절함과 실행 계획 변동(Plan Change)입니다. 19c의 적응형 기능(Adaptive Features) 작동이나 통계 정보 미갱신으로 인해 기존에 Multi-Block I/O를 사용하는 Full Table Scan이나 Hash Join을 수행하던 대용량 배치 SQL이 갑자기 Index Scan 및 Nested Loop Join으로 바뀔 때 db file sequential read 대기가 폭발적으로 늘어납니다.

넷째, 버퍼 캐시 크기 부족 및 경합입니다. 메모리 내 버퍼 캐시 공간이 부족하거나 다른 대용량 쿼리로 인해 필요한 데이터 블록이 자주 캐시에서 방출(Age Out)되면, 동일한 블록을 반복적으로 디스크에서 다시 읽어오는 재캐싱 현상이 심화됩니다.

ASH 및 AWR을 활용한 문제 SQL 추적 기법

db file sequential read 대기가 급증했을 때 가장 먼저 수행해야 하는 작업은 원인을 제공하는 범인 SQL(Top SQL)을 찾아내는 것입니다. Oracle 19c가 제공하는 ASH(Active Session History)와 AWR(Automatic Workload Repository) 뷰를 활용하면 실시간 및 과거 시점의 악성 SQL을 정밀하게 추적할 수 있습니다.

실시간 장애 상황에서는 V$SESSION 및 V$ACTIVE_SESSION_HISTORY 뷰를 결합하여 현재 db file sequential read 대기 이벤트를 발생시키고 있는 세션과 SQL_ID를 추출합니다. 대기 이벤트의 파라미터인 p1(File Number), p2(Block Number), p3(Blocks) 정보를 파악하면 해당 세션이 정확히 어떤 데이터 파일의 어떤 블록을 읽느라 지연되고 있는지 파악할 수 있습니다.

과거 특정 시점에 발생한 지연 현상을 분석할 때는 DBA_HIST_ACTIVE_SESS_HISTORY 및 DBA_HIST_SQLSTAT 뷰를 조회합니다. 특정 타임슬롯 동안 db file sequential read 대기 비중이 높았던 Top SQL_ID 목록을 추출하고, 해당 SQL의 물리적 읽기 횟수(Physical Reads)와 델타 수행 시간을 비교 검증합니다. 이를 통해 단순히 실행 횟수가 많아 상위에 랭크된 정상 SQL과 단일 실행으로 수만 회의 Single Block I/O를 일으킨 악성 SQL을 명확히 구분해낼 수 있습니다.

실행 계획 분석과 클러스터링 팩터 개선 전략

문제 SQL_ID를 도출했다면, 다음 단계는 DBMS_XPLAN 패키지를 활용하여 실행 계획의 각 단계별 실제 I/O 발생 지점을 세부 분석하는 것입니다. V$SQL_PLAN 및 DBMS_XPLAN.DISPLAY_CURSOR를 호출할 때 ‘ALLSTATS LAST’ 옵션을 지정하면 예측된 건수(E-Rows)와 실제 처리된 건수(A-Rows), 그리고 각 단계에서 발생한 물리적 읽기(Buffers 및 Reads) 수를 정확히 비교할 수 있습니다.

만약 인덱스 스캔 단계 이후 TABLE ACCESS BY INDEX ROWID 단계에서 물리적 읽기 수가 급증하는 패턴이 확인된다면 해당 인덱스의 클러스터링 팩터(DBA_INDEXES.CLUSTERING_FACTOR)를 조회해야 합니다. 클러스터링 팩터 수치가 테이블의 전체 블록 수(DBA_TABLES.BLOCKS)에 가깝다면 데이터가 양호하게 정렬되어 있는 상태지만, 전체 행 수(NUM_ROWS)에 가깝다면 데이터가 물리적으로 심각하게 흩어져 있음을 의미합니다.

이러한 경우의 해결책으로는 다음과 같은 전략을 적용합니다.

첫째, 조건절 컬럼과 자주 결합되는 컬럼을 조합하여 결합 인덱스(Composite Index)를 구성하고, 테이블 Access 자체를 차단하는 Index Fast Full Scan이나 Index Only Access를 유도합니다.

둘째, 클러스터링 팩터가 불량하고 특정 조건 조회가 빈번하다면 테이블을 해당 키 기준 재정렬(Table Reorg/Rebuild)하거나 파티셔닝(Partitioning) 구조로 전환하여 물리적 I/O 집중도를 완화합니다.

셋째, 대량 데이터를 처리하는 워크로드라면 힌트(INDEX_FFS, FULL)나 SQL Patch를 활용하여 Single Block I/O 대신 Multi-Block I/O 방식인 Full Table Scan 및 Hash Join으로 실행 계획을 유도하는 것이 훨씬 유리합니다.

마치며

Oracle 19c 데이터베이스에서 db file sequential read 대기 이벤트는 무조건적인 장애의 징후라기보다는, 시스템의 메모리 처리 한계를 넘어 디스크 Single Block I/O가 과도하게 발생하고 있다는 아키텍처적 경고 신호입니다. 대기 이벤트의 발생 횟수와 평균 지연 시간을 분리하여 분석하고, ASH 및 AWR 기반의 Top SQL 추적, DBMS_XPLAN을 통한 A-Rows와 Physical Reads 검증, 그리고 클러스터링 팩터 및 인덱스 구조 개선으로 이어지는 체계적인 진단 프로세스를 정립해야 합니다. 이러한 다각도 튜닝 접근법을 적용한다면 스토리지 증설 없이도 데이터베이스 처리 수율과 응답 속도를 획기적으로 개선할 수 있습니다.