Latch: cache buffers chains
[편집] Basic Info
버퍼 캐시를 사용하기 위해 해시 체인을 탐색하거나 변경하려는 프로세스는 반드시 해당 체인을 관리하는 cache buffers chains 래치를 획득해야 한다. cache buffers chains 래치를 획득하는 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기하게 된다.
오라클 9i 이후부터는 읽기 전용의 목적으로 체인을 탐색하는 경우에는 cache buffers chains 래치를 Shared 모드로 공유할 수 있어 경합을 줄이는데 도움이 된다. Shared 모드의 cache buffers chains 래치에 대해 한가지 주의할 점이 있다. 만일 cache buffers chains 래치를 공유할 수 있다면 이론적으로는 동시 Select에 의한 cache buffers chains 래치 경합은 전혀 발생하지 않아야 한다. 하지만, 실제로 테스트를 해보면 동시 Select인 경우에도 래치 경합은 여전히 발생한다. 그 이유는 Buffer Lock과 관련이 있다. 읽기작업을 위해서 Shared 모드로 래치를 획득한 경우, 실제 버퍼를 읽는 과정에서 Buffer Lock을 Shared 모드로 획득해야 하는데 이 과정에서 버퍼 헤더의 정보를 일부 변경해야 한다. 따라서 Buffer Lock을 획득하는 동안에는 래치를 Exclusive 모드로 변경해야 하고Buffer Lock을 해제하는 동안에도 래치를 Exclusive하게 획득해야 한다. 이 과정에서 경합이 발생하고 이로 인해 latch: cache buffers chains 이벤트를 대기하게 된다.
cache buffers chains 래치 경합이 발생하는 대표적인 경우는 다음과 같다.
[편집] 비효율적인 SQL
비효율적인 SQL문장이 cache buffers chains 래치 경합의 가장 중요한 원인이다. 동시에 여러 프로세스가 넓은 범위의 인덱스나 넓은 범위의 테이블에 대해 스캔을 수행할 경우 cache buffers chains 래치 경합이 광범위하게 발생할 수 있다.
cache buffers chains 래치 경합이 발생한 경우 경합의 원인을 정확하게 밝히는 것이 중요하다. 래치 경합이 핫블록에 의해 발생하는지 비효율적인 SQL문장에 의해 발생하는지를 판단하는 가장 중요한 근거는 SQL문장 자체이다. SQL문장 자체가 비효율적으로 작성되어 있는 것으로 판단할 수 있는 근거가 확실하다면 SQL문장을 튜닝함으로써 문제를 해결할 수 있다.
만일, SQL문장에 대한 정보가 없다면 간접적으로 핫블록에 의한 문제인지 비효율적인 SQL문장에 의한 문제인지 판단할 수 있는 방법이 있다. V$LATCH_CHILDREN 뷰에서 자식 cache buffers chains 래치에 해당하는 CHILD# 과 GETS, SLEEPS 값을 비교하여 특정 자식 래치에 사용하는 회수와 경합이 집중되는지 판단하는 것이다. 다음 명령문을 이용해서 SLEEPS 회수가 높은 자식 래치를 얻는다.
SQL>
select * from
(select child#, gets, sleeps from v$latch_children
where name = 'cache buffers chains'
order by sleeps desc
) where rownum <= 20
만일 특정 자식 래치의 GETS, SLEEPS 값이 다른 자식 래치에 비해서 비정상적으로 높다면 해당 래치가 관장하는 체인에 핫블록이 있는 것으로 추측할 수 있다. 위의 테스트를 수행한 후의 결과는 다음과 같은데 특정 래치에 대한 편중 현상이 보이지 않으므로 핫블록에 의한 문제는 없다고 판단할 수 있다.
CHILD# GETS SLEEPS
---------- ---------- ----------
329 81160 78
940 79773 74
232 62792 69
791 99123 68
905 99185 68
408 80687 65
259 101793 62
611 82187 62
466 99870 60
839 79744 60
...
핫블록 여부를 판단하는 또 다른 방법은 V$SESSION_WAIT 뷰로부터 래치의 주소를 얻어서 비교하는 것이다. cache buffers chains 래치의 경우 V$SESSION_WAIT.P1RAW가 자식 래치의 주소에 해당한다. 만일 V$SESSION_WAIT 뷰로부터 얻은 래치의 주소가 과다하게 중복해서 나타난다면 해당 래치에 대한 편중이 생긴다는 의미이며, 이 경우 핫블록에 의한 경합으로 해석할 수 있다.
[편집] 핫블록(Hot Block)
SQL 문장이 적절히 튜닝되었음에도 불구하고 cache buffers chains 래치의 경합이 해결이 되지 않는 경우가 있다. SQL 문의 작동방식이 소수의 특정 블록을 계속해서 스캔하는 형태로 작성되었다면, 여러 세션이 동시에 이 SQL 문을 수행하는 경우 핫블록에 의한 cache buffers chains 래치 경합이 발생한다.
V$LATCH_CHILDREN 뷰를 통해 특정 자식 래치 사용이 편중되어 있는지 확인해 보면 핫블록에 의한 래치 경합인지를 간접적으로 판단할 수 있다. 또는, V$SESSION_WAIT 뷰의 P1RAW 컬럼값을 캡쳐해서 반복적으로 관찰되는 값을 래치 주소로 이용해도 된다.
SQL>
select * from
(select addr, child#, gets, sleeps from v$latch_children
where name = 'cache buffers chains'
order by sleeps desc
) where rownum <= 20
;
ADDR CHILD# GETS SLEEPS
---------------- ---------- ---------- ----------
C0000000CDFF24F0 569 10500275 11298 <-- 래치 사용 집중
C0000000CE3ADDF0 827 5250508 8085
C0000000CDF18A98 178 5250192 4781
C0000000CDEDB6E8 68 3786 17
C0000000CE3CBEE0 881 2121 8
C0000000CE359430 675 1768 1
C0000000CDEB6230 1 235 0
C0000000CDEB6B18 2 171 0
C0000000CDEB7400 3 390 0
C0000000CDEB7CE8 4 192 0
C0000000CDEB85D0 5 151 0
...
위의 결과에서 보면 child# 569, 827, 178 세 개의 자식 래치가 집중적으로 사용되고 있으며 이에 의해 래치 경합이 발생한 것을 확인할 수 있다. X$BH 뷰를 이용하면 정확하게 어떤 블록들이 핫블록인지 확인할 수 있다.
SQL> select hladdr, obj,
(select object_name from dba_objects where
(data_object_id is null and object_id = x.obj) or
data_object_id = x.obj and rownum = 1) as object_name,
dbarfil, dbablk, tch from x$bh x
where hladdr in
('C0000000CDFF24F0', 'C0000000CE3ADDF0', 'C0000000CDF18A98')
order by hladdr, obj;
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
------------------------ ---------- ------------------ ------------- ------------- ------
C0000000CDF18A98 57 OBJAUTH$ 1 43911 1
C0000000CDF18A98 73 IDL_UB1$ 1 27025 1
C0000000CDF18A98 181 C_TOID_VERSION# 1 26792 1
C0000000CDF18A98 181 C_TOID_VERSION# 1 14244 1
…
C0000000CDF18A98 55236 CBC_TEST_IDX 4 45919 130
…
C0000000CDFF24F0 55236 CBC_TEST_IDX 4 45918 130
C0000000CE3ADDF0 2 IND$ 1 7933 1
C0000000CE3ADDF0 2 IND$ 1 60455 9
C0000000CE3ADDF0 18 OBJ$ 1 29623 1
...
C0000000CE3ADDF0 55236 CBC_TEST_IDX 4 100 130
X$BH 뷰로부터 1) 사용자 객체(Table, Index)에 해당하며, 2) 접촉 회수(Touch Count)가 높은 블록을 기준으로 핫블록을 추출할 수 있다. 위의 조회결과를 보면 CBC_TEST_IDX 인덱스의 45918, 45919, 100 블록에서 대부분의 경합이 발생하는 것을 확인할 수 있다.
[편집] Parameter & Wait Time
[편집] Wait Parameters
latch free 대기이벤트와 동일하다.
[편집] Wait Time
latch free 대기이벤트와 동일하다.
[편집] Check Point & Solution
[편집] 비효율적인 SQL 문을 튜닝한다.
비효율적인 SQL 문장을 튜닝해서 Logical Reads를 줄이면 자연스럽게 버퍼 캐시에 대한 액세스가 줄어들고 그만큼 cache buffers chains 래치 경합도 감소한다.
[편집] 핫블록을 분산한다.
핫블록에 의한 cache buffers chains 래치 경합인 경우에는 핫블록을 분산함으로써 경합을 감소시킬 수 있다. 핫블록을 분산시키는 방법은 다음과 같다.
- PCTFREE를 높게 주거나 작은 크기의 블록을 사용함으로써 블록 경합을 줄인다. PCTFREE를 높게 주는 것과 작은 크기의 블록을 사용하는 것은 하나의 블록에 포함되는 로우수를 줄임으로써 블록 경합을 피한다는 점에서 기본적으로 동일한 방법이다. 이 방법은 확실히 블록 경합을 줄이는 효과가 있지만 그 만큼 관리해야 할 블록수가 늘어남으로써 다른 사이드 이펙트를 초래할 수 있다. 가령 블록수가 늘어남으로써 동일한 쿼리가 훨씬 더 많은 블록을 스캔해야 하기 때문에 성능 저하 현상이 생기게 된다. 즉 핫블록에 의한 래치 경합은 줄어들지만, 늘어난 스캔 회수만큼 다시 래치 경합이 증가할 수도 있다. 따라서 테스트에 의한 검증없이 적용하는 경우 일반적인 가이드와는 전혀 다른 결과를 낳을 수도 있다. 경합 해소를 위한 일반적인 가이드를 그대로 따르는 것은 때로는 위험할 수 있기 때문에 반드시 실제 데이터를 이용해 테스트를 수행하는 것이 바람직하다.
- 파티셔닝(Partitioning) 기법을 사용해서 로우가 물리적으로 다른 블록으로 들어가게끔 한다. 이 기법을 사용하면 문제가 되는 로우들이 자연스럽게 물리적으로 다른 블록에 분산시킴으로써 래치 경합을 피할 수 있다. 하지만, 테이블에 대해서 이 방법을 적용할 경우에는 인덱스의 클러스터링 팩터(Clustering Factor)를 악화시킬 수 있으며, 이로 인해 인덱스범위스캔에 의한 데이터 스캔 속도가 저하될 수도 있다. 따라서 파티셔닝을 적용할 때도 사이드 이펙트(Side-effect)를 충분히 고려해야 한다.
- 문제가 되는 블록의 로우들에 대해서만 삭제 후 재삽입 작업을 한다. 이 방법은 테이블에 대해서만 가능하다. 문제가 되는 블록들과 해당 블록에 포함된 로우 들의 ROWID를 정확하게 알 수 있다면 해당 로우를 삭제한 후 재삽입해서 각 로우가 다른 블록에 흩어지게 할 수 있다. 블록 덤프와 DBMS_ROWID 패키지를 이용하면 핫블록에 속한 ROWID를 알 수 있다. 이 방법은 테이블 속성을 변경하지 않는 가장 이상적인 방법이라고 할 수 있다. 하지만 핫블록이 고정되어 있지 않고 SQL문의 조건(Where …)에 따라 매번 바뀐다면 적용할 수 없다. 또한 인덱스에 대해서는 이 방법을 사용할 수 없다.
테이블에서의 cache buffers chains 래치 경합은 해결하기가 비교적 쉬운 편이다. 로우를 분산시킬 수 있는 방법이 매우 다양하기 때문이다. 하지만, 인덱스에서의 경합 문제는 상당히 까다롭다. 정렬된 상태로 저장된다는 인덱스의 고유 특성 때문에 임의의 블록으로 분산시키는 것이 불가능한 경우가 있기 때문이다. 이 경우에는 PCTFREE를 높게 주거나 작은 크기의 블록을 사용하는 방식을 사용하는 것 외에는 뾰족한 대책이 없다. 하지만 앞서 설명한 것처럼, 이 경우에 블록의 개수가 늘어나게 되고 이로 인해 오히려 래치 경합이 줄어들지 않는 경우도 있으므로 적용 시에 유의해야 한다.
[편집] Event Tip
[편집] 버퍼 캐시 구조
오라클은 물리적인 I/O를 최소화하기 위해 최근에 사용된 블록에 대한 정보를 메모리의 일정 영역에 보관한다. 이 메모리 영역을 버퍼 캐시라고 부른다. 버퍼 캐시는 Shared Pool, 리두 버퍼(Redo Buffer)와 함께 SGA를 이루는 가장 중요한 메모리 영역 중 하나다. 아래 명령을 이용해 현재 인스턴스의 버퍼 캐시(Buffer cache)의 크기를 알 수 있다.
SQL> show sga
Total System Global Area 314572800 bytes
Fixed Size 788692 bytes
Variable Size 144963372 bytes
Database Buffers 163577856 bytes
Redo Buffers 5242880 bytes
Database Buffers에 해당하는 값이 현재 인스턴스의 버퍼 캐시의 크기이다.
오라클은 버퍼 캐시를 효과적으로 관리하기 위해 해시 체인(Hash chain) 구조를 사용한다. 해시 체인은 Shared Pool 내에 존재하며 오라클의 전형적인 메모리 구조 관리기법인 버킷(Bucket) -> 체인(Chain) -> 헤더(Header) 의 구조를 사용한다. 이 구조는 아래 그림에 표현되어 있다.
그림 오라클 8i이상에서의 버퍼 캐시 구조도
해시 체인 구조의 시작점은 해시 테이블(Hash table)이다. 해시 테이블은 여러 개의 해시 버킷(Hash bucket)으로 이루어져 있다. 하나의 해시 버킷은 해시함수 결과와 매칭된다. 오라클은 블록의 주소(DBA: Data Block Address. File#와 Block#으로 이루어져 있음)와 블록 클래스에 대해 간단한 해시 함수를 적용한 결과를 이용해 해시 버킷을 찾아간다. 해시 버킷에는 같은 해시값을 갖는 버퍼 헤더(Buffer Header)들이 체인 형태로 걸려 있다. 버퍼 헤더는 버퍼(Buffer)에 대한 메타정보를 가지고 있으며, 버퍼 메모리 영역의 실제 버퍼에 대한 포인터 값을 가지고 있다. 해시 체인 구조는 Shared Pool 영역에 존재하며, 실제 버퍼에 대한 정보들은 버퍼 캐시 영역에 존재한다는 사실을 명심하자.
해시 체인 구조는 cache buffers chains 래치를 이용해 보호된다. 특정 블록을 스캔하고자 하는 프로세스는 반드시 해당 블록이 위치한 해시 체인을 관리하는 cache buffers chains 래치를 획득해야 한다. 기본적으로 한번에 하나의 프로세스만이 하나의 cache buffers chains 래치를 획득할 수 있으며 하나의 cache buffers chains 래치가 여러 개의 해시 체인을 관리한다. 따라서, 동시에 많은 수의 프로세스가 버퍼 캐시를 탐색할 경우 cache buffers chains 래치를 획득하는 과정에서 경합이 발생하며, 이 과정에서 latch: cache buffers chains 이벤트를 대기한다. 오라클 9i 부터는 읽기 전용의 작업에 한해서 cache buffers chains 래치를 Shared 모드로 획득한다. 따라서 동시에 읽기 작업을 수행하는 프로세스간에는 cache buffers chains 래치를 공유할 수 있다. 하지만 버퍼에 대해 Buffer Lock을 획득하거나 해제할 때 cache buffers chains 래치를 Exclusive하게 획득해야 하기 때문에 읽기 작업만 수행하는 경우에도 여전히 cache buffers chains 래치 경합은 발생한다.
다음과 같은 명령문으로 cache buffers chains 래치의 개수를 구할 수 있다.
SQL> select count(*) from v$latch_children where name =
'cache buffers chains';
COUNT(*)
----------
1024
또는 _DB_BLOCK_HASH_LATCHES 히든 파라미터값을 조회해도 같은 결과를 얻을 수 있다. 해시 버킷의 수는 _DB_BLOCK_HASH_BUCKETS 히든 파라미터값을 이용해 조회 가능하다.
[편집] Working Set
오라클은 버퍼 캐시를 효율적으로 사용하기 위해 두 종류의 LRU(Least Recently Used) 리스트를 사용한다. LRU 리스트는 가장 최근에 사용되거나 미사용된 버퍼들의 리스트로 프리(Free. 미사용) 버퍼, 사용 중이거나 사용된 버퍼, 아직 LRUW 리스트(Dirty List)로 옮겨지지 않은 더티(Dirty. 변경된) 버퍼 등을 포함한다. 일부 문서에서는 LRU 리스트를 대체 리스트(Replacement List)라고 부른다. LRUW 리스트는 아직 디스크에 기록되지 않은 변경된(Dirty한) 버퍼들의 리스트를 관리한다. 버퍼 캐시의 모든 버퍼들은 반드시 LRU 리스트 또는 LRUW 리스트 둘 중의 하나에 속한다. LRUW 리스트는 더티 리스트(Dirty List), 또는 기록 리스트(Write List)라고도 부른다.
오라클은 리스트 스캔의 효율성을 위해 LRU 리스트나 LRUW 리스트를 다시 메인 리스트(Main List)와 보조 리스트(Auxiliary List)로 나누어 관리한다. 이를 정리하면 다음과 같다.
LRU 리스트(대체 리스트)
- 메인 리스트 : 사용된 버퍼들의 리스트. 핫 영역과 콜드 영역으로 구분 관리된다.
- 보조 리스트 : 프리 버퍼들의 리스트. 더 정확하게 표현하면, 미 사용된 버퍼들이나, DBWR에 의해 기록된 버퍼들의 리스트
LRUW 리스트(기록 리스트)
- 메인 리스트 : 변경된 버퍼들의 리스트
- 보조 리스트 : 현재 DBWR에 의해 기록중인 버퍼들의 리스트
오라클은 프리 버퍼 탐색 시, 우선 LRU 리스트의 보조 리스트에서 프리 버퍼를 찾는다. 보조 리스트의 버퍼가 모두 사용된 경우에는, 메인 리스트의 콜드 영역에서 프리 버퍼를 찾는다. 인스턴스가 최초로 구동된 때는 모든 버퍼들은 보조 리스트에서 관리된다. 또한 변경된 버퍼들이 DBWR에 의해 기록된 후에는 다시 프리 버퍼로 바뀌며, LRU 리스트의 보조 리스트에 추가된다.
LRU 리스트 와 LRUW 리스트는 항상 짝(Pair)으로 존재하며, 이 짝을 Working Set이라고 부른다(즉 Working Set = LRU + LRUW). 오라클은 복수 개의 Working Set을 사용한다. 하나의 Working Set을 하나의 cache buffers lru chain 래치가 관리한다. LRU 리스트나 LRUW 리스트를 탐색하고자 하는 프로세스는 반드시 cache buffers lru chain 래치를 획득해야 한다. 따라서 동시에 많은 프로세스가 LRU 리스트나 LRUW 리스트를 탐색하고자 할 경우에 cache buffers lru chain 래치를 획득하기 위해 경쟁하게 되며 이 과정에서 latch: cache buffers lru chain 이벤트를 대기한다.
_DB_BLOCK_LRU_LATCHES 히든 파라미터의 값을 조회하거나, 다음 명령문을 이용해 cache buffers lru chain 래치의 최대 개수를 구할 수 있다.
SQL> select count(*) from v$latch_children where name = 'cache buffers lru chain';
COUNT(*)
----------
16
하지만 위의 래치를 다 사용하는 것은 아니다. 오라클에는 다양한 종류의 버퍼 풀이 존재하며 각 버퍼 풀들이 이들 래치를 골고루 사용한다. 첫째, 버퍼는 크게 Default 버퍼 풀, Keep 버퍼 풀, Recycle 버퍼 풀로 나누어 진다. 둘째, Default 버퍼 풀은 다시 블록 크기 별로 표준블록사이즈, 2K, 4K, 8K, 16K, 32K 버퍼 풀로 나누어 진다. 개개의 버퍼 풀은 각각 독립적인 cache buffers lru chain 래치를 사용한다. 따라서 래치의 최소 개수는 8개가 된다. 다음 명령문을 사용하면 어떤 래치가 어떤 종류의 버퍼에 대해 사용 중인지를 확인할 수 있다.
SQL>
-- x$kcbwds=Working Set, x$kcbwbpd=Buffer pool, v$latch_children=Latch
select d.blk_size, c.child#, p.bp_name, c.gets, c.sleeps
from x$kcbwds d, v$latch_children c, x$kcbwbpd p
where
d.set_latch = c.addr
and d.set_id between p.bp_lo_sid and p.bp_hi_sid
order by c.child#
;
BLK_SIZE CHILD# BP_NAME GETS SLEEPS
---------- ---------- -------------------- ---------- ----------
8192 1 KEEP 42 0
8192 2 KEEP 42 0
8192 3 RECYCLE 42 0
8192 4 RECYCLE 42 0
8192 5 DEFAULT 2337 0 <-- 실제 사용중
8192 6 DEFAULT 2322 0 <-- 실제 사용중
2048 7 DEFAULT 33 0
2048 8 DEFAULT 33 0
4096 9 DEFAULT 32 0
4096 10 DEFAULT 32 0
8192 11 DEFAULT 32 0
8192 12 DEFAULT 32 0
16384 13 DEFAULT 32 0
16384 14 DEFAULT 32 0
32768 15 DEFAULT 32 0
32768 16 DEFAULT 32 0
위의 결과를 해석하면 Keep 버퍼 풀에 대해 2개, Recycle 버퍼 풀에 대해 2개, 그리고 Default 버퍼 풀에 대해 블록 크기 별로 각각 2개씩 래치를 사용하는 것을 알 수 있다. 만일 Default 버퍼 풀에 8K 표준 크기의 버퍼 풀만 사용한다면 2개의 lru 래치 만을 사용하게 될 것이다. 최대 16개의 래치 개수는 CPU 개수로부터 유래된 것이다. 오라클은 DBWR의 개수가 4보다 작으면 4 * CPU_COUNT 만큼 lru 래치를 생성하고, DBWR의 개수가 4이상이면 DB_WRITER_PROCESSES * CPU_COUNT 만큼 lru 래치를 생성한다. 필자의 시스템에서는 CPU 개수가 네 개이므로 16개의 래치가 생성되었고 그 중 실질적으로 사용되고 있는 것은 8K 버퍼 풀에 할당된 두 개의 래치임을 알 수 있다. 단, 앞서 언급한 것처럼 버퍼 풀의 최소 개수가 8개이기 때문에 lru 래치의 최소 개수도 8개임에 유의하자.
[편집] 버퍼 캐시 LRU 리스트의 관리
서버 프로세스가 스캔하는 모든 버퍼들이 LRU 리스트에 등록되기 때문에 LRU 리스트를 효율적으로 관리하는 것이 매우 중요하다. 특히 불필요하게 많은 량의 블록을 스캔하는 프로세스에 의해 중요한 버퍼들이 버퍼 캐시에서 밀려나는 것을 최소화할 수 있어야 한다. 오라클은 8i 이후의 버전부터 LRU 리스트를 효율적으로 관리하기 위해 Touch count에 기반한 LRU 알고리즘을 사용한다. 이 알고리즘은 LRU 리스트의 메인 리스트를 관리하는데 사용된다. Touch count 기반의 LRU 알고리즘을 그림으로 표현하면 아래 그림과 같다.
Touch count 기반의 LRU 알고리즘은 다음과 같은 방식으로 작동한다.
- LRU 리스트의 메인 리스트는 크게 핫 영역(Hot Region)과 콜드 영역(Cold Region)으로 나누어진다. 자주 사용되는 블록은 핫 영역에 머무르며, 사용빈도가 낮은 블록은 콜드 영역에 머무른다. 오라클은 개별 버퍼마다 Touch count(접촉 회수)를 관리하며, 프로세스에 의해 스캔이 이루어질 때마다 Touch count를 1씩 증가시킨다.
- 프리 버퍼를 찾을 때는 우선 LRU 리스트의 보조 리스트에서 미사용된 버퍼를 찾는다. 만일 보조 리스트가 비어 있다면, 메인 리스트의 콜드 영역의 꼬리에서부터 프리 버퍼를 찾는다. 메인 리스트의 꼬리에 있으면서 Touch count가 1이하인 버퍼가 프리 버퍼로 사용된다. 프리 버퍼를 찾는 과정에서 Touch count가 2 이상인 블록을 만나면 핫 영역의 머리(Head of Hot Region)로 옮기고 해당 버퍼의 Touch count를 0으로 초기화시킨다. 핫 영역으로 옮기는 기준이 되는 값은 _DB_AGING_HOT_CRITERIA 히든 파라미터이며 기본값이 2이다.
- 싱글 블록 I/O에 의해 읽혀진 블록은 Mid-point에 삽입되며 Touch count는 1의 값을 지닌다. Mid-point가 가리키는 위치는 콜드 영역의 머리(Head of Cold Region)이다. 싱글 블록 I/O에 읽혀진 블록은 콜드 영역의 머리에 위치함으로써 버퍼 캐시에 머무를 확률이 높아진다.
- 멀티 블록 I/O에 의해 읽혀진 블록들은 Mid-point에 삽입된 후 콜드 영역의 제일 뒤(Tail of Cold Region)으로 옮겨진다. 풀테이블스캔(FTS)이나 인덱스풀스캔으로 읽힌 블록들은 콜드 영역의 꼬리에 위치함으로써 버퍼 캐시에 머무를 확률이 낮아진다.
- Keep 버퍼 풀과 Recycle 버퍼 풀은 Default 풀과는 달리 영역의 구분이 불필요하므로 핫 영역을 가지지 않는다. Recycle 버퍼 풀은 핫 영역을 가지지 않는다는 점을 제외하면 Default 버퍼 풀과 완전히 동일한 방식으로 작동한다. 하지만 Keep 버퍼 풀의 경우에는 FTS로 읽히는 작은 크기의 테이블을 메모리에 상주시키기 위해 고안된 공간이기 때문에 멀티 블록 I/O로 읽은 블록들을 싱글 블록 I/O로 읽은 블록과 동일하게 콜드 영역의 제일 앞에 위치시키도록 구현되었다.
[편집] 버퍼 탐색 과정
오라클은 해시 체인과 LRU, LRUW 리스트를 적절히 사용해서 사용자가 요청한 블록을 버퍼 캐시로 위치시킨다. 그 절차 및 기법을 시간 순으로 정리하면 다음과 같다.
- 사용자가 요청한 블록의 DBA와 클래스에 대해 해시 함수를 이용해서 해시 값을 생성하고 해시 값에 해당하는 해시 버킷을 찾는다.
- 해시 버킷을 보호하는 cache buffers chains 래치를 획득한다. 읽기 작업이라면 Shared 모드로, 변경 작업이라면 Exclusive 모드로 래치를 획득한다. 만일 이 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기한다. 해시 버킷에 딸려있는 체인을 탐색해서 블록에 해당하는 버퍼 헤더가 존재하는지 확인한다. 버퍼 헤더가 이미 존재하고 해당 블록이 버퍼 캐시에 올라와 있는 상태라면 해당 버퍼에 대해 Buffer Lock을 Shared 모드나 Exclusive 모드로 획득하고 원하는 작업을 수행한다. 일반적으로 Buffer Lock을 획득하는 과정에서 경합이 발생하면 buffer busy waits 이벤트를 대기하게 된다. DBWR에 의해 기록중인 버퍼에 대해 Buffer Lock을 획득하는 과정에서 경합이 발생하는 경우에는 write complete waits 이벤트를 대기한다. cache buffers chains 래치를 획득한 후 해시 체인을 탐색하고 버퍼를 사용하기 위해 Buffer Lock을 획득한 후 버퍼를 읽는 일련의 작업을 “Logical Reads”라고 부른다. Logical Reads가 발생한 블록 수만큼 session logical reads 통계 값이 증가한다. 만일 Logical Reads 작업이 일관된 모드의 읽기(Consistent read) 작업이라면 consistent gets 통계 값이 증가하고, 현재 모드의 읽기(Current read) 작업이라면 db block gets 통계 값이 증가한다. 따라서 session logical reads 통계 값은 consistent gets 통계 값과 db block gets 통계 값의 합과 일치한다.
- 버퍼 캐시에 블록이 존재하지 않으면 우선 Working Set을 관리하는 cache buffers lru chain 래치를 획득한다. 이 과정에서 경합이 발생하면 latch: cache buffers lru chain 이벤트를 대기한다. 래치를 획득한 후 LRU 리스트의 보조 리스트에서 프리 버퍼를 찾는다. 보조 리스트가 비었다면, 메인 리스트에서 가장 덜 사용된 순서로 프리 버퍼를 찾는다. 이 과정에서 더티 버퍼가 발견되면 LRUW 리스트로 이동시킨다. 프리 버퍼를 찾게 되면 해당 버퍼에 대해 Buffer Lock을 Exclusive하게 획득하고 데이터 파일로부터 블록을 해당 버퍼로 읽어 들인다. 이때 Buffer Lock을 획득하는 과정에서 경합이 발생하면 read by other session 이벤트를 대기한다. 데이터 파일로부터 물리적으로 블록을 읽어 들이는 일련의 작업을 Physical Reads”라고 부른다. Physical Reads가 발생한 블록 수만큼 physical reads 통계 값이 증가한다. physical reads 통계 값은 direct path I/O 작업에서도 증가하기 때문에, 버퍼 캐시를 경유한 정확한 Physical Reads 값은 physical reads 통계 값에서 physical reads direct, physical reads direct(lob) 통계 값을 빼면 된다.
- LRU 리스트에서 프리 버퍼를 찾을 때 _DB_BLOCK_SCAN_MAX_PCT(기본값은 40) 파라미터의 값만큼 스캔을 하고도 프리 버퍼를 찾지 못하면 서버 프로세스는 LRU 리스트의 스캔을 멈춘다. 서버 프로세스는 DBWR에게 더티 버퍼를 파일에 기록하고 프리 버퍼를 확보할 것을 요청한다. DBWR에 의해 프리 버퍼가 확보될 때까지 서버 프로세스는 free buffer waits 이벤트를 대기한다. 요청을 받은 DBWR은 DBWR make free request 통계 값을 증가시키고, cache buffers lru chain 래치를 획득한 후 LRUW 리스트를 콜드 영역의 꼬리에서부터 탐색한다. 디스크에 기록할 버퍼를 찾게 되면 Buffer Lock을 획득한 후 버퍼를 디스크에 기록한다. 디스크에 기록된 버퍼는 프리 버퍼로 변경되고 LRU 리스트로 옮겨진다. DBWR이 LRUW 리스트를 탐색할 때마다 DBWR lru scans 통계 값과 DBWR buffers scanned 통계 값이 증가한다.
[편집] Analysis Case
[편집] 1. 인덱스 튜닝에 의한 latch: cache buffers chains 대기 감소
latch: cache buffers chains 이벤트 발생 시나리오는 다음과 같다.
- t_cache_buffers_chains_latch(type,name,id) 테이블에는 16만건의 데이터가 입력되어 있으며, 이중 type=4, name=’KIM’을 만족하는 데이터는 15만건이다.
- idx_cache_buffers_chains_latch 인덱스는 type, name 두개의 컬럼으로 구성되어 있다.
- 10개의 세션이 동시에 (type=4, name=’KIM’, id=4)을 만족하는 데이터의 개수를 구하는 SQL을 idx_cache_buffers_chains_latch 인덱스를 사용하여 수행된다.
- 세션들은 수행중 내내 latch: cache buffers chains 이벤트를 대기한다.
위의 시나리오를 그림으로 표현하면 아래 그림 1과 같다. 
그림 latch: cache buffers chains 이벤트 발생 시나리오
동시에 여러 프로세스가 위의 쿼리를 실행하는 과정을 모니터링한 결과는 아래 표와 같다. latch: cache buffers chains 이벤트 대기가 가장 심각하게 발생함을 알 수 있다.
표 모니터링 결과
| 실행 결과 |
SQL> @report
---- Report Scenario No. 2 -------------------------
scenario_name : cache_buffers_chains_latch
session# : 10
expired_type : 1
inteval : 30
Type=EVENT, Name=jobq slave wait, Value=50404(cs)
Type=EVENT, Name=latch: cache buffers chains, Value=3061(cs)
Type=EVENT, Name=read by other session, Value=677(cs)
Type=EVENT, Name=db file sequential read, Value=537(cs)
Type=EVENT, Name=events in waitclass Other, Value=534(cs)
Type=EVENT, Name=library cache pin, Value=73(cs)
Type=EVENT, Name=enq: TX - row lock contention, Value=63(cs)
Type=EVENT, Name=buffer busy waits, Value=15(cs)
Type=EVENT, Name=latch: library cache, Value=0(cs)
Type=EVENT, Name=latch: shared pool, Value=0(cs)
Type=EVENT, Name=cursor: mutex X, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=12955464
Type=STATS, Name=session logical reads, Value=7205239
Type=STATS, Name=redo size, Value=25088
Type=STATS, Name=execute count, Value=513
Type=STATS, Name=physical reads, Value=356
Type=STATS, Name=parse count (total), Value=229
Type=STATS, Name=sorts (memory), Value=110
Type=STATS, Name=parse time elapsed, Value=92
Type=STATS, Name=redo entries, Value=87
Type=STATS, Name=parse count (hard), Value=62
Type=STATS, Name=session cursor cache hits, Value=53
Type=STATS, Name=user commits, Value=20
Type=STATS, Name=sorts (disk), Value=0
Type=TIME, Name=DB time, Value=29893(cs)
Type=TIME, Name=sql execute elapsed time, Value=29808(cs)
Type=TIME, Name=parse time elapsed, Value=103(cs)
Type=TIME, Name=hard parse elapsed time, Value=23(cs) |
RealTime Client의 Active Session List는 아래 그림 2와 같다. 대부분의 액티브 세션들이 latch: cache buffers chains 이벤트를 대기하고 있는 것을 확인할 수 있다.
그림 RealTime Client – Active Session List
위와 같은 같은 상황에서 latch: cache buffers chains 이벤트 대기가 발생하는 이유는 다음과 같다.
- idx_cache_buffers_chains 인덱스를 사용하여 데이터를 검색하면 16만건 중 15만건의 데이터가 조건을 만족하므로 이 15만건에 대해 다시 테이블 액세스가 발생하게 되어 결과적으로 넓은 범위의 블록에 대한 랜덤 엑세스(Random Access)가 반복적으로 발생하게 된다.
- Cache Buffer 는 검색성능을 향상시키기 위해 해시체인(Hash Chains)으로 관리되어지는데 이 체인을 검색하기위해서는 반드시 래치를 획득하여야만 한다. 하지만 10개의 세션이 동시에 래치를 획득하기 위해 경합이 발생하게 되므로 latch: cache buffers chains 이벤트에 대한 대기가 불가피하게 된다.
SQL의 검색조건중 id컬럼은 선택도가 양호하므로 이것을 인덱스 사용시 이용할 수 있도록 한다.
- 기존의 idx_cache_buffers_chains에 id컬럼을 추가한다.
- 15만회에 걸쳐 테이블검색을 하던 것을 1회검색으로 원하는 결과를 얻게 되므로 경합은 현저히 줄어들게 된다.
그림 latch: cache buffers chains 이벤트 대기 개선 시나리오
-- 기존의 idx_cache_buffers_chains 인덱스 삭제
DROP INDEX idx_cache_buffers_chains;
-- 인덱스에 id컬럼을 추가하여 새로 생성
CREATE INDEX idx_cache_buffers_chains
ON t_cache_buffers_chains_latch(type,name,id);
성능 개선 후의 모니터링 결과는 아래 표와 같다. 표 2에서 latch: cache buffers chains 이벤트의 대기 시간이 1133(cs)로, 성능 개선 전인 3061(cs)이었던 것에 비하면 3배 정도 개선효과가 있는것을 확인할 수 있다.
표 자체 모니터링 결과
| 실행결과 |
SQL> @report
---- Report Scenario No. 3 -------------------------
scenario_name : cache_buffers_chains_latch
session# : 10
expired_type : 1
inteval : 30
Type=EVENT, Name=jobq slave wait, Value=4994(cs)
Type=EVENT, Name=latch: cache buffers chains, Value=1133(cs)
Type=EVENT, Name=latch: library cache, Value=362(cs)
Type=EVENT, Name=enq: TX - row lock contention, Value=284(cs)
Type=EVENT, Name=latch: library cache pin, Value=135(cs)
Type=EVENT, Name=library cache load lock, Value=75(cs)
Type=EVENT, Name=library cache pin, Value=32(cs)
Type=EVENT, Name=events in waitclass Other, Value=18(cs)
Type=EVENT, Name=db file sequential read, Value=2(cs)
Type=EVENT, Name=cursor: mutex S, Value=0(cs)
Type=EVENT, Name=latch: In memory undo latch, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=10530632
Type=STATS, Name=session logical reads, Value=3745047
Type=STATS, Name=execute count, Value=748866
Type=STATS, Name=redo size, Value=22120
Type=STATS, Name=parse count (total), Value=296
Type=STATS, Name=sorts (memory), Value=159
Type=STATS, Name=session cursor cache hits, Value=131
Type=STATS, Name=parse count (hard), Value=67
Type=STATS, Name=parse time elapsed, Value=57
Type=STATS, Name=redo entries, Value=53
Type=STATS, Name=user commits, Value=20
Type=STATS, Name=physical reads, Value=5
Type=STATS, Name=sorts (disk), Value=0
Type=TIME, Name=DB time, Value=26548(cs)
Type=TIME, Name=sql execute elapsed time, Value=26534(cs)
Type=TIME, Name=parse time elapsed, Value=85(cs)
Type=TIME, Name=hard parse elapsed time, Value=36(cs) |
[편집] 2. 과도한 Logical Read로 인한 cache buffer chain latch 발생 분석 사례
동시사용자가 많은OLTP 및 WEB환경에서, 부적절한 인덱스 사용으로 인한 과도한 I/O발생은 성능상의 심각한 문제를 야기하는 경우가 많다. Oracle DBMS의 성능진단/분석 툴인 MaxGauge(맥스게이지)를 활용하여, 부적절한 인덱스 사용으로 인한 과도한 I/O발생이 야기시키는 성능저하 문제의 원인을 규명해 보고자 한다.
[편집] 성능저하구간의 확인
성능문제가 발생한 인스턴스에서 수집된 가동이력 로그로부터 일간 추이그래프를 확인해 보면,「CPU 사용률」은 높지만 execute count가 적은 DB Management시간대(01시30분~05시00분)를 제외하고 「CPU 사용률], [Active session count], [Wait]가 동일한 추이로 변화하고 있음을 감지할 수 있다.
■ CPU사용률의 추이그래프 
■ Active Session수의 추이그래프 
■ Wait Events의 추이그래프（Wait Time or Waits） 
■ Execution Count의 추이그래프 
[편집] Wait Events의 검출 및 분석
Active Session의 급증으로 인한 성능저하（Performance Slow-Down）의 원인을 규명하기 위해, 문제시점(10시17분)의 Wait Events의 발생내용을 확인해 본다. 
｢Value｣ 탭에서 동 시점의 Top Wait Event를 확인한 결과, Idle Event(= SQL*Net message from client)를 제외한 Top Wait Event는 latch free임이 확인 된다.
Active Session의 급증에 대한 Latch free 대기이벤트의 연관성을 규정하기 위해, 대기이벤트와의 발생패턴을 비교해 본 결과, Active Session의 발생 추이와 상당히 유사하고, 문제시점에 발생한 Wait Events(Wait Time)의 약 53.4%(전체 109.89초 중에서, 58.69초를 점유 함）를 차지하고 있는 점에서, Active Session의 급증은 latch free 대기이벤트의 급격한 발생과 연관이 있음을 추측할 수 있다.
실제로, 같은 시점의 상세데이터를 표시하는 ｢세션 Grid｣ 화면에서도, latch free가 Top Wait Event이며, 그 중에서도 latch free(cache buffer chain) 대기 이벤트가 많이 발생해 있음을 확인할 수 있다.
[편집] Wait Event(Latch Free)발생원인의 조사
latch free대기이벤트의 발생원인에는 여러 가지가 있으나, 일반적으로 latch free (cache buffer chain) 대기이벤트가 발생한 경우에는 ｢Hot block｣이 그 원인일 경우가 많다. Hot block은 table full scan보다는 index range scan에서 빈번하게 발생하며, 해결책으로는 SQL 튜닝을 통하여 Index 검색 범위를 줄여야 하며 SQL 튜닝이 불가능한 경우에는 블록 사이즈를 줄이거나 pct free 값을 증가시켜 블록당 row수를 줄여야 한다.
[편집] 세션 및 SQL의 분석을 통한 문제원인의 규명
latch free (cache buffer chain)의 대기가 많았던 21시30분~ 21시40분 사이에서, latch free (cache buffer chain)를 유발한 SQL이 동일한 패턴의 SQL로 확인된다. 
[편집] 결론
latch free(cache buffer chain)대기이벤트의 다발에 의한 Active session의 급증 →
SQL 수행 시 비효율적인 Index Scan으로 인한 성능 저하 발생 →
Table Access 없이 Index Scan으로 수행되도록 해당 SQL의 조건절 칼럼을 Index에 모두 포함시켜 재생성 하여 해결
[편집] 3. 잘못된 인덱스 스캔으로 인한 Latch 경합
성능문제가 발생한 인스턴스에서 수집된 가동이력로그 및 실시간 모니터링을 통해 Active Session의 추이를 확인해본다. 문제 구간의 Active Session은 Cache Buffers Chains Latch 이벤트를 대기하고 있고, Active Session List의 Wait 항목에서 Latch의 주소값(ADDR)은 16321267696임을 알 수 있다.
Latch 경합이 발생한 Latch의 Address를 X$bh 뷰와 조인하여 어떤 오브젝트의 블록인지 확인한다.
-- X$bh 뷰와 조인하여 블록을 확인하는 SQL문 --
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name, b.object_type
from sys.xm$bh a, dba_objects b
where (a.obj = b.object_id or a.obj = b.data_object_id)
and a.hladdr = '00000003CCD2C7F0'
order by 4 desc
이 중, Touch Count(TCH)가 높은 블록을 확인한다.
같은 Latch Addr 로 조회 시 여러 개의 object 결과값이 나오는 이유는 하나의 래치가 여러 개의 체인을 관리하기 때문이다. 문제의 인스턴스의 Cache Buffers Chains Latch는 1024 개이고, _DB_BLOCK_HASH_BUCKETS 파라미터는(해시 버킷의 수) 254083 개이다. 즉, 문제의 인스턴스에서 1개의 CBC 래치는 약 249 개의 체인을 관리하고 있다.
ABC$123 이란 INDEX가 가장 TCH가 높으므로, Cache Buffers Chains Latch 경합의 주요 발생 블록임을 유추할 수 있다.
dba_indexes 뷰를 통해 해당 인덱스의 TABLE을 확인하고, 해당 테이블의 인덱스 구성을 확인한다.
select index_name, table_name
from dba_indexes
where index_name = 'ABC$123';
ABC$123 인덱스는 ABC 테이블에 있으며, ABC 테이블은 ABC_NUM + ABC_TIM 으로 구성된 UNIQUE 인덱스와 ABCTYPE으로 구성된 인덱스를 가지고 있음. 래치 경합으로 문제가 된 인덱스는 DMLTYPE로 구성된 ABC$123 인덱스이다.
경합이 발생한 세션의 SQL문의 트레이스 결과를 확인해본다.
-- SQL문 --
UPDATE ABC
SET version$$=version$$+1 ,
type$$='I'
WHERE ABC_NUM =:b2
AND ABC_TIM" =:b1
AND ABC_TYPE='D'
SQL문의 조건에 PK Index의 구성 컬럼이 있음에도 불구하고, 범위가 넓은 인덱스를 선택해 SQL문이 실행되었음을 알 수 있다. (query 부분을 통해, 수행 시 불필요하게 174427 블록을 액세스함을 알 수 있다.)
이 경우, 힌트를 적용하여 PK Index를 경유하게 되면, Cache Buffers Chains Latch 경합을 해소할 수 있다.
[편집] 4. 인덱스 컬럼 변형에 의한 Latch 대기현상
시스템의 CPU 사용율이 Logical Reads와 Physical Reads와 유사한 추이를 나타내고 있다. SQL의 수행횟수를 나타내는 Execute Count는 평균적으로 500회 정도를 유지하고 있으며, SQL의 수행횟수가 많아서 CPU를 많이 사용한 것은 아닌 것으로 추측된다. 
이 구간의 Active Session 추이를 확인해 보면, Latch Free 이벤트와 추이가 유사함을 알 수 있고, Cache Buffers Chains Latch를 대기하는 세션들을 발견할 수 있다. 
Cache Buffers Chains Latch는 SQL문을 수행 시에 넓은 처리범위로 인하여 발생한다. 또한, db file sequencial reads 이벤트의 발생 추이도 유사하므로, SQL문이 인덱스 스캔을 통해 수행하고 있으나 비효율적인 인덱스를 통해 데이터를 Access하고 있으며 이로 인해 처리범위가 과다하게 나타난 것으로 예상된다.
이 사례는 세션이 수행하고 있는 SQL문에서 distinct key의 종류가 많아서 분포도가 좋은 컬럼이 변형되어 그 컬럼의 인덱스를 경유하지 못하고 비효율적인 컬럼의 인덱스를 통해 데이터를 Access한 경우이다.
예를들면, 다음과 같다.
SELECT emp_id, emp_name, salary, emp_date, dept_id
FROM emp
WHERE emp_date >=:1
AND emp_date <=:2
AND TO_NUMBER( emp_id ) =:3
AND emp_name like :4
AND job_id =:5
와 같은 SQL문을 수행하는데, emp_id 컬럼에 TO_NUMBER라는 함수를 사용하여 해당 인덱스를 이용하지 못하고 emp_name의 컬럼의 인덱스를 이용해서 실행이 된 경우로 이해할 수 있다.
이 경우, 다음과 같이 변경하여야 한다.
SELECT emp_id, emp_name, salary, emp_date, dept_id
FROM emp
WHERE emp_date >=:1
AND emp_date <=:2
AND emp_id =to_char(:3)
AND emp_name like :4
AND job_id =:5
이와 같이, Latch: Cache buffers chains 가 발생하는 경우, 과다한 처리범위를 발생하는 SQL문을 추출하여야 하고, 처리범위를 줄이기 위해 Tuning이 필요하다.
using-dynamic-intimate-memory-sparc-168402.pdf
