[오라클 성능 고도화] 1. 아키텍처(1)
기본 아키텍처
오라클 기준으로 다음 용어은 아래와 같이 정의된다.
"데이터베이스"란?
디스크에 저장된 데이터 집합(Datafile, Redo Log File, Control File 등)을 말한다.
"인스턴스"란?
SGA 공유 메모리 영역 + 이를 액세스하는 프로세스 집합을 말한다.
여기서 프로세스 집합은 다시 "서버 프로세스"와 "백그라운드 프로세스" 집합으로 나뉜다.
"서버 프로세스"란?
사용자의 명령을 처리한다.
"백그라운드 프로세스"란?
뒤에서 묵묵히 주어진 역할을 수행한다.
사용자가 오라클에 접속해 명령어를 치면, SQL을 파싱하고 필요 시 최적화를 수행하며, 블록을 읽고 결과를 반환해준다.
위와 같은 과정은 모두 사용자의 요청에 의해 서버 프로세스가 수행하고, 수행 과정에서 백그라운드 프로세스가 처리해야 할 영역은 신호를 보내 대신 처리하도록 요청한다.
실제 클라이언트를 통해 접속하는 모습을 상상해보자.
1. 접속 요청 - 사용자는 클라이언트를 통해 SQL PLUS에서 계정과 패스워드를 입력하여 접속하려고 엔터를 쳤다.
2. 프로세스 생성 - 오라클의 LISTENER가 요청을 받아 하나의 프로세스를 생성하고(fork)
3. 메모리 할당(생성) - 별도의 PGA 메모리를 할당한다.
4. RESEND 패킷 전송 - 접속 요청이 정상적으로 처리되어 재전송되어 돌아온다.
5. 연결 - 오라클 인스턴스에 연결되어 SQL PLUS로 접속되었다.
여기서 프로세스를 생성하고 메모리를 할당하는 작업은 비용이 매우 커 매번 연결요청을 처리하지 않도록 "커넥션 풀"을 사용한다.
한번 접속되면 해제하지 않고 Pooling해두는 것을 말한다.
앞으로 기술하게 될 튜닝의 핵심은 이 재사용성에 있다.
DB 버퍼 캐시
SGA는 목적에 따라 여러 영역으로 분리되어 관리되는데, 그중 DB buffer cache는 데이터파일에 입력한 데이터를 저장하고, 다시 읽는 과정에 사용되는 영역이다.
(1) 블록 단위 I/O
오라클은 블록 단위로 데이터를 처리하는데, 데이터파일에 액세스 뿐만 아니라 읽어 캐시에 올릴 때도 블록 단위로 데이터를 읽어 온다.
버퍼 캐시에 적재할 때 인덱스를 경유할 시 한 블록씩(single block read) 읽어 오지만, Full scan 시 여러 개 블록(multiblock read)을 읽어 들인다. 버퍼 캐시의 변경 블록을 주기적으로 기록해주는 백그라운드 프로세스인 DBWR 프로세스 또한 성능 향상을 위해 multiblock read를 한다.
블록 단위 I/O가 의미하는 것은 한 줄의 레코드만 필요하다고 하더라도 한 블록을 모두 읽어야 한다는 것이다. 가장 중요한 성능지표 중 하나는 액세스해야 할 블록 개수를 줄이는 것이다. 최적화를 담당하는 오라클의 옵티마이저도 인덱스를 통한 액세스를 할지 Full scan을 할지 결정하는 지표는 이 블록 개수이다.
(2) 버퍼 캐시 구조
SGA는 수많은 자료구조가 사용되고 있는데, DB 버퍼 캐시는 해시 테이블 구조로 관리된다.
해시 테이블은 해싱 알고리즘으로 데이터를 처리하는 자료 구조이다.
1. Hasing - 사용자의 명령어가 해싱 알고리즘에 의해 데이터 블록 주소(DBA, Data Block Address)가 고유값(hash value)으로 변환되면
2. Searching - 같은 해시 값을 가진 블록 주소들이 담겨있는 해시 버킷을 뒤진다.
(해시 버킷에는 버퍼 블록의 실제 데이터를 포인터르 가르키고 있는 헤더 부분이 연결 리스트(Linked List) 구조로 연결되어 있고 이를 해시 체인에 연결 되어 있다고 표현한다.)
3. Parsing - 해당하는 버킷에서 체인을 따라 스캔하다 원하는 데이터를 발견하면, 버퍼에 있는 결과를 재사용(Soft pashing)하고 없다면 다른 유저의 재사용을 위해 디스크에서 읽어 체인에 연결한 뒤 읽는다.(Hard pashing)
(3) 캐시 버퍼 체인
오라클의 SGA는 여러 사용자의 동시성을 제공하는데 큰 이점이 있지만, 같은 데이터에 액세스하려 한다면 반드시 요청 순서를 지켜야만 하기 때문에 "액세스가 직렬화(seriallzation) 되야한다."
이를 위해 SGA에 공유된 자료구조를 보호하는 Lock 매커니즘을 "래치(Latch)"라고 부른다. 여러 자료구조가 존재하는 만큼 여러 래치가 존재한다.
래치를 획득한 프로세스만 해당 자료구조로 진입이 가능하다. 버퍼 캐시 같은 경우 래치를 획득한 프로세스만이 해시 체인을 탐색할 수 있다는 것이다. 버퍼 캐시에서의 래치를 "cache bufffers chains 래치"라고 한다.
해당 래치는 해시 체인 스캔, 블록 추가, 제거할 경우 래치 획득을 요구하며, 한개의 래치가 여러 개의 해시 체인을 동시에 관리한다. (1:N개를 관리하며 이는 버퍼 캐시 크기 및 버전에 따라 상이함)
하나의 해시 체인에는 여러 개의 버퍼가 연결될 수 있지만, 튜닝 시 목표한 해시 체인을 찾고 추가적으로 스캔하지 않고 끝낼 수 있도록 하나의 체인에는 하나의 버퍼만 달리도록 하는 것이 목표이다.
버킷과 체인은 1:1의 관계이므로 해당 목표를 위해서는 충분히 많은 해시 버킷이 필요하다. (통상 블록 대비 2-3배 이상 버킷이 존재)
-- 히든 파라미터 --
* 해시 버킷 개수 : _db_block_hash_buckets
* 래치 개수 : _db_block_hash_latches
* 블록 버퍼 개수 : _db_block_buffers
-- 조회 쿼리 --
col KSPPSTVL for a10
col KSPPINM for a20
SELECT KSPPINM, KSPPSTVL
FROM X$KSPPI X, X$KSPPCV Y
WHERE X.INDX = Y.INDX
AND X.KSPPINM LIKE '%히든 파라미터 명%'
AND SUBSTR(X.KSPPINM, 1, 1) = '_'
/
(4) 캐시 버퍼 LRU 체인
버퍼 헤더는 해시 체인에 의해서 연결되어 관리되지만(캐시 버퍼 체인) LRU 체인에 의해서도 연결돼 있다. LRU 체인은 한정된 DB 버퍼 캐시에 모든 데이터를 캐싱할 수는 없기 때문에 사용빈도가 높은 블록 위주로 구성될 수 있도록하는 LRU(least recently used, 액세스 빈도가 낮은 데이터 블록을 덮어씀) 알고리즘으로 관리되는 자료구조다. LRU 리스트를 보호하기 위한 래치를 "cache bufffers lru chains 래치"라고 한다.
LRU 리스트는 두 개의 리스트로 구성되어 있다.
1. Dirty 리스트(LRUW(write) 리스트) - 변경 됐지만 디스크에 기록되지 않은 블록(Dirty 블록) 관리
2. LRU 리스트 - 아직 Dirty 리스트로 옮겨지지 않은 나머지 버퍼 블록 관리
모든 버퍼 블록은 위 두 리스트 중 하나에 소속되어 있다.
모든 캐시 버퍼는 아래 세가지 상태 중 하나이다.
1. Free(Clean) 버퍼 상태 - 데이터가 읽히지 않아 비어 있음
2. Dirty 버퍼 상태 - 캐시된 이후 변경이 발생했으나 디스크에 쓰지이 않아 동기화가 필요한 버퍼 블록. 기록 후 Free 상태가 된다.
3. Pinned 버퍼 상태 - 읽기 또는 쓰기 작업을 위해 액세스 중인 버퍼 블록
버퍼 LOCK
(1) 버퍼 Lock
버퍼 캐시를 탐색하기 위해 cache buffers chains 래치를 획득해 해시 체인을 탐색했다. 해당 래치는 여러 개의 버킷을 동시에 담당하므로 빨리 해제해야 경합을 줄일 수가 있다. 이제 해당 버퍼 블록에 작업하려 하는데, 이미 선행 프로세스가 해당 블록을 사용하고 있다면?
이럴 경우 데이터 정합성(Integrity)을 위해 동시에 같은 버퍼에 액세스 할 수 없도록 버퍼 Lock이 존재한다. 직렬화 액세스를 위해 캐시된 버퍼 블록을 읽거나 변경하려면 먼저 버퍼 헤더로부터 버퍼 Lock을 획득(이를 버퍼 Pin이라고도 하며, Pinned buffer를 의미함) 하도록 하는 것이다. 버퍼 Lock을 획득 해야만 비로소 래치를 해제한다.
읽기(Select)만 할때는 여러 프로세스가 접근 가능한 "Share 모드"
(select 라도 블록 클린아웃이 필요할 경우 Exclusive Lock 설정)
변경 시에는 하나의 프로세스만 얻을 수 있는 "Exclusive 모드" 버퍼 Lock이 설정된다.(이하 EX락)
해시 체인 래치를 획득해 버퍼를 찾아도 EX락이 선행 프로세스에 의해 점유되어 있어 버퍼Lock을 획득 하지 못한다고 해서 해당 작업이 끝날 때 까지 래치를 들고 있는다면, 여러 버킷을 관리하는 래치의 경합이 늘어날 것이다.
이럴 경우 버퍼 헤더에 있는 버퍼 Lock 대기자 목록(Waiter List)에 등록하여 일단 래치를 해제한다. 이 버퍼 Lock 대기자 목록에 등록되어 있는 상태가 "buffer busy waits 대기 이벤트" 이다.
버퍼 Lock을 획득 후 읽기/쓰기 작업을 완료하기 위해 버퍼 Lock을 해제하는데도 충돌을 방지하기 위해 해당 버퍼가 속한 체인 래치를 다시 한번 획득한다. 획득 후 버퍼 Lock 해제, 래치 해제를 거쳐 버퍼 블록 읽기가 완료된다.
즉, 하나의 버퍼 블록을 읽기 위해서는
래치 획득(1) → 해시 버킷, 체인 탐색 →
1. 버퍼 Lock 획득 성공 → 래치 해제 → 블록 읽기 → [래치 획득(2) → 버퍼 Lock 해제 → 래치 해제] → 작업 완료
or
2. 버퍼 Lock 획득 실패 → 버퍼 Lock 대기자 목록 등록(대기 이벤트 발생) → 래치 해제 → 버퍼 Lock 획득 → 블록 읽기 → [래치 획득(2) → 버퍼 Lock 해제 → 래치 해제] → 작업 완료
위와 같은 과정이 수행되는 것이다.
한 번의 버퍼 블록 Read에 버퍼 Lock 을 Pin하기 위해 두 번의 래치 획득을 거치지만 실제 몇 오퍼레이션은 래치를 쥔 채 읽어 한 번만 일어 난다.
(2) 버퍼 핸들
버퍼 헤더에 Pin을 설정하는데에 사용하는 오브젝트를 말한다. 버퍼 핸들을 얻어 버퍼 헤더에 있는 소유자 목록(holder List)에 연결해 Pin을 설정하는데, 버퍼 핸들도 공유 리소스 이므로 "cache buffer handles 래치"가 존재한다. 해당하는 버퍼를 많은 프로세스가 Pin 하려고 하면 버퍼 핸들을 차지하려는 경합이 많이 생겨날 것이다.
오라클은 각 프로세스마다 _db_handles_cached에 지정된 개수(기본 5개) 만큼 버퍼 핸들을 미리 할당해 그 이상의 버퍼 핸들을 요구할 때 상기 래치를 획득하여 추가 버퍼 핸들을 할당 받는다.
-- 히든 파라미터 --
* 시스템 전체 사용 가능 버퍼 핸들 개수 : _db_handles (processes * _db_handles_cached)
(3) 버퍼 Lock의 필요성
데이터를 변경할 때 어차피 DML Lock이 걸려 보호되는데 어째서 블록에 또 버퍼Lock이 설정되는걸까?
단 한 줄의 레코드 변경에도 블록 단위 I/O를 수행하기 때문이다. 동시에 블록에 접근하게 되면 다른 프로세스가 엉뚱한 결과를 가져오게 될 수 있다. Pinned 버퍼는 명령어를 통해 캐시를 비우더라도(alter system flush buffer_cache) 밀려나지 않는다.
(4) 버퍼 Pinning
버퍼 Lock에서 언급했다시피 래치 획득이 선행되어야 하는데, 같은 블록을 반복적으로 읽을 경우 래치를 반복 획득, 해제하지 않도록 데이터베이스 Call이 유지되는 동안 Pin을 유지하는 기능을 말한다. 오퍼레이션이 자주 사용하는 블록에 대하여 Logical Reads를 획기적으로 줄일 수 있다.
-- 블록 액세스 방식에 따른 지표 확인 --
v$sysstat, v$sesstat, v$mystat
-- 컬럼 --
session logical reads : 래치 획득으로 블록 액세스 시 Count
buffer is pinned count : 버퍼 Pinning을 통해 곧바로 액세스 시 Count
하나의 Call (Parse Call, Execute Call, Fetch Call) 내에서만 유효하며 결과 반환 후 Pin은 해야된다.
통상 인덱스 스캔으로 테이블에 액세스 할때 Pinning이 적용되는데, 인덱스의 리프 블록을 액세스한 뒤 테이블 블록을 교차 방문하기 때문에 테이블 블록에 대한 I/O만 증가하는 것을 볼 수 있다. 인덱스 클러스터링 팩터 (인덱스 레코드가 가리키는 테이블 rowid 정렬 순서가 인덱스 키 값 정렬 순서와 거의 일치하는 경우)가 좋을 경우 더 자주 발생한다.
오라클은 버전이 올라갈 수록 Pinning이 적용되는 지점을 늘리고 있는데, 그만큼 버퍼 Pinning을 통한 블록 I/O 감소효과가 튜닝에서 중요한 요소이기 때문이다. 인덱스를 경유하더라도 성능이 안나올 경우, Pinning 효과를 노려 액세스 빈도가 높은 인덱스 키 순서대로 테이블 레코드를 재정렬하는 방안도 있다.
참조 :