꿈꾸는 개발자, DBA 커뮤니티 구루비

II. latch: cache buffers chains

1) Event 정의

Cache Buffer Chain을 탐색하기 위해 cache buffers chains latch를 획득하고자 대기하는 Event
- P1 = address
- P2 = number
- P3 = tries

2) 대기발생 사유

여러 세션이 동시에 동일 cache buffers chains latch를 Exclusive Mode로 요청
- 동일 세그먼트에 대한 동시 다발적인 Logical Reads에 의해 유발
- 핫 블록 또는 불량 SQL에 의해 발생

Latch: cache buffers chains 이벤트 발생 원인은 크게 두 가지 경우가 있다.

핫 블록(hot block)에 의한 경합
- 동시에 여러 프로세스가 동일 블록에 대해 접근하고자 할 경우 발생한다.
Bad SQL에 의한 경합
- 불필요한 블록을 많이 접근하는 SQL문장이 동시에 수행될 경우 발생한다.

3) Hot Block에 의한 latch 경합

SQL문이 원하는 버퍼에 접근하기 위해 cache buffers chains 래치를 획득을 하고 버퍼 헤더를 탐색한다.
한 세션이 래치를 가지고 있는 동안 다른 세션에서 동일한 블록을 접근 할 경우 다른 세션에서는 동일한 cache buffers chains 래치를 획득해야 하기 때문에 latch: cache buffers chains 이벤트를 대기한다.

참고) 핫 블록을 찾아내는 방법
X$BH뷰로부터 사용자 객체에 해당하며 접촉 횟수가 높은 블록을 기준으로 핫 블록을 추출한다.


      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 (       )
        order by hladdr, obj;

집중적으로 사용되는 cache buffer chains latch 검사


          select * from (
                        select addr, child#, gets, sleeps
                        from v$latch_children
                        where name = 'cache buffers chains'
                        order by sleeps desc)
          where rownum <= 20

4) Bad SQL에 의한 latch 경합

한 세션에서 bad SQL문이 수행 될 경우 다량의 버퍼를 접근하게 되며 특정 cache buffers chains 래치를 자주 획득하게 된다.
이때, 다른 세션에서 bad SQL문이 수행 되면서 다량의 버퍼를 탐색할 경우, 동일한 버퍼는 아니지만 동일한 cache buffers chains 래치를 사용하기 때문에 cache buffers chains 래치에 대한 경합이 발생하게 된다.

비효율적인 SQL문장이 cache buffers chains 래치 경합의 가장 중요한 원인이다.

동시에 여러 프로세스가 넓은 범위의 인덱스나 넓은 범위의 테이블에 대해 스캔을 수행할 경우 한 프로세스가 많은 버퍼 헤더를 읽어야 하므로 래치와 락을 소유하는 시간이 길어지게 된다. 이때 다른 프로세스가 래치을 못 얻어 latch: cache buffers chains 이벤트가 광범위하게 발생할 수 있다.

참고) Cache buffers chains 래치 경합 원인 규명
Cache buffers chains 래치 경합이 발생한 경우 원인을 정확하게 밝히는 것이 매우 중요하다.원인을 분석하는 데 가장 중요한 근거는 SQL문장 자체이다. SQL문장 자체가 비효율적으로 작성되어 있는 것으로 판단할 수 있는 근거가 확실하다면 SQL문장을 튜닝 함으로써 문제를 해결할 수 있다. SQL문장을 분석할 때 실행 계획이나 SQL trace등을 이용하면 더 정확한 분석을 할 수 있다.

만일, SQL 문장에 대한 정보가 없다면 간접적으로 핫 블록에 의한 문제인지 비효율적인 SQL문장에 의한 문제인지 판단할 수 있는 방법이 있다.
V$LATCH_CHILDREN 뷰에서 자식 cache buffers chains 래치에 해당하는 CHILD# 과 GETS, SLEEPS 값을 비교하여 특정 자식 래치에 사용하는 회수와 경합이 집중되는지 판단하거나 V$SESSION_WAIT 뷰로부터 얻은 래치의 주소가 과다하게 중복되어 나타난다면 해당 래치에 대한 편중이 생긴다는 의미이며 이 경우 핫 블록에 의한 경합으로 볼 수 있다.

5) Event 해소 방안

Hot Block인 경우 (일반적으로 latch: cache buffers chains 이벤트는 SQL튜닝만을 통해서는 해결할 수가 없다. 튜닝을 하더라도 특정 핫 블록을 읽어야 하기 때문이다. 이를 해결하기 위해서는 핫 블록의 데이터 분산이 필요하다.)
- PCTFREE를 높여주는 것이다. PCTFREE를 높여 주므로 한 블록에 들어가는 로우(row)수를 줄여준다. 이 경우 블록의 개수가 많아짐으로 전체범위 처리시 더 많은 블록을 접근할수 있으므로 충분한 검증이 필요하다.
- 파티셔닝(partitioning) 기법을 사용하는 것이다. 이 기법은 로우를 물리적으로 나누어 주는 기법이다. 하지만 블록의 각 로우들이 흩어지기 때문에 클러스터링 팩터(clustering factor)가 안 좋아질 수가 있다.
- 문제가 되는 로우들을 삭제 후 재 삽입 하는 방법이다. 특정 로우에 대해서만 작업이 진행되기 때문에 특정 로우에 대해서는 효과가 있지만 집중되는 로우가 바뀐다면 다른 로우에 대해서도 작업을 해줘야 효과가 생긴다. 즉 현상에 따라 일일이 작업을 해줘야 한다.

Bad SQL인 경우
- Bad SQL인 경우에는 SQL 튜닝을 통하여서 최소한의 블록을 읽도록 해야 한다.
- 병렬 쿼리(parallel query)를 적절하게 사용하는 것도 경합을 줄이는 방법이 될 수 있다.
- 병렬 쿼리는 SGA를 거치지 않고 데이터 파일에서 원하는 데이터를 직접 읽어 들이기 때문에 버퍼 캐시에서의 경합 자체가 존재하지 않는다

문서에 대하여

최초작성자 : 황종필
최초작성일 : 2010년 09월 18일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)엑셈에서 출간한 'PRACTICAL OWI IN ORACLE 10G'를 참고하였습니다.*

latch cache buffers chains (by sealooks) [2010.09.18]