[SQL튜닝/인덱스와 조인] Oracle DBMS 구조 (Database, Instance

[SQL튜닝/인덱스와 조인] Oracle DBMS 구조 (Database, Instance - SGA, Background Process, PGA)

Se On 2025. 1. 6. 19:08

🖥️ 들어가며

📌 Oracle Database를 크게 나누면 다음과 같습니다.
- Instance와 Database → Database: Data files, Control files, Redo Log files → Instance: SGA, Background Process
- SGA: Shared Pool(Library Cache, Data Dictionary Cache), Data base buffer cache, Redo Logo Buffer
- PGA와 SGA / Parameter file, Password file, Archived Log files

✏️ 1-1. Oracle Database - Instance - Background Process

⭐⭐⭐ PMON, SMON, DBWR, LGWR, CKPT

PMON: Process Monitor
- 비정상 종료된 프로세스를 정리하고 자원을 해제합니다.
SMON: System Monitor
- 인스턴스 복구, 임시 세그먼트 정리 등의 작업을 수행합니다.
- Recovery에는 Media recovery*와 Instance recovery가 있습니다.
  - Media recovery: 데이터 파일 복구를 위한 로그 적용 과정
  - Instance recovery: 인스턴스의 비정상 종료 후 복구를 자동으로 수행
- Instance recovery: 인스턴스 비정상 종료 후 재시작 시 자동으로 수행합니다.
  1) Roll forward → 2) Roll back → 3) Check Point
  - 1) Roll forward: Redo Log를 사용해서 커밋된 변경사항을 Data file에 적용합니다.
    (메모리에 있던 변경사항 중 디스크에 기록되지 않은 것들을 복구)
  - 2) Roll back: Commit 되지 않은 트랜잭션을 Undo 데이터를 사용하여 롤백합니다.
    (완료되지 않은 트랜잭션으로 인한 데이터 불일치 방지)
  - 3) Check Point: 복구 과정은 아니지만 복구의 시작점을 결정하는 중요한 요소입니다.
    - 데이터 동기화: 메모리(버퍼 캐시)와 더티 버퍼(변경된 데이터)를 디스크의 Data file에 기록합니다. (메모리와 디스크간 데이터 일관성 유지)
    - 복구 시간 단축: 복구 시작점을 설정합니다. 장애 발생 시 마지막 체크포인트 이후의 변경사항만 복구하면 되므로 전체 복구 시간이 단축됩니다.
    - SCN 기록: 체크 포인트 발생 시, 현재 System Change Number가 Data file 헤더와 Control File에 기록합니다.
    - Check Point 프로세스 (1 → 3 → 2 → 4)
      (1) CKPT 프로세스: CKPT 프로세스가 체크포인트 요청을 시작합니다.
      (2) DBWR 활성화: DBWR에 더티 버퍼를 디스크에 쓰도록 지시합니다.
      (3) LGWR 활성화: 이때 LGWR가 먼저 활성화되어 필요한 Redo Log 정보를 기록합니다.
      (4) 트랜잭션 큐: 체크포인트 동안 새로운 트랜잭션들은 큐에 쌓입니다. 이를 통해 체크포인트 중에도 데이터베이스 작업이 계속될 수 있습니다.
  - Check Point를 만난 시점: 메모리와 디스크가 동기화된 시점을 보장합니다.

*Media recovery: 백업에서 Data file을 복원한 후, Archived Log와 Online Redo Log를 적용하여 데이터를 최신 상태로 복원하는 과정입니다.

DBWR: Database Writer
- 버퍼 캐시의 더티 버퍼를 Data file에 기록합니다.
- 비동기적으로 동작합니다.
- 작동 조건: 체크포인트 발생 시, 버퍼 캐시 공간 부족 시, LGWR가 트랜잭션 커밋 시
- Datafiles, Data Buffer Cache와 짝꿍입니다.
LGWR: Log Writer
- Redo Log Buffer의 내용을 Rego Log Files에 기록합니다.
- 주기적으로 동작합니다.
- 파일이 가득 차면 다음 파일로 전환합니다. (로그 스위치, Redo Log files의 순환적인 특성에 따라 발생)
- 작동 시점: DBWR와 독립적으로 Redo Log Files에 기록, 트랜잭션 커밋 시
- Redo Log files, Redo Log Buffer와 짝꿍입니다.
CKPT: Check Point
- 체크포인트 발생 시 관련 정보를 관리합니다.
- 주요 기능: 체크포인트 정보를 컨트롤 파일과 데이터 파일 헤더에 기록, DBWR 활성화, SCN과 체크포인트 정보를 연계합니다.

✏️ 1-2-1. Oracle Database - Instance - SGA - Shared Area

⭐⭐⭐ Shared Pool, Library Cache, Dictionary Cache

Shared Area == Shared Pool
- SQL 문장의 파싱 정보를 저장하고 재사용해 데이터베이스 성능을 향상시킵니다.
- 파싱 유형
  - 하드 파싱: 새로운 SQL 문장을 처음 실행
  - 소프트 파싱: 이미 파싱된 SQL 문장을 재사용, 하드 파싱에 비해 빠르고 효율적
Library Cache
- SQL 문장, PL/SQL 블록, 실행 계획 등을 저장합니다.
Dictionary Cache (Data Dictionary Cache)
- 데이터베이스 객체에 대한 메타데이터를 저장합니다.
- 테이블, 인덱스, 사용자, 권한 등의 정보를 포함합니다.

✏️ 1-2-2. Oracle Database - Instance - SGA

⭐⭐⭐ Data Buffer Cache, Redo Log Buffer

Data Buffer Cache
- 데이터베이스의 데이터 블록을 메모리에 캐시하는 영역입니다.
- 디스크 I/O를 줄여 성능으 향상시킵니다.
Redo Log Buffer
- 데이터베이스 변경 사항을 임시로 저장하는 메모리 영역입니다.
- 주로 Insert, Delete, Update문과 같은 DML문에 의한 변경사항을 기록합니다.
- 작동 방식
  - DML문 발생 시
    : 변경 사항은 Redo Log Buffer, Data Buffer Cache에 동시에 기록됩니다.
    - Redo Log Buffer: 트랜잭션 복구를 위해 변경 사항 기록
    - Data Buffer Cache: 메모리에 데이터를 캐싱하여 실제 데이터 블록을 변경
  - Commit 발생 시
    : Redo Log Buffer의 내용이 Redo Log files에 기록됩니다.
  - 데이터베이스 복구 시
    : Redo Log files의 정보를 사용하여 변경사항을 재현합니다.

UPDATE 사원
SET 부서 = '경리부'
WHERE 부서 = '자금부';

위 쿼리문이 실행되는 순서
- 1) Redo Log Buffer에 변경 정보 기록
- 2) Data Buffer Cache에 변경 사항 반영 (1과 동시 기록)
- 3) Commit하면 Redo Log files에 변경 내용 기록
성능 최적화
- DB Buffer Cache에 Write 쓰기는 블록 단위로 이루어집니다.
  - ex: 8K[1Blcok] * 20개의 블록 = 160K 데이터 처리 필요
- Redo Log Buffer는 Append 방식*으로 소량의 Write*만으로 데이터 정합성 보장 가능합니다.
  - 데이터 정합성 보장, 빠름, I/O 부하를 줄임

*Append 방식: 데이터를 기존 내용을 덮어쓰지 않고, 새로운 위치에 추가하는 방식 (순차적 기록 가능)

*소량의 Write: Redo Log Buffer가 효율적인 기록을 위해 I/O를 줄이고, 모든 변경사항을 순차적으로 기록

그외
- Java Pool: Java 실행을 위한 메모리 영역
- Large Pool: 백업/복구 작업 및 병렬 작업 지원

✏️ 2. Oracle Database - Database

⭐⭐⭐ Data files, Control files, Redo Log files, Archived Log files

Data files
- Oracle Database의 실제 데이터를 저장하는 물리적 파일입니다.
- 사용자가 저장한 모든 데이터, 스키마, 인덱스, 테이블 구조가 저장되어 있습니다.
Control files
- 데이터베이스의 물리적 구조에 대한 정보를 포함하는 이진 파일입니다.
- Data file, Redo log file의 경로가 저장되어 있습니다.
Redo Log files
- 데이터베이스의 모든 변경 사항을 기록하는 순환 로그 파일입니다.
Archived Log files
- Rodo Log files이 가득 차면 로그 스위치가 일어나면서 Archived Log files에 기록합니다.
- Redo Log가 DB의 역사라면, Archived Log files은 역사 박물관이라고 볼 수 있습니다.
- Redo Log files을 계속해서 백업해두는 곳으로, 복구할 때 사용합니다. (온라인 리두 로그의 복사본)
Parameter file
- 인스턴스나 DB의 모든 옵션 종류를 기록합니다. (데이터베이스 인스턴스의 구성 정보 저장)
- Control 파일의 경로가 있습니다.
- DB가 복구되는 단계에서 사용합니다.
Password file
- 데이터베이스 관리자 권한(SYSDBA)을 가진 사용자의 인증 정보를 저장합니다.

✏️ 3. Oracle Database - PGA

Clinet - User Process
- 세션마다 Server Process가 있습니다.
Server Process
- PGA: Server Process가 독점적으로 사용하는 메모리 영역입니다.

PGA: Program Global Area
- 공유 X, 작음, 빠름
- 다른 프로세스와 공유되지 않습니다.
- SGA 대비 크기가 작습니다.
- 접근 속도는 SGA보다 빠릅니다.

SGA: System Global Area
- 공유 O, 큼, 경합, 락, 대기 => 동시성 제어 필요
- 모든 데이터베이스 프로세스가 공유하는 대규모 메모리 영역입니다.
- PGA들의 총합보다 일반적으로 더 큽니다.
- 주요 특징
  - 경합: 여러 프로세스가 동시에 접근하려 하므로 경쟁이 치열합니다.
  - 락(lock): 특정 영역에 접근하기 위해 락을 획득해야 합니다.
  - 대기: 다른 프로세스가 사용 중일 경우 대기해야 합니다.

📒 정리하면

Oracle Database → Database / Instance
Database: Datafiles, Control files, Redo Log files (Archived Log files, Parameter file, Password file)
Instance: SGA / Background Process
SGA: Shared Pool(Library Cache, Data Dictionary Cache), Data base Buffer Cache, Redo Log Buffer
Background Process: PMON, SMON, DBWR, LGWR, CKPT
PGA vs. SGA

참고자료
- https://codingmoonkwa.tistory.com/216