🖥️ 들어가며
📌 데이터베이스 성능 튜닝의 3대 핵심 요소: ⭐⭐⭐Li Call IO
- Li: A/B
- Call: A/A
- IO: OLTP는 인덱스/Batch는 PPT DDL
✏️ 1. 데이터베이스 성능 튜닝의 3대 핵심 요소
- ⭐⭐⭐
Li Call IO
: A/B, A/A, OLTP는 인덱스/Batch는 PPT DDL
- DB 성능 튜닝 핵심 1: Library 캐시 최적화 (SQL 파싱 부하 해소)
- 목표: SQL 파싱(Parsing) 오버헤드 감소시킵니다.
- Application Cursor Caching
- 세션 커서를 PGA에 캐싱 → 동일 SQL 재실행 시 Open/Close를 생략하여 → Soft Parsing 횟수를 감소시킵니다. - Bind 변수 활용
- Hard Parsing을 방지해, CPU 사용량과 Shared Pool 경합을 감소시킵니다.
-- 1. Application Cursor Parsing
-- 세션 커서 수 설정
ALTER SYSTEM SET session_cached_cursors = 50;
-- 2. Bind 변수 사용
-- 바인드 변수 미사용: 매번 Hard Parsing
SELECT * FROM emp WHERE id = 100;
SELECT * FROM emp WHERE id = 200;
-- 바인드 변수 사용: 1회 Hard Parsing 후 재사용
SELECT * FROM emp WHERE id = :v_id;
- DB 성능 튜닝 핵심 2: DBMS Call 최소화
- Application Cursor Caching:
- 일반 처리: 매번 SQL 실행 시 커서 Open/Close → 매번 Parse Call 필요
- 최적화 처리: 세션 종료 시까지 커서 Open 유지 → PGA에서 커서 상태 정보 캐싱 → Parse Call 감소 - Array Processing
- 건마다 처리 → Bulk 처리, 배열 단위로 처리합니다.
- 1000건 Insert → 1회 DBMS Call
- Application Cursor Caching:
-- PL/SQL: FORALL문을 통한 Bulk 처리 예시
BEGIN
FORALL i IN 1..1000
INSERT INTO emp VALUES (employees(i));
END;
- DB 성능 튜닝 핵심 3: I/O 성능 효율화
- 논리적 I/O를 줄이는 방안은 다음과 같습니다.
- OLTP:📍index 중심
- 수직적 탐색 효율화: B-Tree 인덱스 계층을 최소화합니다.
- 수평적 스캔 선택도 향상: 인덱스 매칭도를 올립니다. (Where 조건의 컬럼을 인덱스 선두 배치, 드라이빙)
- Table Random Access 최소화: 인덱스만 읽고 테이블 액세스를 방지합니다. (Where절에 있는 조건이 모두 인덱스에 들어가도록, 커버링 인덱스)
- Batch:📍Direct Path I/O
- 👉🏻 DBMS가 데이터를 읽는 순서
- 메모리부터(Data Buffer Cache) 확인하고 → 메모리에 데이터가 없다면 디스크를 확인합니다.
- Direct Path I/O: 대량 데이터 처리 시 버퍼 캐시를 우회하고 디스크에서 직접 데이터를 읽는 방식을 말합니다.
(소량의 데이터는 메모리에서 처리, 대량의 데이터는 효율성을 위해 디스크에서 직접 읽음) - 👉🏻 HWM
- High Water Mark
- 데이터가 얼마나 기록되었는지 나타내는 지표입니다.
- Direct Path 작업 시 새로운 데이터를 추가할 때 새 공간에 데이터를 기록합니다. (기존 블록 재사용 XXX)
- 이때 트랜잭션 커밋과 무관하게 HWM이 즉시 상승하는 특징이 있습니다. - Parallel Read: SELECT /*+ PARALLEL*/ ...
- Parallel Write: UPDATE /*+ PARALLEL*/ ...
- Temporary Tablespace Read/Write: 주로 정렬 또는 Hash Join시 사용됩니다.
- Direct Path Read: 버퍼 캐시를 우회해 디스크에서 직접 읽습니다.
- Direct Path Write: CTAS, INTAS, 버퍼 캐시를 우회해 데이터 파일에 직접 기록합니다.
- Lob Type Read no cache 옵션: 캐시를 사용하지 않고 직접 디스크에서 읽어 성능을 최적화할 수 있습니다.
- 👉🏻 DBMS가 데이터를 읽는 순서
✏️ 2. Oracle 아키텍처
- ✅ 아키텍처
- Oracle 아키텍처
- 데이터베이스: 물리적인 디스크에 저장된 데이터 집합니다. (Data files, Redo Log files, Control files)
- 인스턴스: SGA 공유메모리영역 + 이를 액세스하는 프로세스 집합입니다.
- 액세스
- Single: 하나의 인스턴스 - 하나의 데이터베이스만 액세스
- RAC: 여러 인스턴스 - 하나의 데이터베이스를 액세스
- ⚠️ 하나의 인스턴스 - 여러 데이터베이스를 액세스할 수 없음
- SQL Server 아키텍처
- 하나의 인스턴스당 최고 32,767개의 데이터베이스를 정의해 사용할 수 있습니다.
- 시스템 데이터베이스: master, model, msdb, tempdb
- 사용자 데이터베이스: 데이터 파일(.mdf), 트랜잭션 로그 파일(.ldf), 보조데이터 파일(*.ndf)
- Oracle 아키텍처
- ✅ 프로세스
- SQL Server는 Worker 쓰레드
- 서버 프로세스: 전면에 나서 사용자가 던지는 각종 명령을 처리합니다.
- 백그라운드 프로세스: 뒤에서 주어진 역할을 수행합니다.
- 📍프로세스 1: 서버 프로세스
- 주요 역할
- SQL 파싱 → 최적화* → 커서 오픈해 → SQL 실행하면서
*서버프로세서가 옵티마이저를 실행 → 실행계획을 만드는 것 - Block Read → 읽은 Block 정렬하여(PGA에서 수행) → 최종 결과집합을 만들어 네트워크에 전송
- SQL 파싱 → 최적화* → 커서 오픈해 → SQL 실행하면서
- 종류
- 전용 서버 방식: Dedicated Server
- 클라이언트 세션:서버프로세스 = 1:1 매핑
- 연결 요청을 받은 리스너가 서버프로세스(Thread)를 생성 → 서버프로세스가 단 하나의 사용자 프로세스를 위한 전용(dedicated) 서비스를 제공합니다.
- 공유 서버 방식: Shared Server
- 클라이언트 세션:서버프로세스 = 1:N 매핑
- 하나의 서버 프로세스를 여러 사용자 세션이 공유하는 방식입니다.
- 전용 서버 방식: Dedicated Server
- 주요 역할
- 📍프로세스 2: 백그라운드 프로세스
| Oracle | SQL Server | 설명 |
| PMON: Process Monitor | OPS: Open Data Service | - 이상이 생긴 프로세스의 리소스를 복구 |
| SMON: System Monitor | Database cleanup /shrinking thread |
- 장애가 발생한 시스템을 재기동할 때 인스턴스 복구를 수행 - 임시 세그먼트, 익스텐트를 모니터링 |
| DBWR: Database Writer | Lazy writer thread | - 버퍼 캐시에 있는 Dirty 버퍼를 Data file로 Write |
| LGWR: Log Writer | Log writer thread | - 로그 버퍼 엔트리를 Redo Log file에 Write |
| ARCN: Archiver | - | - 꽉 찬 Redo Log가 덮어 쓰여지기 전에 Archive Log Directory로 백업 |
| CKPT: Checkpoint | Database Checkpoint thread | - 이전 Checkpoint 이후 발생한 Data Buffer의 변경 내용을 Data file로 Write 하기 위한 Base-Line 설정 - 현재 기록된 시점을 Control file 헤더에 저장 - Write Ahead Logging 방식: 데이터 변경 전에 로그부터 남기는 매커니즘 |
| RECO: Recoverer (오라클에서는 중요하지 X) |
DTC: Distributed Transaction Coordinator (중요⭐⭐⭐) | - 분산 트랜잭션 과정에 발생한 문제를 해결 |
| 구분 | Oracle | SQL Server |
| Physical | Data File | |
| Logical | Tablespace | File Group |
| Segment | Heap/Index | |
| Extent | ||
| Blocks | Pages | |
- ✅ 파일구조
- 📍파일구조 1: 데이터 파일
- (1) 블록 (SQL Server의 Page. I/O의 기본 단위)
- 대부분의 DBMS에서 I/O는 블록 단위로 이루어집니다.
- 데이터를 읽고 쓸 때 사용하는 논리적인 단위입니다.
- 블록 크기
- Oracle: 2, 4, 8, 16, 32, 64KB (다양한 블록 사용 가능)
- SQL Server: 8KB 단일
- ⭐⭐⭐ 액세스하는 블록 개수가 SQL 성능 좌우
- ⭐⭐⭐ 옵티마이저의 판단에 가장 큰 영향을 미치는 것 = 액세스해야 할 블록 개수
- (2) 익스텐트
- Tablespace로부터 공간을 할당하는 단위
- 물리적으로 연속된 Block 집합(Multi I/O시 한번에 가져올 수 있음)
- Oracle
- 크기: 다양한 크기의 익스텐트
- 오브젝트: 모든 블록을 단일 오브젝트가 사용
- SQL Server
- 크기: 항상 8개, 64KB (Page 크기가 8KB * 8개 = 64KB)
- 오브젝트: 2개 이상의 오브젝트 사용 가능
- 균일 인스텐트(Uniform): 64KB 이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용.
8개 페이지 단위로 할당된 익스텐트를 단일 오브젝트가 모두 사용 - 혼합 익스텐트(Mixed): 한 익스텐트에 할당된 8개 페이지를 → 여러 오브젝트가 나누어 사용하는 형태.
모든 테이블이 처음에는 혼합 익스텐트로 시작하지만, 64KB를 넘으면서 2번째부터 균일 익스텐트를 사용하게 됩니다.
- (3) 세그먼트 (SQL Server의 Heap구조 또는 Index구조 오브젝트)
- Tablespace로부터 Extent 단위로 공간을 할당하는 단위
- 물리적으로 연속되지 않음
- 테이블, 인덱스, UNDO처럼 저장공간을 필요로하는 데이터베이스 오브젝트
- 1:1
- 테이블:세그먼트, 인덱스:세그먼트 - 1:M
- 파티션:세그먼트 (하나의 파티션에 여러 개의 세그먼트 할당) - M:M
- 데이터파일:세그먼트 (한 세그먼트에 할당된 익스텐트가 여러 데이터파일에 흩어져서 저장 → I/O 분산효과)
- (1) 블록 (SQL Server의 Page. I/O의 기본 단위)
- 📍파일구조 2: UNDO ⭐⭐⭐
- Transaction Rollback
- 사용자가 트랜잭션을 롤백하거나 시스템 오류로 인해 트랜잭션이 비정상적으로 종료될 경우
→ UNDO를 사용해 데이터를 원래 상태로 복구합니다.
- 사용자가 트랜잭션을 롤백하거나 시스템 오류로 인해 트랜잭션이 비정상적으로 종료될 경우
- Transaction Recovery: 인스턴스 리커버리 시점의 Rollback 단계
- Roll Forward: Redo 로그를 사용 → 데이터베이스를 최신 상태로 복구
- Roll Back: Roll Foward 후 UNDO 데이터를 사용해 커밋되지 않은 트랜잭션을 롤백
- Roll Forward: Redo 로그를 사용 → 데이터베이스를 최신 상태로 복구
- Read Consistency: UNDO를 사용해 읽기 일관성 유지
- SELECT 쿼리: 쿼리가 시작된 시점의 데이터 상태를 보여줍니다.
- 10:00 쿼리 시작 → 10:10에 데이터가 변경 → 10:20에 읽어도 10:00 시점의 데이터를 반환합니다.
- Consistent Mode Read: UNDO 데이터를 활용해 쿼리시작시점의 데이터를 재구성 - UPDATE 쿼리: 현재 데이터를 읽습니다.
- 10:00 업데이트 → 10:10 커밋 후 → 10:20에 읽는다면 변경된 데이터를 반환합니다.
- Current Mode Read
- SELECT 쿼리: 쿼리가 시작된 시점의 데이터 상태를 보여줍니다.
- Transaction Rollback
- 📍파일구조 3: 임시 데이터 파일 (Temp)
- 사용 목적
- 대량의 정렬 또는 해시 작업을 수행하다가 메모리 공간이 부족해지면 중간결과집합을 저장하는 용도로 사용합니다.
- 임시 저장 후 자동 삭제합니다.
- Redo 정보 미생성, 향후 복구되지 않습니다. - Oracle
: 임시 테이블스페이스를 여러 개 생성해 두고, 사용자마다 별도의 임시 테이블스페이스를 지정해 줄 수 있습니다. - SQL Server
: 단 하나의 tempdb 데이터베이스를 사용합니다.
- 사용 목적
- 📍파일구조 4: 로그 파일 (Log file)
- 사용 목적
- DB 버퍼 캐시에 가해지는 모든 변경사항을 기록하는 파일입니다.
- 로그 기록: Append 방식, 빠른 속도
(vs. 데이터 블록 기록: Random I/O 방식, 느린 속도)
- Databse Recovery(Memory Recovery)
- Cache Recovery: 인스턴스 리커버리 시점의 Roll Forward - 주요 개념
- Fast Commit
: 트랜잭션 처리 매커니즘. 트랜잭션 커밋 시 데이터 변경사항을 Redo Log에만 기록하는 방식 (즉시 데이터 파일에 기록하지 않고 = DBWR 미작동) → 이를 통해 커밋 작업 속도 향상, 성능 개선할 수 있습니다.
- WAL(Write-Ahead Logging)
: 데이터 무결성 보장을 위한 프로토콜. 데이터 블록의 변경사항을 데이터 파일에 기록하기 전에, 반드시 해당 변경사항에 대한 Redo 정보를 먼저 Redo Log에 기록해야 합니다. (DB Buffer Cache 이전에 Redo Log 작성, DBWR 이전에 LGWR 작동) → 시스템 장애 발생 시 데이터 복구, 데이터 파일 쓰기 작업 최적화할 수 있습니다.
- Log Force at Commit
: 트랜잭션 커밋 시 모든 Redo 정보를 즉시 Redo Log 파일에 기록하는 매커니즘. → 트랜잭션 영속성 보장, 복구 용이 등 - Oracle: Redo 로그
- 트랜잭션의 데이터 유실 방지
- 마지막 체크포인트 이후 사고 발생 직전까지 수행되었던 트랜잭션을 Redo 로그를 이용하여 재현 (캐시 복구)
- 최소 2개 이상의 파일로 구성, Round-Robin 방식을 이용하여 사용 - SQL Server: 트랜잭션 로그
- 데이터 파일(데이터베이스)마다 트랜잭션 로그 파일이 하나씩 생성
- 가상로그파일이라고 불리는 더 작은 세그먼트 단위로 나뉨
- 가상로그파일 개수가 너무 많아지지 않도록 옵션 지정
(로그파일을 넉넉한 크기로 만들어 자동 증가가 발생하지 않도록 하거나, 증가 단위를 크게 지정)
- 사용 목적
- 📍파일구조 1: 데이터 파일
- ✅ 메모리 구조
- 공유여부에 따른 메모리 구분
- 1) 시스템 공유 메모리 영역
- Oracle: SGA(System Global Area)
SQL Server: Memory Pool - 여러 프로세스가 동시에 액세스할 수 있는 메모리 영역
- 모든 DBMS는 공통적으로 사용하는 캐시 영역이 있습니다. (DB Buffer Cache, Shared Pool, Log Buffer)
- 그외 Large Pool, Java Pool, 시스템 구조와 제어를 캐싱하는 영역을 포함하고 있습니다.
- 여러 프로세스가 공유되기 때문에 내부적으로 Latch, Buffer Lock, Library Cache Lock/Pin같은 액세스 직렬화 매커니즘을 사용합니다.
- Oracle: SGA(System Global Area)
- 2) 프로세스 전용 메모리 영역
- PGA(Process Global Area)
: 오라클은 프로세스 기반의 아키텍처, 서버 프로세스가 자신만의 전용 메모리 영역을 가집니다. - 데이터를 정렬, Hash 관련 작업, 세션과 커서* 정보를 저장합니다.
*커서: Session Cursor (그외 Cursor 종류: Application Cursor, Shared Cursor) - ⚠️ SQL Server는 쓰레드 기반 아키텍처로, 프로세스 전용 메모리 영역을 갖지 않습니다.
- PGA(Process Global Area)
- 1) 시스템 공유 메모리 영역
- 📍SGA 1: DB Buffer Cache
- 필요한 이유
- 데이터 파일로부터 읽어들인 데이터 블록을 담는 캐시 영역입니다.
- 사용자 프로세스는 서버 프로세스를 통해 DB Buffer Cache의 버퍼 블록을 동시에 액세스합니다.
(내부적으로 Buffer Lock을 통한 직렬화) - Direct Path Read 매커니즘*이 작동하는 경우를 제외하면, 모든 블록 읽기는 버퍼 캐시를 통해 이루어 집니다.
(*Direct Path I/O: 대량 데이터 처리 시 버퍼 캐시를 우회하고 디스크에서 직접 데이터를 읽는 방식을 말합니다.) - 디스크에서 읽을 때도 Buffer Cache에 적재한 후 읽습니다.
- 데이터 변경도 Buffer Cache에 적재된 블록을 통해 이루어 집니다.
- 변경된 블록(Dirty Buffer): 주기적으로 DBWR 프로세스에 의해 데이터 파일에 기록됩니다.
- 디스크 I/O: 물리적으로 액세스 암을 움직이면서 헤드를 통해 이루어지는 반면
메모리 I/O: 전기적 신호에 불과. 따라서 디스크 I/O와는 비교할 수 없을 정도로 빠릅니다.
- 버퍼 블록 상태
- Free Buffer
- 인스턴스 기동 후 아직 데이터가 읽히지 않은 상태
- 또는 데이터 파일과 완전히 동기화된 상태
- 언제든 새로운 데이터로 덮어쓸 수 있습니다. - Dirty Buffer
- 버퍼에 캐시된 후 변경이 발생했지만, 아직 디스크에 기록되지 않은 상태
- 데이터 파일 블록과 동기화 필요
- 재사용되기 전 반드시 디스크에 기록해야 합니다.
- 디스크에 기록되면 → Free Buffer 상태로 변경됩니다. - Pinned Buffer
- 현재 읽기 또는 쓰기 작업이 진행 중인 버퍼 블록
- 작업이 완료될 때까지 다른 프로세스가 접근할 수 없습니다.
- Free Buffer
- LRU 알고리즘
- 버퍼 캐시는 유한한 자원이므로 모든 데이터를 캐싱해 둘 수 없습니다.
따라서 사용빈도가 높은 데이터 블록을 위주로 버퍼 캐시가 구성되도록 LRU 알고리즘을 사용합니다. - 모든 버퍼 블록헤더를 LRU 체인에 연결 → 사용빈도순으로 위치를 옮기다가(=Touch Count가 높을수록 MRU) → Free Buffer가 필요해지면, 액세스 빈도가 낮은(LRU)쪽 데이터 블록부터 밀어내는 방식입니다.
- LRU: Least Recently Used
- LRU 리스트의 끝부분에는 가장 오래 사용되지 않은 블록들이 위치
- 이 블록들은 필요 시 가장 먼저 버퍼 캐시에서 제거될 후보 - MRU: Most Recently Used
- MRU 리스트의 시작 부분에는 가장 최근에 접근된 블록들이 위치
- 새로 읽힌 데이터 블록들은 대부분 MRU 끝에 위치
- 버퍼 캐시는 유한한 자원이므로 모든 데이터를 캐싱해 둘 수 없습니다.
- 필요한 이유
- 공유여부에 따른 메모리 구분
- 📍SGA 2: Shared Pool
- Oracle: Shared Pool
SQL Server: Procedure Cache - 1) 딕셔너리 캐시
- 테이블, 인덱스 같은 오브젝트와
- 테이블스페이스, 데이터파일, 세그먼트, 익스텐트, 사용자, 제약사항과 같은 메타정보를 저장합니다. - 2) 라이브러리 캐시
- SQL 문장, 실행계획, Stored Procedure 등을 저장합니다.
- 매번 실행계획을 만드는 무거운 작업을 피하기 위해 캐싱합니다. (캐싱된 SQL, 실행계획 재사용성 중요)
- Bind 변수 사용 및 기준에 맞는 SQL 작성으로 재사용성을 높여줘야 합니다.
- Oracle: Shared Pool
- 📍SGA 3: Log Buffer
- Oracle: Redo Log Buffer
SQL Server: 트랜잭션 로그, 로그 캐시 - Recoevery를 위해 사용되고, DB Buffer에 가해지는 모든 변경사항을 Log Buffer에 먼저 기록합니다.
- 인스턴스 리커버리 절차
- Roll Forward: 최종 체크포인트 발생 이후 Redo Log 데이터를 DB Buffer Cache에 적용합니다.
- Roll Back: Undo 데이터를 사용해 Commit되지 않은 변경사항을 취소합니다.
- Check Point: Cache와 데이터 파일 동기화 시점 파악을 위해, 변경된 데이터를 디스크에 기록하고 동기화 시점을 표시합니다.
- Oracle: Redo Log Buffer
- 📍PGA 1: UGA(User Global Area)
- 각 세션을 위한 독립적인 공간
- 하나의 DB Call을 넘어서 다음 Call까지 계속 참조되는 정보를 저장
- Dedicated Server: PGA에 UGA 영역을 할당
- Shared Server: SGA의 Large Pool 또는 Shared Pool에 UGA 영역을 할당
- 📍PGA 2: CGA(Call Global Area)
- Call이 진행되는 동안 필요한 데이터는 CGA에 저장합니다.
- 매번 DB Call 발생 시 공간 생성 → DB Call 종료 시 PGA에 반환
- Call이 진행되는동안 Recursive Call이 발생하면 그 안에서도 Parse, Execute, Fetch 단계별로 CGA를 할당합니다.
- DB Call: Parse Call, Execute Call, Fetch Call
- 📍PGA 3: Sort Area
- 데이터 정렬을 위해 사용되며, 부족할 때마다 Chunk 단위로 조금씩 할당됩니다.
- Oracle 9i 이전: 세션마다 sort_area_size 파라미터로 설정 가능합니다.
Oracle 9i 이상: workarea_size_policy 파라미터를 auto로 설정하면 내부적으로 알아서 sort area를 할당해 줍니다.
| 구분 | Sort Area 할당 위치 |
| DML | CGA 영역에 할당 |
| SELECT | 수행중간단계에 필요한 Sort Area는 CGA에 할당, 최종결과집합을 출력하기 직전 단계에서 필요한 Sort Area는 UGA에 할당 |
- ⚠️ SQL Server
- PGA 영역이 없습니다.
- 정렬: Memory Pool 안에 있는 버퍼 캐시에서 수행합니다.
- 세션관련정보: Memory Pool 안의 Connection Context 영역에 저장합니다.
📒 정리하면
⭐⭐⭐ Li Call IO
- Libaray 캐시 최적화: SQL 파싱 부하 감소를 위해
- A: Application Cursor Caching으로 소프트 파싱 횟수를 감소 시키고,
- B: Bind 변수를 활용해 하드 파싱 횟수를 감소 시킵니다. - DBMS Call 최소화
- A: Application Cursor Caching 커서 재사용으로 Parse Call을 감소시키고
- A: Array Processing 배열 처리(Bulk Insert)로 트랜잭션당 호출 횟수를 줄입니다. - I/O 성능 효율화
- OLTP 환경: Index 최적화 (수직적 탐색, 수평적 스캔: 드라이빙, 테이블 R.A 최소화: 커버링 인덱스)
- Batch 환경: Direct Path I/O를 적용해 버퍼 캐시 경합을 회피합니다.
(PPTDDL: Parallel Read/Write, Temporary Tablespace Read/Write, Direct Path Read/Write, Lob Type Read no cache 옵션) - Oracle 아키텍처
- SGA
: Shared Pool(Library/Data Dictionary Cache), Database Buffer Cache, Redo Log Buffer, Java/Large Pool - Background Process: PMON, SMON, DBWR, LGWR, CKPT, Others
- Database: Data files, Control files, Redo Log files
- SGA
- 파일 구조
- Data file
- 블록(I/O 기본단위) < 익스텐트(물리적으로 연속된 블록 집합) < 세그먼트(물리적으로 연속X) < 테이블 스페이스 - UNDO
- Transaction Rollback (원상태 복구)
- Transaction Recovery (Roll forward: Redo, 최신 상태로 복구/Roll back: Undo, 커밋되지 않은 트랜잭션 롤백)
- Read Consistency (SELECT: Consistent Mode Read, UPDATE: Current Mode Read) - Temp (임시 데이터 파일)
- 대량 정렬 또는 해시 작업 수행 시 메모리 공간이 부족해지면 중간결과집합을 저장하는 용도로 사용
- 임시저장 후 자동삭제
- Oracle은 여러 개, SQL Server는 단 하나 - Log file
- DB버퍼캐시에 가해지는 모든 변경사항을 기록
- Fast Commit: 데이터 변경사항을 Redo Log에만 기록
- WAL(Write-Ahead Logging): Data file에 기록하기 전에 먼저 Redo Log에 기록
- Log Force at Commit: 커밋 시 모든 Redo 정보를 즉시 Redo Log 파일에 기록
- Data file
- 메모리 구조
- SGA
- Oracle: System Global Area / SQL Server: Memory Pool
- 여러 프로세스가 동시 액세스, 공통으로 사용하는 캐시 영역
- 내부적으로 액세스 직렬화 매커니즘 사용 (Latch, Buffer Lock, Library Cache Lock/Pin) - PGA
- Oracle: Process Global Area / SQL Server: 쓰레드 기반 아키텍처로 X
- 서버 프로세스가 자신만의 전용 메모리 영역을 가짐, 데이터 정렬/Hash/세션과 세션 커서 정보 저장
- SGA
- SGA
- DB Buffer Cache
- 데이터 파일로부터 읽어들인 데이터 블록을 담음
- 모든 블록 읽기는 버퍼 캐시를 통해 이루어짐 (예외: Direct Path I/O)
- Dirty Buffer 즉 변경된 블록은 주기적으로 DBWR 프로세스에 의해 데이터파일에 기록됨
*버퍼블록상태: Free Buffer, Dirty Buffer, Pinned Buffer, LRU 알고리즘 (MRU → LRU) - Shared Pool
- Oracle: Shared Pool / SQL Server: Procedure Cache
- 딕셔너리 캐시 (오브젝트: 테이블, 인덱스 + 메타정보: 테이블스페이스, 데이터파일, 세그먼트, 익스텐트, 사용자, 제약사항)
- 라이브러리 캐시 (SQL 문장, 실행계획, Stored Procedure) - Log Buffer
- Oracle: Redo Log Buffer / SQL Server: 트랜잭션 로그, 로그 캐시
- Recovery를 위해 사용, DB버퍼에 가해지는 모든 변경사항을 Log Buffer에 먼저 기록
*인스턴스 리커버리 절차: Roll Forward(Redo) → Roll Back(Undo) → Check Point(Cache와 데이터파일 동기화)
- DB Buffer Cache
- PGA
- Oracle만 존재 / SQL Server: 쓰레드 기반 아키텍처로 PGA 영역 없음- UGA (User Global Area)
- 하나의 DB Call을 넘어서 다음 Call까지 계속 참조되는 정보를 저장
- Dedicated Server는 PGA에 / Shared Server는 SGA의 Large Pool 또는 Shared Pool에 UGA 영역을 할당 - CGA (Call Global Area)
-Call이 진행되는 동안 필요한 데이터
- 매번 DB Call 발생 시 공간 생성 → DB Call 종료 시 PGA에 반환
*DB Call 종류: Parse Call, Execute Call, Fetch Call - Sort Area
- 부족할 때마다 Chunk 단위로 조금씩 할당
- 9i 이전 sort_area_size / 9i 이상 workarea_size_policy 파라미터 auto로 sort area 자동 할당
- Sort Area 할당 위치
: SELECT문은 CGA 영역에 당,
UPDATE문은 (1) 수행중간단계에서 필요하면 CGA에, (2) 최종결과지합을 출력하기 직전에 필요하면 UGA에 할당
- UGA (User Global Area)
반응형
