[SQLP] 1-1. Oracle 아키텍처

🧷 Oracle 아키텍처

Oracle 은 데이터베이스와 이를 액세스 하는 프로세스 사이에 SGA(System Global Area)라고 하는 공유 메모리 캐시 영역을 두어 구성된다.

• 데이터베이스(Database) : 디스크에 저장된 데이터 집합으로, 주요 파일로는 데이터 파일(Datafile), Redo Log 파일, Control 파일이 포함된다.
• 인스턴스(Instance) : SGA (공유 메모리 영역)와 이를 액세스 하는 프로세스 집합으로 구성된다.

기본적으로 Oracle에서는 하나의 데이터베이스에 대해 하나의 인스턴스가 접근하지만, RAC(Real Application Cluster) 환경에서는 하나의 데이터베이스를 여러 개의 인스턴스가 동시에 액세스 할 수 있다.

 

✅ RAC (Real Application Cluster)

과거에는 여러 개의 인스턴스가 동일한 데이터 파일을 공유하는 공유 디스크(Shared Disk) 방식을 사용했지만, 현재는 기존 모델을 확장한 공유 캐시(Shared Cache) 방식을 지원하며, 이를 위해 Global Cache 개념을 적용한다

• Global Cache 활용
  • 각 인스턴스는 로컬 캐시에 없는 데이터 블록을 이웃 노드에서 전송 받아 사용할 수 있다.
  • 이를 위해 각 Instance를 고속의 전용 네트워크로 연결한다.
• Dirty 버퍼 관리
  • 다른 인스턴스에서 갱신하고 아직 커밋되지 않은 (Active 상태의)블록도 디스크를 거치지 않고 네트워크를 통해 Dirty 버퍼 (메모리와 디스크간에 동기화되지 않은 버퍼) 상태로 서로 주고받으며 갱신된다.
  • 이를 통해 동기화를 최적화 하고 성능을 극대화한다.
• 장점
  • 고가용성 : 특정 노드에 장애가 발생해도 다른 노드가 지속적인 서비스를 제공한다.
  • 확장성 : 인스턴스를 추가하여 성능을 확장가능 하다.

✅ SQL Server 아키텍처

• 하나의 인스턴스당 최대 32,767개의 데이터베이스를 정의하여 사용가능하다.
• 설치 시 기본적으로 master, model, msdb, tempdb 등의 시스템 데이터베이스가 생성되며, 여기에 사용자 데이터베이스를 추가로 생성하는 구조이다.
• 데이터베이스를 생성할 때 주 데이터 파일(main) 과 트랜잭션 로그 파일 이 반드시 포함된다.
• 파일 확장자
  • 주 데이터 파일: .mdf (Main Data File)
  • 트랜잭션 로그 파일: .ldf (Log Data File)
  • 보조 데이터 파일: .ndf (데이터가 많을 경우 추가 가능)

 

🖇️ 프로세스

Oracle의 프로세스 집합은 크게 서버 프로세스(Server Process)와 백그라운드 프로세스(Background Process)로 나뉜다.

• Server Process : 전면에서 사용자가 던지는 요청을 수행하는 프로세스
• Background Process : 직접 사용자에게 보이지 않지만, 내부적으로 주어진 역할 수행하는 프로세스

✅ SQL Server

• SQL Server은 스레드(Thread)기반 아키텍처이므로 프로세스 대신 쓰레드 표현을 사용한다.
• 서버 프로세스에 해당하는 작업을 Worker Thread가 담당한다.

1. Server Process

(1) 주요 역할

• 각 클라이언트를 위한 전용 서버 프로세스가 생성되어 사용자 요청을 처리한다.
• SQL을 파싱(parsing) 하고, 필요시 최적화(optimization)를 수행한 후, 커서(cursor)를 열어 SQL을 실행한다.
• 데이터 블록을 읽고, 데이터를 정렬한 후, 클라이언트가 요청한 결과 집합(Result Set)을 생성하여 네트워크를 통해 전송한다.
• 직접 처리할 수 없는 작업은 OS, I/O 서브시스템, 백그라운드 프로세스 등에 대신 처리하도록 시스템 Call을 통해 요청한다.
  • 예시:
    • 데이터 파일에서 DB 버퍼 캐시로 데이터 블록을 적재
    • Dirty 블록(수정되었지만 아직 디스크에 기록되지 않은 블록)을 캐시에서 밀어내고 Free 블록 확보
    • Redo 로그 버퍼를 디스크에 기록하여 트랜잭션의 무결성 유지

(2) 클라이언트 간의 연결 방식

① 전용 서버 방식 (Dedicated Server Mode)

• 클라이언트가 리스너(Listener)를 통해 연결 요청을 보내면, 하나의 개별 서버 프로세스가 생성되고, PGA(Program Global Area) 메모리가 할당된다.
• 새로운 요청마다 별도의 프로세스가 생성되므로 비용이 높은 방식이다.
• SQL을 실행할 때마다 새로운 연결을 생성하면 성능 저하가 발생하므로, Connection Pooling 기법을 적용해야 한다.

✅ Connection Pool
→ 최초 연결을 맺은 후, 작업이 끝나도 이를 해제하지 않고 애플리케이션 서버에서 유지(pooling) 하여, 필요할 때 재사용하는 방식

 

② 공유 서버 방식 (Shared Server Mode)

• 하나의 서버 프로세스를 여러 사용자 세션이 공유하는 방식
• 미리 여러 개의 서버 프로세스를 띄워놓고, 각 클라이언트가 이를 필요할 때마다 재사용한다.
• Connection Pooling 기법을 DBMS 내부에 구현한 방식과 유사하다.

③ 전용 서버 방식 vs 공유 서버 방식

• 전용 서버 방식: 성능이 높지만, 동시 접속자가 많을 경우 리소스 사용량이 증가하여 서버 부담이 커질 수 있다.
  • OLTP(짧고 빈번한 트랜잭션, 실시간 데이터 처리 환경)에서는 빠른 응답 속도와 성능 최적화가 중요하므로 Connection Pooling 기법을 활용해야 한다.
• 공유 서버 방식: 서버 자원을 절약할 수 있지만, 여러 사용자가 동일한 프로세스를 공유하므로 CPU 부하가 높거나 복잡한 SQL을 실행할 경우 성능이 저하될 수 있다.

2. Background Process

프로세스	설명
SMON (System Monitor)	장애 발생 후 재가동 시 인스턴스 복구를 수행하며, 임시 세그먼트 및 익스텐트를 모니터링한다.
PMON (Process Monitor)	이상이 생긴 프로세스가 사용하던 리소스를 복구한다.
DBWn (Database Writer)	버퍼 캐시에 있는 Dirty 블록을 데이터 파일에 기록한다.
LGWR (Log Writer)	Redo 로그 버퍼의 데이터를 Redo 로그 파일에 기록한다.
ARCn (Archiver)	Redo 로그가 덮어쓰기 되기 전에 Archive 로그 디렉토리에 백업한다.
CKPT (Checkpoint)	마지막 Checkpoint 이후의 데이터 변경 사항을 데이터 파일에 기록하도록 트리거하며, 기록 완료 후 Checkpoint 정보를 컨트롤 파일과 데이터 파일헤더에 저장한다.
RECO (Recoverer)	분산 트랜잭션 과정에 발생한 문제를 해결한다.

 

🖇️ 데이터 베이스 저장 구조

1. Datafile

 

(1) Physical

Data File : 디스크 상의 물리적인 OS 파일

• 데이터는 물리적으로 데이터 파일에 저장된다.
• 사용자가 직접 데이터 파일을 선택할 수 없으며, 데이터 파일 할당 및 관리는 DBMS가 자동으로 처리한다.

(2) Logical

① Tablespace : 세그먼트를 담는 컨테이너

• 데이터를 저장하는 가장 큰 논리적인 단위이다.
• 여러 개의 데이터 파일로 구성되어 있다.
• 테이블스페이스를 여러 데이터 파일로 구성하면 파일 결합을 줄일 수 있어 성능을 향상할 수 있다.
• 사용자는 세그먼트가 저장될 테이블스페이스를 지정할 수 있지만, 테이블스페이스 내에서 실제 데이터 파일을 선택하고 익스텐트를 할당하는 것은 DBMS가 자동으로 처리한다.
• 테이블 스페이스와 세그먼트는 1 : N 관계
  • 즉, 하나의 테이블스페이스에 여러 개의 세그먼트가 포함될 수 있다.
  • 하지만, 하나의 세그먼트가 여러 테이블스페이스에 걸쳐 저장될 수 없다.
  • 다만, 하나의 세그먼트는 여러 데이터 파일에 걸쳐 저장될 수 있다. (테이블스페이스가 여러 데이터 파일로 구성되어 있기 때문)

② Segment : 데이터 저장 공간을 구성하는 단위

• 테이블, 인덱스 등의 오브젝트(Object)가 데이터를 저장하기 위해 필요한 공간이다.
• 하나 이상의 Extent로 구성되며, 저장공간을 필요로 한다는 것은 한 개 이상의 Extent를 사용함을 의미한다.
• 특정 세그먼트에 할당된 모든 익스텐트는 해당 세그먼트가 속한 테이블스페이스 내에서만 존재할 수 있다.
  • 즉, 하나의 세그먼트는 여러 테이블스페이스에 걸쳐 저장될 수 없다.
• 세그먼트의 공간이 부족해지면 테이블스페이스에서 새로운 Extent를 추가 할당받을 수 있다.
  • 이때, 새로운 익스텐트는 기존과 동일한 데이터 파일이 아닐 수도 있다.
  • 익스텐트가 여러 데이터 파일에 흩어지면 디스크 경합이 줄어들고 I/O가 분산되는 효과를 얻을 수 있다.
• 오브젝트와 세그먼트는 1:1 관계
  • 파티션과는 1:M 관계
  • 파티션 테이블(또는 인덱스)을 생성하면, 각 파티션별로 별도의 세그먼트가 생성된다.
• LOB 컬럼(Large Object, CLOB, BLOB 등)은 별도의 세그먼트로 저장되며, 해당 테이블과 다른 테이블스페이스에 저장될 수도 있다.

③ Extent : 공간을 확장하는 단위

• 여러 개의 Block으로 구성된 연속된 공간이다.
• 익스텐트 내 블록들은 연속적이지만, 익스텐트끼리는 반드시 연속된 공간이 아니다.
• 하나의 익스텐트는 하나의 오브젝트가 독점적으로 사용한다.
  • 즉, 한 익스텐트에 속한 모든 블록을 동일한 오브젝트의 데이터만 저장 가능하다.
  • Oracle과 달리 SQL Server의 경우, 2개 이상의 오브젝트가 나누어 사용 가능하다.
• 공간이 부족하면 테이블스페이스로부터 추가 익스텐트를 할당받을 수 있다.
  • 추가되는 익스텐트는 기존 익스텐트와 연속되지 않을 수 있다.
  ④ Block : 데이터를 읽고 쓰는 단위
• DBMS에서 데이터를 읽고 쓰는 가장 작은 단위이다.
• I/O 단위로 사용되며, 한 블록은 하나의 오브젝트만 독점적으로 사용 가능하다.
• 블록 단위 I/O
  • 하나의 레코드를 읽더라도 해당 레코드가 속한 전체 블록을 읽어야 한다.
  • 따라서 성능 최적화의 핵심은 불필요한 블록 접근을 최소화하는 것이다.
  • 옵티마이저(Optimizer)의 판단에서 블록 액세스 개수가 가장 중요한 요소이다.
• 블록 주소값 (DBA: Data Block Address): 데이터 블록은 몇 번째 데이터 파일의 몇 번째 블록인지 나타내는 고유한 주소값을 가진다.
• DBMS별 블록 크기
  • Oracle: 2KB, 4KB, 8KB, 16KB, 32KB 등 다양한 블록 크기 사용 가능
  • SQL Server: 8KB 블록(페이지) 크기 사용 → 익스텐트(Extent)는 항상 64KB(8개 블록)

2. 임시 데이터 파일

• 기능 : 대량의 정렬 또는 해시 작업을 수행하는 동안, 메모리 공간이 부족할 경우 중간 결과 집합을 저장하는 역할을 한다.
• 특징
  • 임시 저장 후, 자동 삭제 처리
  • Redo 정보를 생성하지 않으므로, 문제 발생 시 복구되지 않으며 백업이 필요하지 않는다.
• DBMS 별 특징
  • Oracle : 임시 테이블스페이스를 여러 개 생성할 수 있으며, 사용자마다 별도로 지정 가능
  • SQL Server : 단 하나의 tempdb 데이터베이스를 사용하며, tempdb는 전역 리소스로 모든 사용자의 임시 데이터를 저장한다.

3. 로그 파일

Data file과 Control 파일에 가해지는 모든 변경사항은 Redo 로그 엔트리로 저장되며, 이는 Redo 로그 파일에 기록된다.

(1) 로그 파일 구조

Redo 로그 파일은 Online Redo 로그와 Archived(=Offline) Redo 로그로 구성된다.

 

① Online Redo 로그

Online Redo 로그는 Redo 로그 버퍼에 버퍼링 된 로그 엔트리를 기록하는 파일이다. 마지막 체크 포인트 이후부터 장애 발생 직전까지 수행된 트랜잭션들을 재현하는 데 사용된다.

• 캐시에 저장된 변경 사항이 아직 데이터 파일에 기록되지 않은 상태에서 정전 등으로 인스턴스가 비정상 종료되면, 작업 내용을 잃게 된다. 이러한 유실을 방지하기 위해 Online Redo 로그가 사용된다.
• 최소 2개 이상의 파일로 구성된다.
• 현재 사용 중인 Redo 로그 파일이 가득 차면 다음 파일로 자동 전환하는 로그 스위칭(Log Switching)이 발생한다.
• 모든 Redo 로그 파일이 가득 차면 라운드 로빈(Round-Robin) 방식으로 첫 번째 파일부터 다시 덮어쓴다. Archived(=Offline) Redo 로그는 Online Redo 로그가 덮어쓰이기 전에 백업된 파일이다. 디스크 손상 등 물리적 장애(미디어 장애) 발생 시 데이터베이스 복구에 사용된다.
• ② Archived(=Offline) Redo 로그

✅ SQL Server

SQL Server의 트랜잭션 로그 파일(Transaction Log File)은 Oracle의 Online Redo 로그와 유사한 역할을 한다.

• 주 데이터 파일당 하나의 트랜잭션 로그 파일이 생성된다.
• 확장자 : .ldf
• 내부적으로 가상로그 파일이라는 더 작은 단위의 세그먼트로 분할된다.
  • 조각화 방지를 위해 로그 파일 크기를 넉넉하게 설정하거나 증가 단위를 크게 지정하는 것이 일반적이다.
    
    

(2) 로그 파일의 역할

① Database(=Media) Recovery

 

Redo 로그 파일은 디스크 장애(Media Failure) 발생 시 데이터베이스 복구에 사용된다. 이때, Archived Redo 로그 파일을 이용하여 데이터 복구를 수행한다.

 

② Cache(=Instance) Recovery (instance Recovery 시 roll forward 단계)

 

대부분의 DBMS는 I/O 성능을 향상하기 위해 버퍼 캐시(Buffer Cache)를 사용한다. 그러나 버퍼 캐시는 휘발성 메모리 이므로, 데이터 파일에 반영되지 않은 상태에서 정전 등 인스턴스가 비정상 종료 되면 작업 내용이 유실된다. 이러한 트랜잭션 데이터 유실을 방지하기 위해 Redo 로그 파일을 사용하여 시스템 종료 이전 상태로 복구(Instance Recovery)한다.

 

③ Fast Commit

 

데이터 변경사항을 디스크의 데이터 블록에 즉시 기록하는 작업은 Random 액세스 방식이므로 속도가 느리다. 반면, 로그 기록은 Append 방식이루어지므로 상대적으로 빠르다.

❗Append 방식
중간에 버퍼 캐시(Buffer Cache)를 경유하지 않고 바로 데이터 블록에 액세스할 수 있어 적재 시간이 단축된다.

 

따라서, 트랜잭션 발생 시 매번 데이터 파일에 직접 기록하는 대신, 변경 사항을 Append 방식으로 빠르게 Redo 로그 파일에 기록한다. 이후, 메모리 데이터 블록과 데이터 파일 간 동기화는 적절한 수단(예: DBWR, Checkpoint)을 이용해 Batch 방식으로 일괄 수행한다.

즉, 변경 사항이 메모리상의 버퍼 블록에만 기록된 상태에서 아직 디스크에 기록되지 않았더라도, Redo 로그 파일을 믿고 빠르게 Commit을 수행할 수 있다. 이러한 메커니즘을 Fast Commit이라고 부른다.

• 이러한 기능은 Oracle뿐만 아니라 빠른 트랜잭션 처리가 필요한 대부분의 DBMS에서 공통적으로 사용되는 메커니즘이다.

(3) Fast Commit

✔️ Fast Commit 메커니즘

1. 사용자가 Commit을 실행한다.
2. 서버 프로세스는 Commit 레코드를 Redo 로그 버퍼에 기록한다.
3. LGWR 프로세스는 이를 즉시 트랜잭션 로그 엔트리와 함께 Redo 로그 파일에 저장한다.
4. Commit을 수행한 서버 프로세스에 성공 응답을 반환한다.

여기까지 완료되면 아직 사용자의 갱신 내용이 디스크에 기록되지 않았지만 Instance Crash 가 발생하더라도 Redo 로그를 이용해 언제든 복구가능한 상태가 되었으므로 오라클은 안심하고 커밋을 완료할 수 있는 것이다. 즉, Fast Commit의 핵심은 3번이라 할 수 있다.

 

① Log Force at Commit

 

Redo 로그 버퍼는 일정 주기로 로그 파일에 기록되지만, 최소한 커밋 시점에는 반드시 Redo 로그 파일에 기록되어야 한다. 이를 Log Force at Commit 이라고 한다. 메모리에 있는 로그버퍼는 유실될 가능성이 있기 때문에 이 과정은 트랜잭션의 영속성을 보장하기 위해 필수 적인 과정이다.

 

② Write Ahead Logging

 

버퍼 캐시 블록을 갱신하기 전에 반드시 Redo 로그 버퍼에 먼저 기록돼야 하며, DBWR가 Dirty 버퍼를 디스크에 기록하기 전에 LGWR가 해당 Redo 엔트리를 먼저 Redo 로그 파일에 기록했음을 보장되어야 한다.. 이를 Write Ahead Logging 이라고 한다.

 

인스턴스 장애가 발생 시, 로그 파일을 이용해 데이터 복구를 수행하고 최종적으로 커밋되지 않은 트랙잭션은 롤백 처리 해야 한다. 로그 파일에 기록되지 않은 변경 내역이 이미 데이터 파일에 저장된 경우, 사용자가 커밋하지 않은 데이터가 영구적으로 저장될 수 있다. 그렇기 때문에 Redo 로그 파일에는 반드시 데이터 변경 내역이 선기록 되어야 한다.

 

🖇️ 메모리 구조

Oracle의 메모리 구조는 SGA(System Global Area)와 PGA(Process Global Area)로 구분된다.

System Global Area(SGA) : 여러 프로세스가 동시에 액세스 할 수 있는 공유 메모리 영역이다.

• SGA를 구성하는 주요 캐시 영역
  • DB 버퍼 캐시 (DB Buffer Cache): 디스크 I/O 최소화를 위해 데이터 블록을 캐싱
  • 공유 풀 (Shared Pool): SQL 실행 계획, 라이브러리 캐시, 딕셔너리 캐시 저장
  • 로그 버퍼 (Redo Log Buffer): Redo 로그 데이터를 저장하여 빠른 트랜잭션 처리를 지원
  • 추가적으로 Large Pool, Java Pool 등도 포함되며, 시스템 구조와 제어 정보를 캐싱하는 영역이 존재한다.
• 여러 프로세스와 공유되므로 내부적으로 동시 액세스를 제어하는 메커니즘이 필요하다.
  • 래치(Latch), 버퍼 Lock, 라이브러리 캐시 Lock/Pin등을 사용하여 액세스 직렬화 한다.
• SQL Server에서는 Memory Pool이 SGA의 역할을 담당한다.

Process Global Area(PGA) : 개별 서버 프로세스가 가지는 전용 메모리 영역

• 데이터를 정렬하고(Sort Area), 세션과 커서에 대한 상태 정보 저장한다.
• SGA와 다르게 래치 메커니즘이 필요 없으며, 이로 인해 SGA보다 훨씬 빠르게 데이터에 접근할 수 있다.
• SQL Server은 스레드 기반 아키텍처이므로, PGA을 갖지 않는다.
  • 스레드는 독립적인 메모리 공간을 가지지 않고, 부모 프로세스의 메모리 영역을 공유한다.

1. DB 버퍼 캐시 (DB Buffer Cache)

Oracle은 SGA 공유 메모리를 활용하여 빠른 데이터 입출력을 수행한다. 사용자가 입력한 데이터를 데이터 파일에 저장하고 다시 읽는 과정에서 거치는 캐시 영역을 DB 버퍼 캐시라고 한다.

(1) 블록 단위 I/O

DB 버퍼 캐시는 블록 단위로 I/O를 수행한다. 즉, 하나의 레코드에서 단 하나의 컬럼을 읽고자 하더라도, 해당 레코드가 속한 블록 전체를 읽어야 한다.

• 모든 블록 I/O 작업은 Direct Path Read 메커니즘을 제외, 반드시 DB 버퍼 캐시를 거쳐야 한다.
• 데이터 변경 작업도 버퍼 캐시에 적재된 블록을 통해 수행되며, 변경된 블록(Dirty 버퍼 블록)은 DBWR 프로세스가 주기적으로 데이터 파일에 기록한다.(2) 버퍼 캐시 구조

DB 버퍼 캐시는 해시 테이블(Hash Table) 구조로 관리된다. 데이터 블록을 해싱하기 위한 키 값으로 DBA(Data Block Address, 데이터 블록 주소)를 사용한다.

 

① 버퍼 블록 검색 과정

1. 버퍼 캐시에서 먼저 데이터 블록을 검색한다.
2.  검색할 블록 주소를 해시 값으로 변환한 후, 해당 해시 버킷(Hash Bucket)에서 체인을 따라 스캔한다.
3. 버퍼 캐시에 데이터 블록이 존재하면 즉시 반환, 없을 경우 디스크에서 로드한다.
   • 이때, 디스크에서 블록을 읽어와 버퍼 캐시에 적재하여 해시체인에 연결한 후 읽는다.

② 해시 체인 (Hash Chain) 구조

• Hash bucket → Hash Chain → Hash Header
• DBA(데이터 블록 주소)를 해시 함수에 입력하여 해시 값 생성하여 같은 값을 가진 블록들은 해시 버킷(Hash Bucket)에 연결 리스트로 저장한다.
• 각각의 연결 리스트를 해시 체인(Hash Chain)이라고 한다.
• 버퍼 헤더(Buffer Header)는 버퍼에 대한 메타 정보, 버퍼 메모리 영역의 실제 버퍼에 대한 포인터 값을 가지고 있다.
  • 버퍼 블록 자체가 해시 구조로 연결되는 것이 아니라, 버퍼 헤더(Buffer Header)가 해시 체인에 연결된다.
  • 실제 데이터는 버퍼 헤더의 포인터를 통해 버퍼 블록에서 조회한다.

③ 캐시 버퍼 체인

각 DB 버퍼는 DBA가 해시 함수에 의해 해시되어 Hash Tabel에 할당되어 관리되는 데, 이를 Cache Buffer Chain List라 한다.

• 캐시 버퍼 체인 List란 양방향의 링크된 리스트로, 인스턴스가 시작될 때 할당되는 Hash Table로 구성된다.
• Hash Table 안의 Bucket은 각 DB 버퍼들의 Header 정보를 가지며, 이 각 Buffer들은 LRU List 나 Dirty List의 한 가지에 속한다.

④캐시 버퍼 LRU 체인

버퍼 헤더는 해시 체인뿐 아니라 LRU 체인에 의해서도 연결되어 있다. DB 버퍼 캐시는 유한한 자원이므로 모든 데이터를 캐싱할 수 없다. 따라서 자주 사용되는 데이터 블록을 유지하고, 사용 빈도가 낮은 블록은 제거하는 방식으로 관리된다. 이때 사용하는 알고리즘이 LRU(Least Recently Used)알고리즘 이다.

 

《 특징 》

• 모든 버퍼 블록을 헤더를 LRU 체인에 연결하여 사용 빈도순으로 정렬한다.
• Free 버퍼가 필요한 경우, 가장 사용 빈도가 낮은 블록부터 제거하여 새로운 데이터를 적재한다.
• 모든 버퍼 블록은 Dirty 혹은 LRU 리스트 중 하나에 반드시 속한다.
• 읽기(Read) 또는 쓰기(Write) 작업을 위해 액세스 하는 동안에는 리스트에서 일시적으로 제외된다.
• 작업 완료 후, 다시 원래의 리스트로 연결되거나, 최초 변경이 발생하였을 때는 Dirty 리스트로 옮겨간다.

《 구성 》

• Dirty 리스트 (LRUW List / LRU Write List)
  • 변경되었지만 아직 디스크에 기록되지 않은 Dirty 버퍼 블록을 관리한다.
  • DBWR 프로세스가 Dirty 리스트의 블록을 디스크에 기록 후 Free 버퍼로 변환한다. 이때, LRU List의 끝부분에 위치하게 된다.
• LRU 리스트
  • 아직 Dirty 리스트로 옮겨지지 않은 나머지 버퍼 블록을 관리한다.
  • LRU 알고리즘에 의해 사용 빈도가 높은 블록은 유지되고, 낮은 블록은 메모리에서 없어지도록 관리한다.

《 버퍼 상태 》

1. Free 버퍼 (Clean Buffer)
   • 비어 있는(Unused) 상태 또는 데이터 파일과 동기화된 상태
   • 언제든지 덮어 써도 무방한 깨끗한(Clean) 버퍼 블록
   • 새로운 데이터 블록을 로딩하려면 먼저 Free 버퍼를 확보해야 한다.
   • Free 상태의 버퍼가 변경될 경우, Dirty 버퍼로 전환한다.
2. Dirty 버퍼 (Dirty Buffer)
   • 버퍼에 캐시 된 이후 데이터가 변경되었지만 아직 디스크에 기록되지 않은 상태
     - 디스크의 데이터 파일과 동기화(Synchronization)가 필요한 버퍼 블록
     - Dirty 버퍼는 다른 데이터 블록을 위해 재사용되려면 먼저 디스크에 기록돼야 한다.
     - 디스크에 기록되는 순간, 다시 Free 버퍼로 전환된다.
3. Pinned 버퍼 (Pinned Buffer)
   • 현재 읽기(Read) 또는 쓰기(Write) 작업이 현재 진행 중인 버퍼 블록
   • 특정 프로세스가 작업을 완료할 때까지 다른 프로세스는 해당 버퍼에 접근 불가

(3) 동시 액세스 관리

Oracle의 SGA(System Global Area)는 공유 메모리 영역이므로, 여러 프로세스가 동시에 버퍼 블록을 액세스 할 수 있다. 하지만 동시 접근으로 인한 충돌을 방지하기 위해 액세스 직렬화가 필요하며, 이를 위해 래치(Latch) 와 버퍼 Lock 이 사용된다.

 

① Latch
래치는 특정 리소스를 보호하는 잠금(Locking) 메커니즘으로, 래치를 획득한 프로세스만이 해당 자료구조에 접근할 수 있도록 한다. 이를 통해 동시 접근을 직렬화하여 데이터 충돌을 방지한다.

• cache buffers chains Latch
  • 해시 체인을 보호하여 데이터 충돌을 방지한다.
  • 여러 프로세스가 동일한 해시 체인에 접근하는 것을 직렬화(Serialization) 처리한다.
  • 래치를 획득하는 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기하게 된다.
• cache buffers chains lru Latch : LRU 리스트를 보호하기 위해 사용된다.

② 버퍼 Lock
버퍼 Lock은 개별 버퍼 블록을 보호하기 위한 잠금 메커니즘으로, 읽기(Read) 또는 변경(Write) 작업을 수행하는 프로세스는 반드시 버퍼 Lock을 획득해야 한다. 버퍼 Lock을 획득한 후, 즉시 Latch 해제 처리하여 Latch를 오래 유지하지 않도록 한다.

 

《 Lock 모드 》

• Share 모드 (Shared Lock) → 읽기(Read) 작업 시 여러 프로세스가 공유 가능
• Exclusive 모드 (Exclusive Lock) → 변경(Write) 작업 시 한 프로세스만 획득 가능
  • Exclusive Lock은 한 번에 하나의 프로세스만 획득 가능하다. (액세스 직렬화 처리)

《 대기 이벤트 》

프로세스가 cache buffers chains Latch를 획득한 후, 원하는 버퍼 블록이 이미 Exclusive Lock 상태라면?

1. 래치를 계속 유지하면 안 되므로, 버퍼 Lock 대기자 목록(Waiter List)에 등록하고 래치를 해제한다.
2. 버퍼 Lock이 해제되면 대기 중이던 프로세스가 Lock을 획득하고 작업 수행한다.

이 과정에서 발생하는 대기 이벤트가 "buffer busy waits" 이벤트이다.

 

2. 공유 풀 (Shared Pool)

공유 풀(Shared Pool)은 딕셔너리 캐시(Dictionary Cache)와 라이브러리 캐시(Library Cache)로 구성되며, LRU 알고리즘을 사용하여 관리된다.

 

✅ SQL Server에서는 프로시저 캐시(Procedure Cache)라고 한다.

(1) 딕셔너리 캐시 (Dictionary Cache)

• 데이터베이스 딕셔너리 정보를 캐싱하는 메모리 영역이다.
• 데이터 베이스 딕셔너리 : 테이블, 인덱스, 테이블스페이스, 테이터파일, 세그먼트, 익스텐트, 사용자, 제약, Sequence, DB Link에 관한 정보를 저장한다.
• 일반적인 데이터는 데이터 파일에 저장되었다가 버퍼 캐시를 통해 읽히지만, 메타 정보는 데이터 베이스 딕셔너리에 저장되며, 이를 딕셔너리 캐시를 통해 빠르게 조회할 수 있다.
• 블록 단위가 아닌 Row 단위로 읽고 쓰기 때문에 로우 캐시라고도 불린다.

(2) 라이브러리 캐시 (Library Cache)

• 사용자가 수행한 SQL 문, 실행 계획, 저장 프로시저 등을 저장하는 캐시 영역이다.
• 라이브러리 캐시는 같은 SQL 문이 여러 번 실행될 경우, 반복적인 하드 파싱을 최소화하기 위한 캐시 공간이다.
• 하드 파싱은 시스템 성능에 부하를 주는 작업이므로, 이를 줄이기 위해 바인드 변수 사용 및 라이브러리 캐시의 효율적 관리가 중요하다.

3. Redo 로그 버퍼(Redo Log Buffer)

Redo 로그 버퍼는 데이터베이스에서 발생하는 모든 변경 사항을 저장하는 메모리 공간이다. 데이터 변경 사항을 기록할 때, 즉시 Redo 로그 파일에 저장하는 것이 아니라 먼저 Redo 로그 버퍼에 기록된다.

• 데이터 블록 버퍼를 변경하기 전에 항상 Redo 로그 버퍼에 먼저 기록해야만 한다.
• 로그 엔트리를 파일에 곧바로 기록하는 것이 아니라 먼저 로그 버퍼에 기록하는 것은 일정량 모았다가 기록하는 것이 속도가 훨씬 빠르기 때문이다.
• 일정 시점마다 LGWR 프로세스 가 Redo 로그 버퍼의 내용을 Redo 로그 파일에 기록한다.
• 시스템을 모니터링하면, 사용자가 커밋 또는 롤백할 때마다 log file sync 대기 이벤트 가 발생하는 것을 볼 수 있다.
  • 이는 LGWR 프로세스가 로그 버퍼 내용을 Redo 로그 파일에 기록할 때까지 서버 프로세스가 대기하는 현상 때문이다.

✔️ Redo 로그 파일 기록 시점

• 3초마다 DBWR 프로세스로부터 신호를 받을 때
  • DBWR은 Dirty 버퍼를 데이터 파일에 기록하기 전에 로그 버퍼 내용을 Redo 로그 파일에 기록하도록 LGWR에게 신호를 보낸다.
• 로그 버퍼의 1/3이 차거나 기록된 Redo 레코드의 크기가 1MB를 초과할 때
• 사용자가 커밋 또는 롤백 명령을 실행할 때

📌 Undo

Undo 세그먼트는 일반적인 테이블 세그먼트와 구조적으로 비슷하며,

• 익스텐트 단위로 확장되며
• 빠른 읽기/쓰기를 위해 Undo 블록을 버퍼 캐시에 캐싱하며
• 데이터 유실 방지를 위해 변경사항을 Redo 로그에 기록한다.

단, Undo 세그먼트는 저장하는 내용이 다르다. 각 트랜잭션 별로 Undo 세그먼트를 할당하며, 트랜잭션이 수행한 테이블과 인덱스의 변경사항을 Undo 레코드 단위로 저장한다.

 

① Undo 사용 목적

• Transaction Rollback : 트랜잭션을 커밋하지 않고 롤백할 경우, Undo 데이터를 이용해 변경 사항을 되돌린다.
• Transaction Recovery - Instance Recovery 시 rollback 단계
  • Instance Crash 후, Redo를 이용해 Roll Forward 단계가 완료되면 최종 커밋되지 않은 변경사항까지 모두 복구된다. (Redo 로그 파일 기록 시점은 사용자가 커밋 또는 롤백 명령을 실행할 때 이외 있기 때문)
  • 그러나 커밋되지 않은 변경 사항은 롤백해야 하므로, Undo 데이터를 이용해 원래 상태로 되돌린다.
• Read Consistency :
  • Undo 데이터는 읽기 일관성(Read Consistency)을 보장하기 위해 사용된다.
  • 다중 사용자 환경에서 한 사용자가 데이터를 변경하는 동안, 다른 사용자는 변경 전 데이터(Undo 블록에 저장된 이전 값)를 읽을 수 있도록 제공한다.
  • DB2, SQK Server, Sybase는 Locking 기법을 이용해 읽기 일관성을 구현하지만, 오라클은 Undo 데이터를 이용해 구현한다.

② vs Redo

• Redo : Redo 로그를 이용하여 장애 발생 후 커밋된 변경 사항을 복구한다.
• Undo : Undo 데이터를 이용하여 롤백 또는 읽기 일관성을 위해 변경 전 상태로 복구한다.

 

Reference

• 친절한 SQL 튜닝
• 오라클 성능고도화 원리와 해법 1
• SQL 전문가 가이드