Bong's Log

오라클 데이터베이스를 운영하다 보면 애플리케이션 서비스가 갑자기 중단되거나 배치 작업이 실패하면서 다음과 같은 에러를 마주치는 순간이 있습니다.

ORA-28000: the account is locked
ORA-28001: the password has expired

단순히 ALTER USER 계정명 IDENTIFIED BY 비밀번호; 명령어로 해결되는 단순한 상황도 있지만, 실무 환경에서는 그렇게 단순하지 않습니다.

운영 중인 계정의 기존 패스워드를 모르는 상태에서 잠김(Lock)만 해제해야 하거나, 애플리케이션 소스 코드 수정 없이 패스워드 만료 주기(Lifetime)를 우회하여 계정을 연장해야 하는 상황, 그리고 Oracle 11g/12c 이후 강화된 해시 알고리즘과 대소문자 구분 파라미터가 얽혀 발생하는 사이드 이펙트가 빈번하게 발생하기 때문입니다.

이번 포스팅에서는 오라클의 사용자 인증 및 프로파일(Profile) 내부 메커니즘, 계정 잠김/만료 트러블슈팅, 그리고 기존 비밀번호를 그대로 유지하면서 만료 상태를 초기화하는 실무 팁을 상세히 정리합니다.

1. 내부 아키텍처: 계정 상태와 인증 메커니즘

1) 오라클의 계정 상태 머신 (Account Status)

오라클 딕셔너리 뷰 DBA_USERS의 ACCOUNT_STATUS 컬럼은 계정의 현재 라이프사이클을 나타내며, 내부적으로 다음과 같은 우선순위와 전이 규칙을 가집니다.

• OPEN: 정상적으로 로그인이 가능한 상태.
• EXPIRED: 패스워드 유효기간(PASSWORD_LIFE_TIME)이 만료된 상태. 신규 세션 연결 시 ORA-28001을 반환하며 접속이 거부됩니다.
• LOCKED: 로그인 실패 횟수 초과(FAILED_LOGIN_ATTEMPTS) 또는 관리자의 수동 조치로 계정이 잠긴 상태. 접속 시 ORA-28000을 반환합니다.
• EXPIRED & LOCKED: 만료와 잠김이 동시에 발생한 복합 상태.

       [정상 로그인]
   ┌─────────────────┐
   │      OPEN       │
   └────────┬────────┘
            │
  ┌─────────┴─────────┐
  │                   │
[유효기간 만료]    [로그인 실패 N회 초과]
  │                   │
  ▼                   ▼
┌─────────────┐   ┌─────────────┐
│   EXPIRED   │   │   LOCKED    │
│ (ORA-28001) │   │ (ORA-28000) │
└──────┬──────┘   └──────┬──────┘
       │                 │
       └────────┬────────┘
                ▼
      ┌──────────────────┐
      │ EXPIRED & LOCKED │
      └──────────────────┘

2) Password Hash 저장 구조의 변화

오라클은 보안성 강화를 위해 버전에 따라 패스워드 검증자(Verifier) 체계를 단계적으로 확장해 왔습니다.

• Oracle 10g 이전: DES 기반의 16진수 대소문자 비구분 해시(DBA_USERS.PASSWORD).
• Oracle 11g: SHA-1 기반의 대소문자 구분 해시(11G Verifier, PASSWORD_VERSIONS 컬럼에 10G 11G 형태로 표시).
• Oracle 12c Release 1 (12.1): SHA-512 기반 해시 도입(12C Verifier).
• Oracle 12c Release 2 (12.2) 이상: PBKDF2 기반의 고강도 파생 키 알고리즘 적용.

 SEC_CASE_SENSITIVE_LOGON 파라미터 주의점

Oracle 11g 이상에서는 기본적으로 SEC_CASE_SENSITIVE_LOGON = TRUE로 설정되어 패스워드 대소문자를 엄격하게 구분합니다. 레거시 시스템 마이그레이션 시 해당 설정 때문에 의도치 않은 로그인 실패가 발생하고 연쇄적으로 ORA-28000 계정 잠김이 발생할 수 있습니다.

2. 프로파일(Profile) 파라미터와 발생 원인 분석

계정이 잠기거나 만료되는 근본 원인은 해당 계정에 매핑된 RESOURCE PROFILE의 보안 파라미터 때문입니다. 별도로 지정하지 않는 한 모든 계정은 기본적으로 DEFAULT 프로파일을 따릅니다.

주요 패스워드 제어 파라미터

• 개발/운영 미분리 환경의 연쇄 Lock: 배치 서버나 WAS의 DB Connection Pool 설정 중 특정 노드 하나에 잘못된 비밀번호가 설정되어 있으면 백그라운드 재접속 시도(Retry)로 인해 순식간에 FAILED_LOGIN_ATTEMPTS를 초과하여 계정이 잠기게 됩니다.
• 기본값 방치로 인한 돌발 만료: 데이터베이스 설치 후 DEFAULT 프로파일의 PASSWORD_LIFE_TIME(보통 180일)을 UNLIMITED로 전환하지 않아, 정확히 6개월 뒤 실서비스 계정이 정지되는 사고가 발생합니다.

3. 실무 트러블슈팅: 단계별 진단 및 복구

Step 1. 문제 계정의 상태 및 프로파일 점검

장애 발생 즉시 계정의 상태, 사용 중인 프로파일, 잠긴 일시를 조회합니다.

SELECT 
    username,
    account_status,
    profile,
    lock_date,
    expiry_date
FROM 
    dba_users
WHERE 
    username = UPPER('&input_username');

해당 계정에 적용된 프로파일의 상세 보안 정책도 함께 확인합니다.

SELECT 
    profile,
    resource_name,
    limit
FROM 
    dba_profiles
WHERE 
    resource_type = 'PASSWORD'
    AND profile = (SELECT profile FROM dba_users WHERE username = UPPER('&input_username'))
ORDER BY 
    resource_name;

Step 2. 단순 계정 잠김 해제 (비밀번호를 알고 있는 경우)

관리자 권한(SYSDBA)으로 접속하여 계정 잠금을 해제하고, 필요 시 비밀번호를 재설정합니다.

-- 1) 단순 Lock 해제 (비밀번호는 그대로 유지)
ALTER USER app_user ACCOUNT UNLOCK;

-- 2) 만료(EXPIRED) 해제 및 신규 비밀번호 변경
ALTER USER app_user IDENTIFIED BY "NewPassword123#";

Step 3. [고급 실무] 기존 비밀번호를 유지하면서 만료 상태 해제하기

실무에서 가장 난감한 상황은 "WAS 애플리케이션 설정 파일에 암호화되어 있어 기존 비밀번호를 모르는데, 패스워드가 만료되어 접속이 안 되는 상황"입니다. 비밀번호를 바꾸면 운영 중인 수십 대의 WAS 인스턴스를 재기동해야 하는 대형 작업이 됩니다.

이때 오라클 내부의 Password Hash Verifier를 활용하여 비밀번호를 변경하지 않고 만료 상태만 OPEN으로 리셋할 수 있습니다.

① Oracle 11g R2 이전 (패스워드 해시 직접 대입)

-- 기존 10g 해시값 백업 조회
SELECT password FROM sys.user$ WHERE name = 'APP_USER';
-- 결과 예: 9A8B7C6D5E4F3210

-- 백업받은 해시값을 IDENTIFIED BY VALUES 절로 재지정 (만료 상태 초기화)
ALTER USER app_user IDENTIFIED BY VALUES '9A8B7C6D5E4F3210';
ALTER USER app_user ACCOUNT UNLOCK;

② Oracle 12c 이상 (동일 비밀번호 재할당 우회)

Oracle 12c 이상에서는 멀티플 해시 구조로 인해 VALUES 절 문법이 제한되는 경우가 많습니다. 만약 기존 비밀번호를 관리자나 개발팀이 알고 있다면, 단순히 기존 비밀번호를 한 번 더 덮어쓰는 것만으로 ACCOUNT_STATUS가 OPEN으로 돌아오며 만료 카운트다운이 리셋됩니다.

-- 기존 비밀번호와 동일한 값을 대입하여 만료 기한 연장
ALTER USER app_user IDENTIFIED BY "CurrentPassword123#";
ALTER USER app_user ACCOUNT UNLOCK;

Step 4. 무중단 비밀번호 전환 (Oracle 19c 이상: Gradual Database Password Rollover)

Oracle 19c(19.12 RU)부터는 애플리케이션 중단 없이 안전하게 비밀번호를 교체할 수 있는 혁신적인 기능이 제공됩니다. 바로 비밀번호 롤오버 유예 기간(Rollover Period) 설정입니다.

-- 이전 비밀번호와 신규 비밀번호가 7일간 동시에 인증되도록 설정
ALTER USER app_user IDENTIFIED BY "NewSecurePassword456#" 
HTTP_DIGEST_ENABLE 
RETAIN BATCH 
PASSWORD ROLLOVER 7 DAYS;

이 기능을 활용하면 무중단 환경에서 DB 비밀번호를 먼저 변경한 뒤, 운영 시간 동안 순차적으로 WAS 인스턴스들의 설정 파일을 수정 및 재기동할 수 있습니다.

4. 재발 방지를 위한 운영 모범 권고안 (Best Practices)

1. 서비스 계정 전용 프로파일 분리 생성

   -- 서비스 계정 전용 무제한 프로파일 생성
   CREATE PROFILE prf_app_service LIMIT
       FAILED_LOGIN_ATTEMPTS UNLIMITED
       PASSWORD_LIFE_TIME UNLIMITED
       PASSWORD_GRACE_TIME UNLIMITED
       PASSWORD_REUSE_TIME UNLIMITED
       PASSWORD_REUSE_MAX UNLIMITED;
   
   -- 운영 계정에 할당
   ALTER USER app_user PROFILE prf_app_service;
   

2. 어플리케이션 연동용 계정에 DEFAULT 프로파일을 부여하는 것은 잠재적 장애 요인입니다. 서비스 계정 전용 프로파일을 별도로 생성하여 만료와 잠김 위험을 원천 차단해야 합니다.
3. 반복적인 FAILED_LOGIN_ATTEMPTS 발생 시 유입 IP 추적

   -- 로그인 실패 Audit 설정 (전통적 감사 기준)
   AUDIT SESSION WHENEVER NOT SUCCESSFUL;
   
   -- 실패 이력 및 접속 시도 터미널/IP 확인
   SELECT 
       username, 
       os_username, 
       userhost, 
       timestamp, 
       returncode 
   FROM 
       dba_audit_session 
   WHERE 
       returncode IN (1017, 28000) 
   ORDER BY 
       timestamp DESC;
   

4. 계정이 특정 주기로 자꾸 잠긴다면 내부 보안 사고이거나 잘못 설정된 배치가 도는 것입니다. 오라클 감사(Audit)나 시스템 트리거를 활용해 실패 로그를 수집해야 합니다.

5. 요약

계정 잠김(ORA-28000)과 만료(ORA-28001)는 발생 즉시 비즈니스 서비스 정지로 이어지는 크리티컬한 이슈입니다.

단순한 잠금 해제에 그치지 않고,

1) 원인이 된 유입 세션 추적,

2) 서비스 전용 프로파일 격리(PASSWORD_LIFE_TIME UNLIMITED), 그리고

3) 비밀번호를 모르는 긴급 상황에서의 해시 기반 복구 기법을 숙지해 두는 것이 안정적인 데이터베이스 운영의 핵심입니다.

오라클 데이터베이스를 운영하다 보면 대용량 배치 처리, 대규모 테이블/인덱스 생성(CTAS, Index Rebuild), 또는 복잡한 집계 쿼리 실행 도중 다음과 같은 에러를 마주치곤 합니다.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

많은 초급 엔지니어들이 이 에러를 마주했을 때 단순히 *"TEMP 테이블스페이스 용량이 찼으니 데이터파일을 늘리거나 자동확장(AUTOEXTEND)을 켜자"*라며 디스크 증설만으로 넘어가려 합니다. 하지만 이는 미봉책에 불과합니다

.

단 하나의 비효율적인 악성 SQL이 수백 GB의 임시 공간을 순식간에 잠식할 수 있으며, 영구 테이블스페이스(Permanent Tablespace)에서 DDL 작업 도중 발생하는 경우도 흔하기 때문입니다.

이번 포스팅에서는 PGA Work Area와 Temp Segment 간의 내부 동작 메커니즘, ORA-01652가 발생하는 근본적인 원인, 그리고 실무 즉시 활용 가능한 단계별 트러블슈팅 및 튜닝 전략을 깊이 있게 정리합니다.

1. 내부 아키텍처: 정렬 메커니즘과 Temp Segment의 생성 원리

오라클이 SQL을 실행할 때 ORDER BY, GROUP BY, DISTINCT, UNION, HASH JOIN 등의 연산 처리를 어떻게 수행하는지

이해해야 ORA-01652의 본질이 보입니다.

1) PGA Work Area와 Pass 단계

정렬이나 해시 연산이 필요할 때, 오라클은 세션 전용 메모리 영역인 PGA(Program Global Area)의 Work Area를 가장 먼저 사용합니다.

• Optimal Pass: 작업 데이터 셋 전체가 할당된 Work Area 메모리 안에서 한 번에 정렬/해시 처리되는 최상의 상태입니다. 디스크 I/O가 전혀 발생하지 않습니다.
• One-Pass: 데이터가 메모리 허용량을 초과하여, 중간 정렬 결과를 디스크의 Temporary Tablespace에 쓰고(Spill to Disk) 다시 읽어 들이는 과정이 1회 발생하는 상태입니다.
• Multi-Pass: 데이터 볼륨이 메모리 크기보다 훨씬 커서 디스크 읽기/쓰기 작업이 여러 차례 반복되는 상태로, 심각한 성능 저하를 초래합니다.

💡 Sort Extent Pool의 동작 특성

Temporary Tablespace는 성능 최적화를 위해 Sort Extent Pool 방식으로 관리됩니다. 한 번 할당된 익스텐트(Extent)는 세션 쿼리가 끝나도 즉시 OS로 반환(Free)되지 않고, 다른 세션이 재사용할 수 있도록 풀에 남아있습니다. 따라서 단순 dba_free_space 조회로는 정확한 가용량을 볼 수 없고 v$temp_extent_pool이나 v$sort_usage를 통해 실제 사용 중(Active)인 블록을 확인해야 합니다.

2) 왜 세그먼트 확장 에러가 발생하는가?

Multi-Pass 또는 대량 정렬로 인해 디스크의 Temp 영역에 Extent를 추가 할당해야 할 때:

1. 임시 테이블스페이스 내 재사용 가능한 Free Extent가 완전히 고갈되고,
2. 데이터파일이 고정 크기(Fixed)이거나, AUTOEXTEND가 켜져 있어도 파일 시스템(OS 디스크)의 물리 볼륨이 100% Full에 도달했을 때

오라클 커널은 더 이상 익스텐트를 할당할 수 없어 ORA-01652를 발생시키고 실행 중이던 트랜잭션을 강제 중단(Rollback)합니다.

⚠️ 주의: 영구 테이블스페이스(USERS, DATA 등)가 메시지에 찍히는 이유

ORA-01652: unable to extend temp segment by 128 in tablespace USERS와 같이 일반 데이터 테이블스페이스가 찍히는 경우가 있습니다. 이는 CREATE INDEX, ALTER TABLE ... MOVE와 같은 DDL 작업 시, 최종 오브젝트로 확정되기 전까지 데이터가 임시 세그먼트(Temporary Segment) 형태로 해당 테이블스페이스에 생성되기 때문입니다. 즉, 이 경우는 Temp가 아닌 해당 영구 테이블스페이스의 실제 디스크 공간 부족 문제입니다.

2. 주요 발생 원인 3가지

• 악성 SQL로 인한 대량 Sort / Hash Spill (가장 빈번)
• 조인 조건 누락으로 인한 카티시안 곱(Cartesian Product), 잘못된 실행 계획으로 인한 대량 Full Table Scan 후 대규모 Hash Join, 과도한 DISTINCT 및 정렬 연산.
• 임시 테이블스페이스 물리적 사이징 미흡
• 비즈니스 데이터 증가량 대비 Tempfile 크기가 작게 고정되어 있거나, AUTOEXTEND 옵션의 MAXSIZE 제한에 걸린 경우.
• 대용량 DDL 및 통계정보 수집 작업의 중복 수행
• 피크 시간대 대규모 인덱스 재생성(Index Rebuild), 파티션 분할 작업, 병렬도(DOP)를 높게 부여한 CTAS 작업이 동시에 수행되며 Temp Extent를 일시적으로 독점한 경우.

3. 실무 트러블슈팅: 4단계 진단 및 해결 가이드

장애 알람이 발생했을 때 신속하게 원인을 규명하고 조치할 수 있는 단계별 프로세스입니다.

Step 1. 실시간 Temp 공간 점유 세션 및 SQL 추적

가장 먼저 어떤 세션이, 어떤 SQL로 Temp 공간을 독점하고 있는지 파악해야 합니다.

-- 현재 Temporary Tablespace를 사용 중인 세션 및 SQL_ID 조회
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    u.tablespace,
    ROUND((u.blocks * dt.block_size) / (1024 * 1024), 2) AS used_mb,
    s.sql_id,
    SUBSTR(q.sql_text, 1, 80) AS sql_text
FROM 
    v$sort_usage u
JOIN 
    v$session s ON u.session_addr = s.saddr
JOIN 
    dba_tablespaces dt ON u.tablespace = dt.tablespace_name
LEFT JOIN 
    v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
ORDER BY 
    used_mb DESC;

• 만약 시스템 전체를 마비시키는 명백한 개발/배치 쿼리의 실수(예: 카티시안 조인)라면, DBA 판단하에 해당 세션을 Kill하여 가용 공간을 즉시 확보할 수 있습니다.

• ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  

Step 2. 임시 테이블스페이스 구성 및 파일 상태 점검

Temp 데이터파일의 용량 설정 및 자동 확장 여부를 확인합니다.

-- Tempfile 크기 및 Autoextend 설정 상태 점검
SELECT 
    file_id,
    file_name,
    tablespace_name,
    ROUND(bytes / (1024 * 1024), 2) AS current_mb,
    autoextensible,
    ROUND(maxbytes / (1024 * 1024), 2) AS max_mb,
    increment_by * (SELECT block_size FROM dba_tablespaces WHERE tablespace_name = dtf.tablespace_name) / (1024 * 1024) AS next_extent_mb
FROM 
    dba_temp_files dtf
ORDER BY 
    tablespace_name, file_id;

Step 3. 응급 스페이스 확보 (온라인 증설)

스토리지(파일시스템)에 물리적 여유 공간이 있다면, 서비스 중단 없이 즉시 파일을 확장하거나 신규 파일을 추가합니다.

-- 1) 기존 Tempfile 크기 수동 확장
ALTER DATABASE TEMPFILE '/oracle/oradata/prod/temp01.dbf' 
RESIZE 20G;

-- 2) 자동 확장 속성 변경 (필요 시)
ALTER DATABASE TEMPFILE '/oracle/oradata/prod/temp01.dbf' 
AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- 3) 신규 Tempfile 추가 (I/O 병목 분산 목적 권장)
ALTER TABLESPACE TEMP 
ADD TEMPFILE '/oracle/oradata/prod/temp02.dbf' 
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

Step 4. 근본 대책: SQL 튜닝 및 PGA 적정성 검토

스페이스 증설은 시간을 벌어주는 응급처치일 뿐입니다. 근본적인 원인은 비효율적인 SQL 실행 계획에 있습니다.

① 문제 SQL의 실행 계획 분석

• SORT (ORDER BY) 또는 HASH JOIN 단계에서 Cardinality가 과도하게 뻥튀기되고 있지 않은지 확인합니다.
• 조인 조건 누락이나 잘못된 인덱스 설계로 인한 대용량 Full Table Scan을 방지해야 합니다.
• 불필요한 DISTINCT, 과도한 ORDER BY, 중복 제거가 필요 없는 곳의 UNION을 UNION ALL로 변경합니다.

② PGA Target 사이징 점검

인스턴스 전체의 정렬 작업 대비 PGA 메모리가 부족하여 과도한 디스크 정렬(Multi-Pass)이 발생하는지 점검합니다.

-- PGA 캐시 히트율 및 오버할당 횟수 확인
SELECT 
    ROUND(pga_target_for_estimate / (1024 * 1024), 0) AS target_mb,
    pga_target_factor,
    estd_pga_cache_hit_percentage AS estd_hit_ratio,
    estd_overalloc_count
FROM 
    v$pga_target_advice;

estd_pga_cache_hit_percentage가 낮고 estd_overalloc_count가 지속적으로 발생한다면, 시스템 메모리 여유분을 확인한 후 pga_aggregate_target 증설을 검토해야 합니다.

4. 운영 모범 권고안 (Best Practice)

1. 임시 테이블스페이스 그룹(Temporary Tablespace Group) 활용

   ALTER TABLESPACE TEMP1 TABLESPACE GROUP TEMP_GRP;
   ALTER TABLESPACE TEMP2 TABLESPACE GROUP TEMP_GRP;
   ALTER USER scott TEMPORARY TABLESPACE TEMP_GRP;
   

2. 대규모 동시 세션 환경이나 RAC 환경에서는 단일 Temp Tablespace에 경합이 집중될 수 있습니다. 여러 개의 Temp Tablespace를 하나의 Group으로 묶어 사용자/서비스별로 할당하면 I/O 병목을 효과적으로 분산할 수 있습니다.
3. 대용량 인덱스 리빌드 시 고려사항
4. ALTER INDEX ... REBUILD 작업 시 공간 절약을 위해 가급적 온라인 작업 여부 및 영구 테이블스페이스 여유 공간을 사전에 철저히 검증해야 합니다.
5. PGA_AGGREGATE_LIMIT 가드레일 (Oracle 12c 이상)
6. 악성 쿼리가 시스템 메모리를 무한정 잠식해 OS 레벨 OOM(Out of Memory) 크래시를 유발하지 않도록, pga_aggregate_limit 파라미터가 적정선으로 방어되어 있는지 반드시 체크해야 합니다.

5. 마치며

ORA-01652 에러는 단순히 스토리지의 용량 이슈가 아닌, SQL 실행 효율성과 메모리 아키텍처가 복합적으로 얽힌 경고 신호입니다.

장애 발생 시 침착하게 v$sort_usage를 통해 원인 세션과 SQL_ID를 먼저 특정한 뒤, 응급 증설과 더불어 Multi-Pass 정렬을 유발한 악성 SQL을 튜닝하는 접근법이 가장 건강한 데이터베이스 운영 관리의 핵심입니다.

오라클 데이터베이스 모니터링 중 평소 수 초 내에 끝나던 대용량 배치(Batch)나 조회 쿼리가 특정 시점부터 급격히 느려지며 AWR 리포트 최상위 대기 이벤트에 direct path read 또는 direct path read temp가 올라오는 현상을 종종 목격합니다.

일반적으로 "Direct Path Read는 SGA 버퍼 캐시를 거치지 않고 디스크에서 세션 PGA로 바로 읽어오므로 더 빠르고 효율적인 I/O 방식"으로 알려져 있습니다. 그런데 왜 실무 현장에서는 이 대기 이벤트가 시스템 병목과 성능 저하의 주범으로 지목될까요?

이번 포스팅에서는 Direct Path Read의 메모리 우회 원리, direct path read와 direct path read temp의 차이, 통계정보 왜곡 및 옵티마이저 판단 메커니즘, 그리고 실무 트러블슈팅과 제어 전략을 상세히 파헤쳐 봅니다.

1. 내부 아키텍처: Buffer Cache 경유 I/O vs Direct Path I/O

오라클이 데이터 블록을 디스크에서 읽어올 때 사용하는 경로는 크게 두 가지입니다.

[ Conventional Path Read ]
Disk I/O ──> SGA (Database Buffer Cache) ──> PGA (User Session)
             * Latch/Lock 경합 발생 (CBC Latch, Buffer Busy Waits)
             * 캐시 재사용성 확보

[ Direct Path Read ]
Disk I/O ──────────────────────────────────> PGA (User Session)
             * Buffer Cache 완전 우회
             * 캐시 오염 방지 및 메모리 복사 오버헤드 감소

1) Conventional Path Read (일반 경로 읽기)

전통적인 블록 읽기 방식입니다. 데이터를 디스크에서 읽어 먼저 SGA의 Database Buffer Cache에 적재한 뒤, 세션의 PGA로 전달합니다.

• 장점: 한번 캐시에 올라간 블록은 이후 동일 쿼리나 타 세션에서 메모리 속도로 재사용(Logical Read)됩니다.
• 단점: 대용량 테이블을 읽을 경우 캐시 내의 다른 유용한 블록을 밀어내는 캐시 오염(Cache Pollution)이 발생하며, cache buffers chains 래치 경합 및 buffer busy waits가 동반될 수 있습니다.

2) Direct Path Read (직접 경로 읽기)

버퍼 캐시를 완전히 우회하여, OS 디스크 블록을 세션 전용 메모리인 PGA로 직접 스트리밍합니다.

• 배경: 11g 이전에는 병렬 쿼리(PX - Parallel Execution)나 LOB 데이터, 백업(RMAN) 작업 등에서만 제한적으로 발생했습니다.
• 11g의 패러다임 변화 (Serial Direct Path Read): Oracle 11g R2부터 옵티마이저는 단일 세션(Serial)의 Full Table Scan이나 Fast Full Index Scan에서도 대상 테이블의 크기가 버퍼 캐시의 일정 비율을 넘어서면 옵티마이저가 자체 비용 계산을 통해 자동으로 Direct Path Read를 선택하도록 기본 동작 방식이 변경되었습니다.

Direct Path Read 발생 시 필수 사전 작업: Object-Level Checkpoint

버퍼 캐시를 우회하여 디스크에서 직접 데이터를 읽으려면, 현재 메모리에만 존재하는 더티 블록(Dirty Block)의 최신 데이터 정합성이 보장되어야 합니다. 따라서 오라클은 Direct Path Read를 시작하기 직전, 해당 테이블에 속한 더티 블록을 디스크로 내려쓰는 **오브젝트 레벨 체크포인트(Object-level Checkpoint)**를 먼저 유발합니다. 이 과정에서 enq: RO - fast object checkpoint 대기나 DBWR 부하가 선행될 수 있습니다.

2. 두 대기 이벤트의 명확한 차이

모니터링 툴에 찍히는 두 이벤트는 발생 원인과 대상 세그먼트가 완전히 다릅니다.

1) direct path read

• 대상 공간: 일반 영구 테이블스페이스(Permanent Tablespace)의 데이터파일.
• 의미: 테이블이나 인덱스 데이터를 읽는 과정에서 버퍼 캐시를 건너뛰고 디스크 블록을 직접 읽는 상태입니다.
• 원인: 대용량 Full Table Scan, 대용량 병렬 쿼리(PX), Exadata의 Smart Scan(Cell Offload) 등.

2) direct path read temp

• 대상 공간: 임시 테이블스페이스(Temporary Tablespace)의 Tempfile.
• 의미: PGA Work Area 메모리 부족으로 인해 디스크 Temp 영역에 쏟아부었던(Spill) 중간 정렬/해시 데이터를 다시 PGA로 읽어 들이는 상태입니다.
• 원인: 대용량 ORDER BY, GROUP BY, DISTINCT, 대용량 HASH JOIN 과정에서 발생하는 Multi-Pass 정렬/해시 연산.

3. 실무 장애 사례: 왜 성능이 급격히 저하되는가?

Direct Path Read가 디스크 I/O 최적화 기술임에도 불구하고, 성능 장애의 핵심 원인으로 지목되는 전형적인 시나리오 3가지입니다.

1) 통계정보 왜곡으로 인한 옵티마이저의 오판

테이블 통계정보가 오래되어 실제로는 수천만 건에 달하는 대용량 테이블인데 카디널리티(Rows)가 작게 등록되어 있거나, 반대로 작은 테이블이 비정상적인 블록 수로 계산되어 있는 경우입니다.

• 옵티마이저는 비용 계산 오류로 인해 인덱스 스캔 대신 Serial Direct Path Full Table Scan을 선택합니다.
• 결과적으로 수십~수백 GB의 디스크 블록 전체를 매 실행마다 디스크 I/O로 퍼 올리며 스토리지 I/O 대역폭을 완전히 고갈시킵니다.

2) 스토리지 I/O 병목 및 버퍼 캐시 재사용성 상실

작은 조회라도 버퍼 캐시를 거치면 수십만 번 실행되어도 메모리 히트율 99%를 유지하며 수 ms 만에 종료됩니다.

그러나 Direct Path Read로 결정되면 반복 실행되는 빈번한 쿼리임에도 불구하고 캐시를 전혀 쓰지 못하고 매번 물리적 디스크 I/O를 수행합니다. 이로 인해 스토리지 I/O 처리량이 한계에 도달하고 쿼리 응답시간이 수십 초~수 분으로 폭증합니다.

3) 과도한 Temp 디스크 I/O (direct path read temp)

조인 조건 누락이나 잘못된 인덱스로 인해 카티시안 곱이 발생하거나, PGA 크기를 초과하는 초대형 Hash Join이 실행되면 세션이 감당할 수 없는 Temp Read/Write가 발생하며 시스템 전체 I/O를 마비시킵니다.

4. 실무 진단 및 트러블슈팅

Step 1. 실시간 Direct Path Read 발생 세션 및 SQL 추적

현재 Direct Path Read 대기 이벤트로 인해 병목을 겪고 있는 활성 세션과 SQL_ID를 즉시 식별합니다.

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.event,
    s.p1text, s.p1 AS file_no,
    s.p2text, s.p2 AS start_block,
    s.p3text, s.p3 AS blocks_read,
    s.sql_id,
    ROUND(s.wait_time_micro / 1000, 2) AS wait_time_ms,
    SUBSTR(q.sql_text, 1, 80) AS sql_text
FROM 
    v$session s
JOIN 
    v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE 
    s.status = 'ACTIVE'
    AND s.event IN ('direct path read', 'direct path read temp')
ORDER BY 
    wait_time_ms DESC;

Step 2. 문제 세그먼트(테이블/인덱스) 특정

대기 이벤트 파라미터 P1(File#)과 P2(Block#)를 활용하여 세션이 접근 중인 물리 오브젝트를 확인합니다.

SELECT 
    owner,
    segment_name,
    partition_name,
    segment_type,
    tablespace_name
FROM 
    dba_extents
WHERE 
    file_id = &p1_file_no
    AND &p2_block_no BETWEEN block_id AND block_id + blocks - 1;

Step 3. 실행 계획(Execution Plan) 및 통계정보 확인

해당 SQL이 의도치 않게 Full Table Scan을 타며 Direct Path Read를 유발하고 있는지 확인합니다.

-- 캐시된 커서의 실제 실행 계획 및 라인별 통계 확인
SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

실행 계획에서 다음 패턴이 보이는지 점검해야 합니다:

• TABLE ACCESS FULL 또는 INDEX FAST FULL SCAN
• Notes 섹션에 dynamic statistics used 또는 Cardinality 예측치와 실제 A-Rows의 극단적 괴리
• 조인 단계의 HASH JOIN 부하

Step 4. 해결 및 제어 전략

① 옵티마이저 통계정보 갱신 (가장 우선되는 근본 조치)

테이블 및 인덱스 통계정보를 최신화하여 CBO(비용 기반 옵티마이저)가 올바른 실행 계획(인덱스 레인지 스캔 등)을 수립하도록 유도합니다.

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'SCOTT',
        tabname          => 'BIG_ORDERS',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => 4
    );
END;
/

② 힌트를 통한 경로 강제 제어

단기적으로 쿼리가 버퍼 캐시를 타도록 유도하거나 Direct Path Read를 억제해야 할 경우 힌트를 적용합니다.

• 인덱스 유도: /*+ INDEX(t 테이블_인덱스명) */
• 캐시 적재 유도: /*+ CACHE(t) */ (풀 스캔 시 블록을 버퍼 캐시의 LRU 끝단이 아닌 일반 헤드에 적재)

③ _serial_direct_read 히든 파라미터 제어 (주의 필요)

오라클 11g 이후 도입된 직렬 Direct Path Read 메커니즘은 _serial_direct_read 히든 파라미터로 제어됩니다.

-- 현재 파라미터 값 확인
SELECT 
    ksppinm AS parameter,
    ksppstvl AS value,
    ksppdesc AS description
FROM 
    x$ksppi x
JOIN 
    x$ksppcv y ON x.indx = y.indx
WHERE 
    ksppinm = '_serial_direct_read';

• 값 구성:
  • AUTO (기본값): 옵티마이저가 테이블 블록 수, 버퍼 캐시 크기(_small_table_threshold), Dirty 블록 비율 등을 계산하여 자동 결정.
  • NEVER: 직렬 쿼리에서 Direct Path Read를 비활성화하고 과거 10g 방식처럼 무조건 버퍼 캐시를 경유하도록 강제.
  • ALWAYS: 직렬 풀 스캔 시 무조건 Direct Path Read 수행.

파라미터 변경 권고안

인스턴스 전체 레벨(ALTER SYSTEM)에서 _serial_direct_read = NEVER로 변경하는 것은 전체 시스템의 버퍼 캐시 경합을 증폭시킬 수 있으므로 권장되지 않습니다. 특정 배치 세션이나 장애 쿼리에 한해 세션 레벨 또는 SQL 패치 레벨로 제한 적용하는 것이 안전합니다.

-- 세션 단위 제한적 적용
ALTER SESSION SET "_serial_direct_read" = NEVER;

5. 운영 모범 권고안 (Best Practices)

1. OLTP vs DW/배치 환경의 Workload 분리 : 빈번하게 트랜잭션이 발생하는 OLTP 환경에서는 Direct Path Read가 캐시 적중률을 떨어뜨려 독이 될 수 있습니다. 반면 일회성 대용량 집계가 주를 이루는 DW/배치 환경에서는 캐시 오염을 막아주는 핵심 최적화 요소입니다. 두 성격의 워크로드가 혼재된 시스템이라면 파티셔닝과 인덱스 설계를 분리해야 합니다.
2. _small_table_threshold 임계치 이해 : 오라클은 내부적으로 _small_table_threshold(통상 버퍼 캐시 크기의 약 2%)보다 작은 테이블은 메모리 캐시를 경유하고, 이보다 큰 테이블은 Direct Path Read 대상으로 검토합니다. 버퍼 캐시(SGA) 크기를 동적으로 늘릴 때 이 임계치도 함께 확장된다는 점을 사이징 설계 시 고려해야 합니다.
3. PGA Work Area 사이징과 direct path read temp 방어 : direct path read temp가 빈번하다면 디스크 I/O 문제가 아니라 PGA 부족 및 비효율적인 조인/정렬 로직이 원인입니다. V$PGA_TARGET_ADVICE를 점검하여 PGA_AGGREGATE_TARGET을 증설하거나, 쿼리의 UNION을 UNION ALL로 변경하고 조인 순서를 개선해야 합니다.

6. 요약

direct path read는 그 자체로 에러나 버그가 아니라, 오라클이 메모리 오염을 방지하고 대용량 데이터를 처리하기 위해 제공하는 고도화된 I/O 메커니즘입니다.

하지만 왜곡된 통계정보나 잘못 작성된 SQL로 인해 빈번한 조회가 Direct Path Read로 빠지게 되면 시스템 전체 스토리지가 마비되는 심각한 병목을 초래합니다.

현장에서 이 이벤트를 마주한다면 단순히 파라미터로 우회하려 들기 전에, V$SESSION과 실행 계획을 통해 어떤 테이블이 왜 풀 스캔되고 있는지 원인을 규명하고 통계정보 최신화 및 SQL 튜닝을 선행하는 것이 최선의 해결책입니다.