데이터베이스 아키텍처

SQL 튜닝은 결국 "메모리와 디스크 사이에서 블록을 얼마나 적게 읽느냐"의 문제다. 그 출발점으로 Oracle이 어떤 구성 요소로 이루어져 있고, 데이터를 어떤 단위로 저장하는지 정리한다.

데이터베이스와 인스턴스

Oracle에서 데이터베이스와 인스턴스는 서로 다른 개념이다.

flowchart TB
  U["사용자 프로세스<br/>(SQL*Plus, 애플리케이션)"] --> S["서버 프로세스<br/>+ PGA"]
  subgraph INST["인스턴스"]
    S --> SGA["SGA<br/>DB 버퍼 캐시 · 공유 풀 · 로그 버퍼"]
    BG["백그라운드 프로세스<br/>DBWn · LGWR · CKPT · SMON · PMON"] --- SGA
  end
  SGA <--> DB[("데이터베이스<br/>데이터 파일 · 리두 로그 · 컨트롤 파일")]
  classDef hl fill:#fef3c7,stroke:#f59e0b,color:#451a03
  class SGA hl

• 일반적인 구성은 인스턴스 1개 : 데이터베이스 1개다.
• RAC(Real Application Cluster) 환경에서는 여러 인스턴스가 하나의 데이터베이스를 공유한다. 각 인스턴스의 버퍼 캐시는 캐시 퓨전(Cache Fusion)으로 서로 블록을 주고받는다.

시험 포인트: 인스턴스는 메모리 + 프로세스, 데이터베이스는 디스크 파일이다. RAC는 "다수 인스턴스 : 하나의 데이터베이스" 구조다.

SQL Server와의 차이

SQL Server는 하나의 인스턴스 안에 여러 개의 데이터베이스를 두는 구조다. master, model, msdb, tempdb 같은 시스템 데이터베이스와 사용자 데이터베이스가 한 인스턴스에서 함께 운영된다. 데이터 파일은 주 데이터 파일(.mdf), 보조 데이터 파일(.ndf), 트랜잭션 로그 파일(.ldf)로 구분한다.

메모리 구조 SGA와 PGA

SGA 구성 요소

PGA 구성 요소

• Sort Area: ORDER BY, GROUP BY, Sort Merge Join 등 정렬 작업 공간. 부족하면 Temp 테이블스페이스를 사용한다 (Disk Sort).
• Hash Area: Hash Join의 Build Input(해시 맵)을 만드는 공간.
• 세션 정보, 커서 상태 정보: 바인드 변수 값, 커서 실행 상태 등.

프로세스 구조

서버 프로세스

사용자 프로세스(클라이언트)의 요청을 받아 실제로 SQL을 처리한다. SQL 파싱, 최적화, 실행, 블록 읽기, 결과 전송을 담당한다. 처리 중 디스크 I/O나 다른 프로세스의 작업을 기다려야 하면 대기 이벤트(Wait Event)를 남기고 잠시 멈춘다.

주요 백그라운드 프로세스

시험 포인트: 더티 블록을 디스크에 쓰는 것은 DBWn, 리두 로그를 쓰는 것은 LGWR이다. 서버 프로세스가 직접 데이터 파일에 쓰지 않는다는 점을 묻는 문제가 나온다.

버퍼 캐시·Redo·Undo의 관계

DML이 실행될 때 Oracle은 데이터를 바로 디스크에 쓰지 않는다. 메모리에서 변경하고, 변경 내역은 리두 로그로 남긴다.

Redo의 목적

LGWR가 리두 로그 버퍼를 기록하는 시점은 다음과 같다.

• 3초마다
• DBWn이 더티 블록을 기록하기 전에 신호를 보낼 때
• 로그 버퍼의 1/3이 차거나 기록되지 않은 Redo가 1MB를 넘을 때
• 사용자가 커밋 또는 롤백할 때

Undo의 목적

시험 포인트: Redo는 앞으로 재실행(Roll Forward), Undo는 되돌리기(Rollback)와 읽기 일관성이다. 읽기 일관성은 Undo의 목적이라는 점을 꼭 기억한다. 읽기 일관성의 자세한 원리는 동시성 제어를 참고한다.

체크포인트

체크포인트는 버퍼 캐시의 더티 블록을 데이터 파일에 기록해 메모리와 디스크를 동기화하는 시점이다. 체크포인트가 자주 일어나면 인스턴스 복구 시 적용할 Redo가 줄어 복구 시간이 짧아지지만, 디스크 쓰기가 늘어난다.

데이터 저장 구조

Oracle의 저장 구조는 논리 구조와 물리 구조로 나뉜다.

flowchart LR
  TS["테이블스페이스"] --> SEG["세그먼트<br/>(테이블·인덱스·파티션)"]
  SEG --> EXT["익스텐트<br/>(연속된 블록 묶음)"]
  EXT --> BLK["데이터 블록<br/>(I/O 최소 단위)"]
  TS -.-> DF[("데이터 파일<br/>물리 구조")]
  classDef hl fill:#fef3c7,stroke:#f59e0b,color:#451a03
  class BLK hl

꼭 구분할 관계

• 하나의 세그먼트는 하나의 테이블스페이스에 속한다. 단, 파티션 테이블은 파티션마다 다른 테이블스페이스에 둘 수 있다 (파티션이 각각 세그먼트이기 때문).
• 하나의 테이블스페이스는 여러 데이터 파일로 구성되므로, 한 세그먼트가 여러 데이터 파일에 걸쳐 저장될 수 있다.
• 하나의 익스텐트는 하나의 데이터 파일 안에 있다. 익스텐트 안의 블록은 서로 인접해 있다.
• 세그먼트 공간이 부족하면 테이블스페이스로부터 익스텐트 단위로 공간을 추가 할당받는다.

블록 단위 I/O의 의미

DBMS는 레코드 하나를 읽고 싶어도 그 레코드가 속한 블록 전체를 읽는다. 컬럼 하나만 SELECT해도 마찬가지다. 그래서 SQL 성능은 "몇 건을 읽느냐"보다 "몇 개의 블록을 읽느냐" 로 결정된다. 자세한 내용은 데이터베이스 I/O 메커니즘에서 다룬다.

블록의 내부 구조와 ROWID

블록은 헤더, 테이블 디렉터리, 로우 디렉터리, 빈 공간, 로우 데이터로 구성된다. 각 레코드의 위치는 ROWID로 식별한다.

ROWID = 오브젝트 번호 + 데이터 파일 번호 + 블록 번호 + 로우 번호

인덱스는 키 값과 함께 이 ROWID를 저장하고, 인덱스를 통해 테이블 레코드를 찾아갈 때 ROWID로 블록을 찾는다. 자세한 내용은 테이블 랜덤 액세스를 참고한다.

테이블스페이스 종류

핵심 요약

• 데이터베이스는 디스크 파일의 집합, 인스턴스는 SGA + 백그라운드 프로세스다. RAC는 다수 인스턴스가 하나의 DB를 공유한다.
• SGA는 모든 프로세스가 공유(래치 필요), PGA는 서버 프로세스 전용 메모리(래치 불필요)다.
• SGA는 DB 버퍼 캐시, 공유 풀(라이브러리 캐시·딕셔너리 캐시), 리두 로그 버퍼로 구성된다.
• 더티 블록은 DBWn, 리두 로그는 LGWR가 기록한다. 커밋은 LGWR가 Redo를 기록하면 완료된다.
• Redo의 목적은 Database Recovery, Cache Recovery, Fast Commit이다.
• Undo의 목적은 Transaction Rollback, Transaction Recovery, Read Consistency다.
• 저장 구조는 블록 → 익스텐트 → 세그먼트 → 테이블스페이스 순이며, 블록이 I/O 최소 단위다.
• 익스텐트는 한 데이터 파일 안에 있고, 세그먼트는 여러 데이터 파일에 걸칠 수 있다.