DBMS 내부 아키텍처와 SQL 옵티마이저의 원리를 이해하지 않고서는 고성능의 DB 애플리케이션을 구축하기 어렵다.
01 기본 아키텍처
SGA 는 많은 프로세스가 동시에 데이터를 액세스 하기 때문에 사용자 데이터를 보호하는 Lock 은 물론
공유 메모리 영역인 SGA 상에 위치한 데이터 구조에 대한 액세스를 직렬화 하기 위한 LOCK 매커니즘 (Latch) 도 필요해진다.
오라클 접속 과정
1. 연결 요청 사용자 → LISTENER
2. 프로세스 생성 연결 요청 상속 LISTENER → SERVER
3. 메모리 생성 SERVER → PGA
4. RESEND 패킷 전송 SERVER → 사용자
5. 연결 사용자 → SERVER
리스너에 연결 요청을 하는 순간 하나의 프로세스를 띄우고 PGA 메모리를 할당한다.
재사용성은 데이터베이스 성능 튜닝의 핵심 원리다.
02 DB 버퍼캐시
(1) 블록 단위 I/O
데이터파일에서 버퍼 캐시로 블록을 적재할 때
인덱스를 경유한 테이블 액세스 시 한번에 한블록씩
Full Scan 시 성능 향상을 위해 한번에 여러 블록 읽어들인다.
블록 단위로 읽는다는 의미는, 하나의 레코드에서 하나의 컬럼만을 읽고자 하더라도
레코드가 속한 블록 전체를 읽게 됨을 의미한다.
SQL성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수
옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록 개수
옵티마이져의 중요한 판단기준은 읽어야 할 레코드 수가 아니라 블록 개수다.
(2) 버퍼캐시구조
SGA 내에 많이 사용하는 자료구조는 해시 테이블
해싱 알고리즘, 해시 버킷
DB 버퍼 캐시 내에서 데이터 블록을 해싱하기 위해 사용되는 키 값은 데이터 블록 주소(DBA)
해시 함수에 데이터 블록 주소를 입력해 리턴받은 해시 값이 같은 블록들을 같은 해시 버킷에
연결 리스트 구조로 연결하는데 이를 해시 체인이라고 한다.
버퍼 헤더만 해시 체인에 연결되고, 버퍼 헤더에 있는 포인터를 이용해 버퍼 블록을 찾아가는 구조
Shared pool hash list
DB Buffer cache hash list
(3) 캐시 버퍼 체인
각 해시 체인은 래치에 의해 보호된다.
같은 리소스에 대한 액세스를 반드시 직렬화 해야 하고 이를 위해 구현된 Lock 매커니즘을 Latch 라고 부른다.
cache buffers chains latch: 두 개 이상의 프로세스가 같은 해시 체인으로 진입해 새로운 블록을 연결하고 해체하는 작업을 방지하기 위해 사용
하나의 cache buffers chains latch 가 여러 개 해시 체인을 동시에 관리한다.
래치는 데이터 자체를 보호하는게 아니라 SGA 에 공유되어 있는 자료구조를 보호하는 것이며,
그 중 cache buffers chains 래치는 버퍼 캐시에 연결된 체인 구조를 보호한다.
즉, 해시 체인을 스캔하거나 거기에 블록을 추가, 제거할 때 래치가 요구된다.
(4) 캐시 버퍼 LRU 체인
액세스 빈도가 낮은 데이터 블록들을 우선하여 밀어냄으로 자주 액세스 되는 블록들이 캐시에 더 오래 남아 있도록 관리하는 것
Dirty 리스트: 캐시 내에서 변경됐지만, 아직 디스크에 기록되지 않은 Dirty 버퍼 블록들을 관리하며, 'LRUW 리스트' 라고도 한다.
LRU 리스트: 아직 Dirty 리스트로 옮겨지지 않은 나머지 버퍼 블록들을 관리한다.
cache buffers lru chain 래치: LRU 리스트를 보호하기 위해 사용하는 래치
Free 버퍼: 데이터가 비어 있는 상태이거나 데이터 파일과 동기화돼 있는 상태
Dirty 버퍼: 캐시된 이후 변경이 발생했지만, 데이터 파일 블록과 동기화가 필요한 버퍼 블록
Pinned 버퍼: 읽기 쓰기 작업을 위해 현재 액세스 되고 있는 버퍼 블록
03 버퍼 Lock
(1) 버퍼 Lock?
버퍼 Pin, Pinned 버퍼라고도 부른다
데이터 정합성
캐시된 버퍼 블록을 읽거나 변경하려는 프로세스는 먼저 버퍼헤더로부터 버퍼 Lock 을 획득한다.
버퍼 Lock 을 획득했다면 래치를 곧바로 해제한다.
버퍼 내용을 읽기만: Share 모드, 변경할 때는: Exclusive 모드
해시 체인 래치를 획득하고 목적한 버퍼를 찾았는데 다른 프로세스가 버퍼 Lock 을 Exclusive 모드로 점유한 채 내용을 갱신 중이라면
버퍼 헤더에 있는 버퍼 Lock 대기자 목록에 자신을 등록하고 래치는 해제한다. (buffer busy waits 대기 이벤트)
(2) 버퍼핸들
버퍼 Lock, 버퍼 Pin, Pinned 버퍼
버퍼 헤더에 Pin 을 설정하려고 사용하는 오브젝트
cache buffer handles 래치: 버퍼 핸들을 얻기 위해 필요한 래치
_db_handles_cached 개수만큼 버퍼 핸들을 할당해 줌, 기본값은 5개
(3) 버퍼 Lock 의 필요성
오라클이 하나의 레코드를 갱신하더라도 블록 단위로 I/O 를 수행하기 때문
(4) 버퍼 Pinning
버퍼를 읽고 나서 버퍼 Pin 을 즉각 해제하지 않고 데이터베이스 Call 이 진행되는 동안 유지하는 기능
같은 블록을 반복적으로 읽을 때 버퍼 Pinning을 통해 래치 획득 과정을 생략한다면 논리적인 블록읽기 횟수를 획기적으로 줄일 수 있다.
클러스터링 팩터 - 인덱스 레코드가 가리키는 테이블 rowid 정렬 순서가 인덱스 키 값 정렬 순서와 거의 일치한다면
04 Redo
Online Redo Log: Redo log 버퍼에 버퍼링된 로그 엔트리를 기록하는 파일로서, 최소 2개 이상의 파일로 구성
로그 스위칭, 라운드 로빈 방식 사용
Archive Redo Log: Online Redo log 가 재사용되기 전에 다른 위치로 백업해둔 파일을 말한다.
* Redo Log 사용 목적
1. Database Recovery (물리적 디스크 깨짐, Media Recovery)
2. Cache Recovery (Instance Recovery 시 Roll Forward 단계)
3. Fast commit
Delayed 블록 클린아웃: 커밋 시점에는 Undo 세그먼트 헤더의 트랜잭션 테이블에만 커밋 정보를 기록하고, 블록 클린아웃은 나중에 수행하도록 하는 것
* LGWR 이 Redo 로그에 기록하는 시점
1. 3초마다 DBWR 프로세스로부터 신호를 받을 때
2. 로그 버퍼의 1/3 이 차거나 기록된 Redo 레코드량이 1MB를 넘을 때
3. 사용자가 커밋 또는 롤백 명령을 날릴 때
Fast Commit 매커니즘의 핵심은 3번, 트랜잭션이 영속성을 보장받으려면 최소한 커밋 시점에는
Redo 정보가 메모리가 아닌 디스크 상에 안전하게 저장되었음이 확인되어야 한다.
Write Ahead Logging: 버퍼 캐시에 있는 블록 버퍼를 갱신하기 전에 먼저 Redo 엔트리를 로그 버퍼에 기록해야 하며,
DBWR 가 버퍼 캐시로부터 Dirty 블록들을 디스크에 기록하기 전에 LGWR 가 해당 Redo 엔트리를 모두 Redo 로그 파일에 기록해음이 보장되어야 함
05 Undo
AUM (Automatic Undo Management): Undo 세그먼트마다 하나의 트랜잭션이 할당되는 것을 목표로 세그먼트 개수를 오라클이 자동 관리
* Undo 세그먼트에 저장된 정보의 사용목적
1. Transaction Rollback
2. Transaction Recovery
3. Read Consistency (튜닝 원리의 중요한 관심 사항)
(1) Undo 세그먼트 트랜잭션 테이블 슬롯
* Undo 세그먼트 헤더에 트랜잭션 테이블 슬롯이 위치하는데, 각 슬롯에 기록되는 사항
1. 트랜잭션 ID
2. 트랜잭션 상태 정보
3. 커밋 SCN
4. Last UBA
5. 기타
undo segment tx slot: 트랜잭션 슬롯을 곧바로 얻지 못해 이용 가능한 슬롯이 생기기를 기다릴 때 발생하는 대기 이벤트
* DML 오퍼레이션별로 Undo 레코드에 기록되는 내용
Insert: 추가된 레코드의 rowid
Update: 변경되는 컬럼에 대한 before image
Delete: 지워지는 로우의 모든 컬럼에 대한 before image
undo_retention: 트랜잭션이 완료되었어도 지정한 시간 동안은 "가급적" Undo 데이터를 재사용하지 말라고 오라클에 힌트를 주는 것
alter tablespace undotbs1 retention guarantee ;
(2) 블록 헤더 ITL 슬롯
* ITL (Interested Transaction List) 슬롯에 기록되는 내용
1. ITL 슬롯번호
2. 트랜잭션 ID
3. UBA (Undo Block Address)
4. 커밋 Flag
5. Locking 정보
6. 커밋 SCN (트랜잭션이 커밋된 경우)
(3) Lock Byte
레코드가 저장되는 로우마다 그 헤더에 Lock Byte를 할당해 해당 로우를 갱신 중인 트랜잭션의 ITL 슬롯 번호를 기록해둔다. 로우단위 Lock
06 문장수준 읽기 일관성
(1) 문장수준 읽기 일관성이란?
단일 SQL문이 수행되는 도중에 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 일관성 있는 결과집합을 리턴하는 것
(2) Consistent 모드 블록 읽기
Current 블록은 디스크로부터 읽혀진 후 사용자의 갱신사항이 반영된 최종 상태의 원본 블록을 말하며,
CR 블록은 Current 블록에 대한 복사본이다.
CR 블록은 여러 버전이 존재할 수 있지만 Current 블록은 한개 뿐이다.
Consistent 모드 읽기: 쿼리가 시작된 시점을 기준으로 데이터를 읽기
Current 모드 읽기: 데이터를 찾아간 바로 그 시점의 최종 값을 읽어야 할 때
SCN (System Change Number): 블록이 마지막으로 변경된 시점 정보
(3) Consistent 모드 블록 읽기의 세부 원리
오라클에서 수행되는 모든 쿼리는 Global 변수인 SCN(System Commit Number) 값을 먼저 확인하고 나서 읽기 작업을 시작하는데,
이를 쿼리SCN, 스냅샷SCN 이라고 한다.
* 쿼리SCN을 가지고 Consistent 모드로 읽을 때, 읽는 블록 상태에 따라 어떻게 일관성을 유지하면서 데이터를 읽는지
Current 블록SCN <= 쿼리SCN 이고, committed 상태 - CR블록을 생성하지 않고 Current 블록을 그대로 읽음
Current 블록SCN > 쿼리SCN 이고, committed 상태 -
Current 블록이 Active 상태, 즉 갱신이 진행중인 것으로 표시 되어 있을 때 -
IMU(In-Memory Undo)
DBA 당 CR 개수 제한
하나의 데이터 블록마다 6개까지만 CR Copy를 허용하며, 이를 제어하는 파라미터가 _db_block_max_cr_dba 다.
07 Consistent vs. Current 모드 읽기
(1) Consistent 모드 읽기와 Current 모드 읽기의 차이점
Consistent 모드 읽기는 SCN 확인 과정을 거치며 쿼리가 시작된 시점을 기준으로 일관성 있는 상태로 블록을 액세스하는 것
- SQL Trace 의 query, AutoTrace 의 consistent gets
- select 문에서 읽은 블록 대부분, CR블록을 생성하려고 Undo세그먼트로부터 읽어들이는 블록수까지 더함
Current 모드 읽기는 SQL문이 시작된 시점이 아니라 데이터를 찾아간 바로 그 시점의 최종 값을 읽으려고 블록을 액세스하는 것
- SQL Trace 의 current, AutoTrace 의 db block gets
- DML문 수행할 때, 인덱스가 많을수록 더 많이 나타남
(2) Consistent 모드로 갱신할 때 생기는 현상
Lost Update 문제를 회피하려면 갱신 작업만큼은 Current 모드를 사용해야 한다.
(3) Current 모드로 갱신할 때 생기는 현상
(4) Consistent 모드로 읽고, Current 모드로 갱신할 때 생기는 현상
(5) Consistent 모드로 갱신대상을 식별하고, Current 모드로 갱신
1. select 는 Consistent 모드로 읽는다.
2. insert, update, delete, merge 는 Current 모드로 읽고 쓴다. 다만 갱신할 대상 레코드를 식별하는 작업만큼은 Consistent 모드로 이루어진다.
(6) 오라클에서 일관성 없게 값을 갱신하는 사례
08 블록 클린 아웃
트랜잭션에 의해 설정된 로우 Lock 을 해제하고 블록 헤더에 커밋 정보를 기록하는 오퍼레이션이다.
블록을 클린아웃 하는 시점은 해당 블록이 처음 액세스 되는 시점
(1) Delayed 블록 클린 아웃
트랜잭션이 갱신한 블록 개수가 총 버퍼 캐시 블록 개수의 1/10을 초과할 때 사용하는 방식
1. ITL 슬롯에 커밋 정보 저장
2. 레코드에 기록된 Lock Byte 해제
3. Online Redo 에 Logging
(2) 커밋 클린아웃 (= Fast 블록 클린아웃)
디스크를 거쳐 인스턴스간에 블록을 주고받는 과정을 '핑'
블록 클린아웃 정보를 나중에 로깅한다는 의미에서 Delayed 로깅 블록 클린 아웃
(3) ITL 과 블록 클린아웃
09 Snapshot too old (ora-01555)
발생원인
1. 데이터를 읽어 내려가다가 쿼리 SCN 이후에 변경된 블록을 만나
과거시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데,
Undo 블록이 다른 트랜잭션에 의해 이미 재사용돼
필요한 Undo 정보를 얻을 수 없는 경우,
- 프로그램 코딩 패턴에 문제가 없다면 Undo 세그먼트가 너무 작다는 신호
2.커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없는 경우
(1) Undo 실패
(2) 블록 클린아웃 실패
(3) Snapshot too old 회피 방법
1. 불필요한 커밋 자주 수행하지 않는다.
2. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다.
3. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 조정한다.
4. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다.
5. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 형태의 조인문 또는
인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고,
이를 회피할 수 있는 방법을 찾는다.
6. 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트연산이 발생하도록 한다.
7. 만약 delayed 블록 클린아웃에 의해 Snapshot too old 가 발생하는 것으로 의심되면
대량 업데이트 후에 곧바로 해당 테이블에 대해 Full Scan 하도록 쿼리를 날리는 것도
하나의 해결방법이 될 수 있다.
10 대기 이벤트
(1) 대기이벤트란?
다른 프로세스가 일을 마칠 때까지 기다리는 것
오라클 프로세스가 일을 계속 진행할 수 있는 조건이 충족될 때까지 수면 상태에 빠지는 것
(2) 대기 이벤트는 언제 발생할까?
1. 자신이 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용 중일 때
- buffer busy waits, latch free, enqueue 관련 대기 이벤트 등
2. 다른 프로세스에 의해 선행작업이 완료되기를 기다릴 때
- write complete waits, checkpoint completed, log file sync, log file switch 이벤트 등
3. 할 일이 없을 때
- SQL*NET message from client, PX Deq: Execution Msg 등
(3) 대기 이벤트는 언제 사라질까?
1. 대기 상태에 빠진 프로세스가 기다리던 리소스가 사용 가능해 지거나
2. 작업을 계속 진행하기 위한 선행작업이 완료되거나
3. 해야할 일이 생겼을 때
(4) 래치와 대기 이벤트 개념 명확화
래치를 얻는 과정 자체가 경합을 의미하지는 않는다.
공유된 자원을 읽으려면 래치를 얻는 것이 당연한 일이므로 v$latch 뷰에서 gets 횟수가 증가한다고 해서 문제될 것은 없다.
그저 공유자원에 대한 접근 요청이 많았던 것으로 이해하면 된다.
다만, 그 과정에서 다른 프로세스와 경합이 발생하는지 관심 있게 살펴봐야 하며,
만약 그렇다면 시스템의 동시성이 저하되므로 문제다.
11 Shared Pool
(1) 딕셔너리 캐시
오라클 딕셔너리 정보를 저장해 두는 캐시영역으로서 Row 단위로 읽고 쓰기 때문에 '로우 캐시' 라고도 불린다.
(2) 라이브러리 캐시
사용자가 던진 SQL과 그 실행계획을 저장해 두는 캐시 영역