Latch: library cache

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] Basic Info

shared pool 래치가 프리 청크를 찾기 위해 프리리스트를 스캔하고, 적절한 청크를 할당하는 작업을 보호한다면, library cache 래치는 SQL을 수행하기 위해 Library Cache 메모리 영역을 탐색하고 관리하는 모든 작업을 보호한다. library cache 래치는 CPU count 보다 큰 소수 중 가장 작은 수만큼 자식 래치(child latch)를 가진다.

library cache 래치를 획득하는 과정에서 경합이 발생하면 latch: library cache 이벤트를 대기한다. library cache 래치 경합은 주로 다음과 같은 경우에 발생한다.

  • 하드파싱이나 소프트파싱이 과다한 경우
  • 버전 카운트(Version count)가 높은 경우
  • SGA영역의 페이지 아웃(Page out)이 발생하는 경우

[편집] 하드파싱이나 소프트파싱이 과다한 경우

shared pool 래치 경합이 주로 하드파싱에 의한 프리리스트 탐색에 의해 발생하는 것처럼, library cache 래치 경합의 가장 중요한 원인 또한 하드파싱이다. 하드파싱이 많이 발생하면 아래와 같은 이유로 library cache 래치 경합이 유발된다.

  • Library Cache 를 탐색하는 회수가 늘어나므로 그 만큼 library cache 래치를 보유하는 시간과 회수가 늘어난다.
  • 하드파싱의 경우 Library Cache 영역에 대한 탐색뿐만 아니라 추가적인 청크 할당이 필요하기 때문에 그만큼 library cache 래치를 보유하는 시간이 늘어난다.

Library cache 래치 경합은 하드 파싱이 아닌 소프트파싱, 즉 바인드 변수를 잘 쓰고 있는 경우에도 발생가한다. 소프트파싱은 비록 하드파싱에 비해 그 비용이 매우 싸긴 하지만 신택스(Syntax) 체크 / 시맨틱(Semantics) 체크 / Library Cache 탐색 등의 과정은 피할 수 없다. 이 과정에서 Library Cache를 탐색하는 동안 library cache 래치를 획득해야만 한다. 따라서 많은 세션이 동시에 소프트파싱을 수행하는 경우 library cache 래치 경합에 의한 성능 저하 현상이 발생하게 된다.

[편집] 버전 카운트(Version Count)가 높은 경우

세 명의 서로 다른 유저가 다음과 같이 동일한 SQL문장을 수행했다고 가정해보자.

Scott : select * from emp where empno = 1;
Mary : select * from emp where empno = 1;
John : select * from emp where empno = 1;

위의 세 SQL문장은 Text가 완전히 동일하므로 동일한 해시 값을 갖는다. 따라서 동일한 해시 체인(Hash chain)의 동일한 핸들에 할당된다. 하지만 emp 테이블이 모두 스키마가 다른 테이블이므로 실제로는 다른 SQL 문장이다. 이 경우 오라클은 Text에 해당하는 부모 LCO를 두고 그 밑에 세 개의 자식 LCO를 만들어 개별 SQL 정보를 관리한다. 세 개의 자식 LCO는 실제로는 익명 리스트(Anonymous List)라고 하는 별도의 리스트에 저장된다.(그림6 참조) 세 개의 자식 LCO를 가지므로 V$SQLAREA 뷰의 VERSION_COUNT(버전 카운트) 컬럼값이 자식 LCO의 개수와 같은 3의 값을 가지게 된다. 버전 카운트가 높다는 것은 자식 LCO 탐색으로 인해 library cache를 탐색하는 시간이 그만큼 증가한다는 의미이며 이로 인해 library cache 래치 경합이 증가할 수 있다는 것을 의미한다. 만일 특정 SQL문장에서 library cache 래치 경합이 많이 발생한다면 해당 SQL의 버전 카운트값을 확인해볼 필요가 있다. 오라클의 버그로 인해 버전 카운트가 불필요하게 높아지는 경우가 있기 때문이다.

[편집] SGA 영역의 페이지 아웃(Page Out)이 발생하는 경우

Shared Pool이 디스크로 페이지 아웃된 경우, 해당 영역에 대한 스캔이 발생할 때 다시 디스크의 내용을 메모리로 불러들이는 과정(페이지인)동안 대기해야 하므로 library cache 래치에 대한 대기시간이 증가할 수 있다. 만일 latch: library cache 대기가 높은 시점에, O/S에서 스왑(Swap)현상이 발생한다면, 페이지 아웃에 의한 성능 저하일 확률이 높다.

[편집] Parameter & Wait Time

[편집] Wait Parameters

latch free 대기이벤트와 동일하다.

[편집] Wait Time

latch free 대기이벤트와 동일하다.

[편집] Check Point & Solution

[편집] 파싱회수를 줄인다.

library cache 래치의 경합을 줄이는 가장 좋은 방법은 한번 파싱에 여러 번 수행을 하는 것이다. 파싱 회수를 줄이면 그만큼 library cache 래치 경합을 줄일 수 있다. 가령 Java 환경이라면 PreparedStatement 객체를 이용해서 파싱을 1 회만 수행하고 close를 수행하지 않은 상태에서 반복적으로 execute를 수행함으로써 파싱회수를 줄일 수 있다. 하지만 항상 이 방법을 쓸 수 있는 것은 아니다. 무엇보다 어플리케이션에서 많은 수정이 필요하며, 업무 로직에 의해 이러한 방법을 사용하는 것이 불가능한 경우도 있을 수 있다. 특정 Web Application Server의 경우 Statement Cache 기능을 제공하는데, 이 기능을 쓰면 비슷한 효과를 얻을 수 있다.

PL/SQL의 Dynamic SQL을 사용할 때는 library cache 래치 경합이 증가할 수 있다는 사실에 유의해야 한다. Dynamic SQL을 사용하면 PL/SQL의 장점 중 하나인 커서(Cursor) 재사용(한번 파싱에 여러 번 실행)의 혜택을 받을 수 없으며, 이로 인해 소프트파싱이 증가하고 library cache 래치 경합이 증가하게 된다. 따라서 가능한 Static SQL을 사용할 것을 권장한다.

[편집] SESSION_CACHED_CURSORS 파라미터를 이용한다

SESSION_CACHED_CURSORS 파라미터 값이 세팅되어 있으면 오라클은 세 번 이상 수행된 SQL 커서에 대한 정보를 PGA내에 보관한다. 사용자가 SQL을 수행 요청할 때 오라클은 PGA에 캐싱된 정보가 있는지 확인하고, 만일 캐싱된 정보가 있다면 캐싱된 정보를 이용한다. 따라서 Library Cache 영역을 탐색하는 시간이 줄어들어 상대적으로 library cache 래치를 보유하는 시간이 줄어들게 된다. SESSION_CACHED_CURSORS 파라미터의 기본값은 버전마다 다르다. 만일 기본값이 작다면 되도록이면 50 이상의 값을 설정하는 것이 바람직하다.

[편집] Event Tip

[편집] Library Cache 구조

Shared Pool에서 가장 중요한 부분 중의 하나인 Library Cache 영역은 SQL문의 수행과 관련된 모든 정보들을 관리하는 영역이다. Library Cache 메모리는 Library Cache Manager(KGL, Kernel Generic Library Cache)에 의해 관리되는데, KGL은 KGH를 이용해서 필요한 메모리 청크를 할당 받는다.

Library Cache 메모리는 [해시 테이블 -> 버킷 -> 체인 -> 핸들 -> 오브젝트]의 구조로 되어 있다. 아래 그림에 Library Cache의 구조가 설명되어 있다.

그림 : Library_Cache.jpg
그림 Library Cache 구조

오라클은 객체 이름(가령 SQL 텍스트)에 해시함수를 적용해 생성된 해시값을 이용해 적절한 해시 버킷(Hash Bucket)을 할당하며, 같은 해시값을 지니는 객체들은 체인(리스트)으로 관리된다. 하나의 Library Cache 핸들(이하 핸들)은 하나의 Library Cache Object(이하 LCO)를 관리한다. 핸들은 실제 LCO에 대한 메타정보 및 포인터 역할을 하며 LCO가 실제 정보를 담고 있다. LCO가 포함하는 정보 중 중요한 것들은 다음과 같다.

  • Dependency Table : 현재 LCO가 의존하는 LCO 들에 대한 정보. 가령 SQL 문장은 해당 문장이 참조하는 테이블, 뷰 등에 대해 의존성(Dependency)를 지닌다.
  • Child Table : 현재 LCO의 자식 LCO 들에 대한 정보. 프로시저, 테이블과 같이 유일한 이름이 부여되는 LCO는 자식을 지니지 않는다. 오라클은 프로시저나 테이블과 같은 객체에 대해서는 스키마명을 항상 같이 저장하기 때문에 유일성이 보장된다. 하지만 SQL문장의 경우에는 SQL 텍스트 자체가 이름으로 사용되기 때문에 유일성이 보장되지 않는다. 따라서 오라클은 SQL 텍스트를 이름으로 갖는 부모 LCO를 생성하고 실제 SQL 커서에 대한 정보는 자식 LCO에 저장한다. 가령 두 개의 다른 스키마 A, B에서 텍스트는 동일하지만, 실제로 참조하는 객체는 다른 SQL 문장을 수행한 경우, 오라클은 SQL 텍스트에 해당하는 부모 LCO와 스키마 A가 수행한 SQL 커서에 해당하는 자식 LCO, 스키마 B가 수행한 SQL 커서에 해당하는 자식 LCO, 총 세 개의 LCO를 생성한다. 이 경우 V$SQLAREA.VERSION_COUNT 값은 3이 된다. 자식 LCO는 Library Cache 영역의 익명 리스트(Anonymous List)에 저장된다.
  • Data Blocks : LCO가 포함하는 실제 정보(데이터)를 저장하는 청크 영역들에 대한 포인터 정보. 가령 SQL 커서의 경우 SQL 문장, 실행계획, 실행문맥정보 등은 특정 메모리 영역(청크)에 보관되고, 이들 청크의 주소값이 LCO의 Data Blocks 영역에 관리한다.

Library Cache 덤프를 이용하면 Library Cache 구조가 물리적으로 어떻게 구성되어 있는지 관찰할 수 있다.

SQL> alter session set events 'immediate trace name library_cache level 10';

BUCKET 84:      <-- 버킷 번호
LIBRARY OBJECT HANDLE: handle=c000000099a05940   ? 핸들
 mutex=c000000099a05a70(1)    
  name=  ? LCO 이름
select   LOW_OPTIMAL_SIZE,           HIGH_OPTIMAL_SIZE,           
       OPTIMAL_EXECUTIONS,           ONEPASS_EXECUTIONS,           
    ...
    DATA BLOCKS: 
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 c000000099ad4480 c0000000994b9bd8 I/P/A/-/-    0 NONE   00      
    ? 청크 영역에 대한 포인터(c000000099ad4480 는 힙에 대한, c0000000994b9bd8 는 청크에 대한 포인터)
  
 LIBRARY OBJECT HANDLE: handle=c000000099baeb10 mutex=c000000099baec40(0)
  ...

  BUCKET 84 total object count=2

BUCKET 126:
LIBRARY OBJECT HANDLE: handle=c00000009e506c60 
    mutex=c00000009e506d90(0)
  name=SELECT SUM(BLOCKS * BLOCK_SIZE) FROM V$DATAFILE_COPY WHERE DBMS_RCVMAN.ISRECLRECID(:B2 , RECID) = :B1  
  ...

위의 Library Cache 덤프에서 확인할 수 있듯이 LCO 의 Data blocks 영역에 힙 영역에 대한 포인터(c000000099ad4480)를 가지고 있다. 힙 덤프 파일에서 위의 주소를 조회해보면 다음과 같은 값을 얻는다.

Chunk c0000000994b91b0 sz=1112    freeable  "PCursor"  ds=c000000099ad4480 

힙 덤프의 내용을 설명하면서 언급한 것처럼 위의 정보는 c000000099ad4480 주소값에 해당하는 서브힙 영역을 가리키며 서브힙 덤프를 이용해 정확한 서브힙과 청크의 위치를 확인할 수 있다.

Library Cache 영역을 탐색하고자 하는 모든 프로세스는 반드시 해당 해시버킷을 보호하는 library cache 래치를 획득해야 한다. 오라클은 CPU 개수보다 큰 소수중 가장 작은 수만큼 library cache 래치를 사용한다. 가령 CPU 개수가 4인 시스템에서는 5개의 library cache 래치를 사용한다. Library Cache 영역에 접근하는 과정에서 library cache 래치 경합이 발생하는 경우, 프로세스는 latch: library cache 이벤트를 대기한다.

library cache 래치가 Library Cache 영역에 대한 탐색을 동기화하는데 사용된다면 library cache locklibrary cache pin 이라는 이름의 두 개의 락은 핸들과 LCO를 보호하는 역할을 한다. 가령 alter table … 명령을 이용해 테이블을 변경하려는 프로세스는 테이블 정보를 저장하는 LCO에 대해 library cache lock을 Exclusive하게 획득해야 한다. library cache locklibrary cache pin을 획득하는 과정에서 경합이 발생하면 각각 library cache lock 이벤트와 library cache pin 이벤트를 대기한다.

[편집] SQL 수행

사용자가 SQL문장을 수행 요청하면 오라클은 위에서 Library Cache의 메모리 영역과 래치를 이용해 필요한 작업을 수행하게 되는데, 이것을 시간 순으로 정리하면 다음과 같다.

  1. 사용자가 새로운 SQL문장을 수행 요청하면 오라클은 기본적인 문법체크와 권한체크 등을 수행한 후, 해시 버킷을 관리하는 library cache 래치를 획득하고, Library Cache 영역에 동일한 SQL 문장, 즉 동일한 LCO가 존재하는지 확인한다. library cache 래치를 획득하는 과정에서 경합이 발생하면 latch: library cache 이벤트를 대기한다. 동일한 LCO가 존재하는 경우에는 8번 단계를 실행하게 되는데, 이 과정을 소프트파싱(Soft Parsing)이라고 부른다. 오라클은 SQL 파싱 요청이 있을 때마다 parse count(total) 통계 값을 증가시킨다.
  2. 만일 동일한 SQL 문장이 존재하지 않는다면, shared pool 래치를 획득하고 가장 적절한 크기의 프리 청크를 프리리스트에서 찾는다. shared pool 래치를 획득하는 과정에서 경합이 발생하면 latch: shared pool 이벤트를 대기한다. 오라클은 프리 청크가 확보될 때까지 계속해서 shared pool 래치를 보유한다.
  3. 만일 최적 크기의 프리 청크가 존재하지 않으면 조금 더 큰 크기의 프리 청크를 찾아서 이를 쪼개어(Split) 사용한다. 쪼개고 남은 메모리 영역은 다시 적절한 프리리스트로 등록된다.
  4. 모든 프리리스트를 탐색하고도 적절한 크기의 프리 청크를 찾지 못하면 LRU 리스트를 탐색한다. LRU 리스트의 청크들은 재생성가능(Recreatable)하면서 현재 사용 중이지 않은 것(pin되지 않은 것)들이다.
  5. LRU 리스트를 탐색하고도 적절한 크기의 청크를 확보하지 못하면 Shared Pool 내의 여유메모리 공간을 추가적으로 할당한다.
  6. 위의 과정이 모두 실패하면 ORA-4031 에러가 발생한다.
  7. 적절한 프리 청크를 찾으면 SQL 문장에 해당하는 핸들(Library Cache Handle)에 대해 library cache lock을 Exclusive하게 획득하고 LCO 정보를 생성한다. LCO가 생성되면 library cache lock을 Null 모드로 변환하고, library cache pin을 Exclusive하게 획득한 후 실행 계획(Execution Plan)을 생성한다. 2번 ~ 7번까지의 과정을 하드파싱(Hard Parsing)이라고 부른다. 하드파싱이 발생하면 오라클은 parse count (hard) 통계값을 증가시킨다. 만일 하드파싱 과정에서 SQL 문장의 오류가 발견되면(가령 존재하지 않는 객체 참조 등) parse count (hard) 통계값과 함께 parse count (failures) 통계값이 같이 증가한다.
  8. 오라클은 SQL 커서에 대해 library cache locklibrary cache pin을 Shared 모드로 획득하고 SQL문장을 실행한다. 이 과정을 실행(Execute) 단계라고 부른다. SQL 커서가 참조하는 LCO(테이블, 프로시저 등)에 대해서는 기본적으로 SQL 커서와 동일한 모드로 library cache locklibrary cache pin을 획득한다. 하지만 DDL 문장과 같이 객체 정보를 변경하는 작업을 수행하는 경우에는 해당 객체에 해당하는 LCO에 대해서 library cache lock과 library cache pin을 Exclusive 모드로 획득하기도 한다. 예를 들어 “alter table xxx add varchar2(10)”과 같은 SQL문장이 수행되는 경우 수행(Execute) 단계에서 SQL 커서 자체에 대해서는 library cache lock을 Shared 모드로 획득하지만, 테이블 xxx에 해당하는 LCO에 대해서는 library cache lock을 Exclusive 모드로 획득한다. library cache locklibrary cache pin을 획득하는 과정에서 경합이 발생하면 각각 library cache lock 이벤트와 library cache pin 이벤트를 대기한다.
  9. 오라클은 실행이 완료된 SQL 커서에 대해 데이터를 페치한다. 이 과정을 페치(Fetch) 단계라고 부른다. 페치 단계에서는 SQL 커서에 대해 library cache lock을 Null모드로 변환하고 library cache pin은 해제한다. 오라클 매뉴얼에서는 Null 모드로 획득한 library cache lockbreakable parse lock이라고 부른다.

파싱 단계에서 사용된 CPU 시간과 파싱을 수행하는데 걸린 시간은 parse time cpu 통계값과 parse time elapsed 통계값에 기록된다. 파싱을 수행하는 과정에서 래치나 락을 획득하기 위해 대기하는 시간이 길어지면 parse time elapsed 통계값이 parse time cpu 통계값에 비해 매우 크게 나타날 수 있다. 특히 동시에 여러 세션이 하드 파싱을 수행하는 경우에 parse time elapsed 통계값이 큰 폭으로 증가하는 현상이 나타나는데 이는 대부분 library cache 래치나 shared pool 래치를 획득하는 과정에서 경합이 발생해서 대기시간이 그만큼 증가하기 때문이다.

아무리 많은 프리 청크들이 존재한다고 하더라도 요청된 SQL을 담을 수 있는 최적 크기 이상의 프리 청크를 발견하지 못하면 오라클은 더 이상 작업을 진행하지 않는다는 점에 유의해야 한다. Shared Pool에 프리 메모리(free memory)가 충분히 있는데도 ORA-4031(unable to allocate %s bytes of shared memory) 에러가 발생하는 것은 이러한 이유 때문이다. 이러한 현상은 대부분 과도한 하드파싱에 의해 프리 청크들이 작은 크기로 쪼개져 있을때 발생한다. 이러한 Shared Pool 단편화(Fragmentation)는 shared pool 래치 경합에도 큰 영향을 준다. Shared Pool이 단편화되면 프리리스트가 많은 수의 프리 청크들을 포함하게 된다. 프리 청크를 할당받기 위해 프리리스트를 탐색하는 과정에서 shared pool 래치를 획득해야 하기 때문에, 프리 청크가 무수히 쪼개져 있으면 프리리스트 탐색시간이 증가하고 그 만큼 shared pool 래치를 보유하는 시간이 길어지기 때문이다.

ORA-4031 에러가 발생할 경우 응급조치로 alter system flush shared_pool 명령을 사용하게 되는데, Shared Pool을 플러시할 경우 연속된 프리 청크를 병합(Coalesce)하기 때문에 다음번 SQL 요청시 적당한 크기의 프리 청크가 발견될 가능성이 높아지기 때문이다. 하지만 Shared Pool을 플러시 하는 것이 ORA-4031 에러의 해결책이라는 의미는 아니며, 플러시에 의해 또 다른 성능 문제가 유발될 수 있음을 알아야 한다.

library cache 래치는 파싱이 발생할 때마다 획득되어야 하기 때문에 특히 경합이 발생할 확률이 매우 높다. library cache 래치 경합은 시스템의 성능과 직접적인 관련이 있기 때문에 오라클은 library cache 래치의 경합을 줄이기 위해 다양한 방법을 제공한다. 첫째, PL/SQL 블록 내에서 반복적으로 실행되는 SQL 커서에 대해서는 최초 실행시에만 파싱이 이루어지며 이후에는 소프트파싱없이 실행이 가능하다. 소프트파싱이 발생하지 않으므로 library cache 래치도 획득하지 않는다. 오라클은 SQL 커서에 해당하는 LCO를 핀(Pin)함으로써 메모리에 고정시키기 때문에 library cache 래치를 획득하지 않고도 LCO를 참조하는 것이 가능하다. 둘째, 세션 내부에 LCO의 위치를 캐싱하는 기능을 제공한다. 이것을 세션 커서 캐싱(Session Cursor Caching)이라고 부른다. 오라클은 한 세션 내에서 세 번 이상 수행된 SQL 커서들의 위치(즉, Library Cache 내에서의 위치)와 SQL 텍스트를 PGA 영역에 저장한다. 저장하는 SQL 커서의 개수는 SESSION_CACHED_CURSORS 파라미터에 의해 결정된다. 이 기능을 사용할 경우 비록 소프트 파싱은 여전히 발생하고 library cache 래치를 획득해야 한다는 사실에는 변함이 없지만, Library Cache 메모리를 구조를 탐색하지 않고 바로 LCO의 위치를 찾아가게 되므로 library cache 래치를 보유하는 시간이 줄어든다. 그만큼 library cache 래치에 의한 경합도 줄어든다.

[편집] Analysis Case

[편집] 1. OS Free Memory 고갈로 인한 성능 저하 현상 분석

오라클에 접속한 프로세스들은 SQL 수행을 위한 파싱 시에 SGA 메모리 영역 중 Shared Pool 영역을 사용하게 된다. 만일 Shared Pool 영역이 OS Free Memory 부족으로 인해 Swap Out 될 경우에는 심각한 성능 저하 현상이 발생하게 된다. OS Free Memory 고갈에 의해 발생되는 성능 저하 현상을 Oracle DBMS의 성능진단/분석 툴인 MaxGauge(맥스게이지)를 활용하여 분석해보기로 한다.

[편집] 성능저하구간의 확인

성능문제가 발생한 인스턴스에서 수집된 가동이력로그로부터 일간 추이그래프를 확인해 보면, 12시13분부터 12시25분 사이에「CPU사용률」,「Active Session」및 「Wait Events」가 연동하여 급증하는 것을 쉽게 확인할 수 있다.

■ CPU사용률의 추이그래프 그림:Case6_1.jpg

■ Active Session수의 추이그래프 그림:Case6_2.jpg

■ Wait Events의 추이그래프(Wait Time) 그림:Case6_3.jpg

[편집] 성능저하구간 Zoom Up

문제 구간을 편리하게 확인하기 위해 12시00분~13시00분의 데이터를 확인해보자.

■ Active Sessions 추이와 「Wait」 추이 비교 그림:Case6_4.jpg

■ Active Sessions 추이와 「CPU」 추이 비교 그림:Case6_5.jpg

Zoom Up을 하여 확인해본 결과, Active Sessions 추이는 「Wait」 추이와는 일치하지만, Active Sessions 추이와 「CPU」 추이와는 약간 다른 것을 알 수 있다. 즉, CPU 사용률과 Active Session의 증가와는 부분적으로만 관련이 있다고 생각할 수 있다.

[편집] Wait Events의 검출 및 분석

Active Session의 급증으로 인한 성능저하(Performance Slow-Down)의 원인을 규명하기 위해, 문제시점의 Wait Events의 발생내용을 확인해 본다. 그림:Case6_6.jpg 「Value」탭에서 동 시점의 Top Wait Event를 확인한 결과, Idle Events (SQL*Net message from client)를 제외한 Top Wait Events는 latch: library cache latch 임을 알 수 있다.

「세션 Grid」 화면에서 동 시점의 세션들의 대기이벤트를 확인해 본다. 그림:Case6_7.jpg

확인 결과, 모든 세션들이 동일한 SQL (Select 문장)을 수행중임을 알 수 있으며, latch: library cache 이벤트를 대기하는 세션은 모두 동일한 address(504403159414602544)를 대기하고 있다. latch: library cache 이벤트의 원인 분석을 위해 파싱 및 SQL 실행과 관련된 성능 정보의 추이를 확인해보자.

[편집] 파싱 및 SQL 실행 관련 성능 정보 분석

파싱과 관련된 지표인 parse count(total), parse count(hard), parse time elapsed 와 SQL 실행과 관련된 지표인 execute count 의 추이를 확인해보자.

그림:Case6_8.jpg

확인 결과, 성능 저하 구간에 parse time elapsed 수치가 높게 나타나고 있으나, 이 구간에 parse count(total), parse count(hard), execute count의 수치는 다른 구간에 비해 그리 높은 것은 아니다. 즉, 파싱에 소요된 시간은 오래 걸렸으나, 이 시점에는 발생한 파싱은 그리 많지 않음을 알 수 있다. parse time elapsed 가 높은 것은 이전 구간에 수행된 하드 파싱에 의해 Shared Pool의 fragment가 많이 발생할 수도 있으므로, 24시간 동안 발생된 하드 파싱의 추이를 확인해보자.

그림:Case6_9.jpg

확인 결과, 성능 저하 구간 이전에도 하드 파싱이 과다하게 발생한 구간은 없음을 알 수 있다. 지금까지의 분석 결과 parse time elapsed 수치가 높은 것은 하드 파싱 및 SQL 실행 과다와는 관련이 없음을 알 수 있다. 이러한 경우, OS Free Memory 부족으로 인해 Shared Pool의 Swap Out으로 인한 경우가 있으므로 OS 성능 정보를 분석해보자.

[편집] OS 성능 정보 및 세션 분석을 통한 문제 원인 규명

「OS Stat」탭에서 IO Wait CPU, User CPU, Sys CPU, Free Memory(MB)의 추이를 확인해보자

그림:Case6_10.jpg

확인결과, 성능 저하 구간에 Free Memory가 고갈됨에 따라서 Swap Out 이 발생하였고, 그 결과 Active Session들이 급증하는 현상이 발생하고 있다. 즉, 메모리 고갈에 의해 Shared Pool이 Swap Out되고, 이로 인해 파싱 및 SQL 수행을 하려는 세션들은 Shared Pool의 정보를 얻기 위해 Swap Out된 메모리를 다시 로딩하는 작업이 필요하므로, latch: library cache 이벤트를 장시간 대기한 것이다.

해당 시점에 「세션 Grid」의 PGA(MB) 정보를 보면 메모리를 과다하게 사용하는 세션은 존재하지 않는다. 즉, 문제의 원인은 오라클 프로세스가 아닌 다른 프로세스들의 메모리 과다에 의한 것으로 추정할 수 있다. 그림:Case6_11.jpg

[편집] 오라클 파라미터 확인

[Parameter] 메뉴를 이용하여 오라클 파라미터를 확인해본 결과, LOCK_SGA 파라미터의 설정 값이 FALSE 이다. SunOS, Linux는 ISM (Intimate Shared Memory) 방식을 사용하므로 LOCK_SGA 값과 무관하게 Shared Memory가 Physical Memory에 Pin되지만, HP-UX, AIX 의 경우에는 LOCK_SGA=FALSE 인 경우 SGA가 Swap Out 될 수 있다. 따라서, 해당 파라미터를 TRUE로 설정하는 것이 권고 된다. 그림:Case6_12.jpg

[편집] 결 론

latch: library cache 대기이벤트의 급증에 의한 Active session의 급증 →

OS Free Memory 고갈로 인한 Shared Pool의 Swap Out으로 인해 문제 발생

[편집] 해결 방안

1. LOCK_SGA 파라미터의 설정 값을 TRUE로 설정

2. 실시간 모니터링을 통해 Memory 과다 사용 프로세스 검출



[편집] 2. 수행횟수 과다로 인한 latch: Library Cache 대기현상

문제가 발생한 인스턴스의 세션과 SQL의 수행 이력을 MaxGauge(맥스게이지) 로그데이터로 확인해 본 결과, 많은 세션들이 Latch: library cache 이벤트를 대기하고 있다. Latch를 대기함에 따라 각 세션의 CPU 사용률도 높게는 70% 이상 사용하고 있음을 확인할 수 있다.

그림:3_2_1.jpg

latch: library cache는 SQL의 Parsing과 관련이 있으며, latch: shared pool의 대기가 함께 발생하지 않으므로, Soft Parsing이 원인임을 유추할 수 있다. 즉, Soft Parsing과 관련이 있는 SQL의 수행 횟수를 확인해 보면, 세션의 SQL 수행횟수가 4000번 이상으로 과다하고 나타나고 있음을 알 수 있다.

[편집] 결 론

Library cache Latch의 원인은 과다한 파싱(높은 수행횟수)이므로, 파싱 횟수를 줄여야한다.

1) 한번 파싱에 여러 번 수행하도록 함 : Java 에서 PreparedStatement, PL/SQL문의 사용
2) Bind SQL의 사용
3) session_cached_cursors 파라미터의 조정


[편집] 3. 수행횟수 과다로 인한 파싱 지연 및 락 대기 현상

문제가 발생한 인스턴스의 로그를 살펴보면, 다수의 세션들이 락 경합으로 수행이 지연되고 있음을 확인할 수 있으며, 트리 구조로 홀더 세션과 대기 세션의 관계를 살펴보면 다음과 같다. 홀더 세션은 ges group parent latch 이벤트를 대기하고 있다. ges group parent latch는 shared pool에 위치한 enqueue 리소스를 보호하는 래치이다. 홀더 세션이 latch 이벤트를 대기 중이므로 작업을 진행하지 못해 락 대기 현상이 발생하고 있다.

그림:4_4_1.jpg

동일 시점에 락 대기세션 이외의 세션은 latch:library cache 이벤트를 대기하고 있다.

그림:4_4_2.jpg

오라클 내부적으로 library cache와 enqueue 리소스 구조체는 모두 shared pool에서 존재하므로, shared pool 에 접근하는 세션이 많아 리소스의 획득이 제대로 이루어지지 못함을 추측할 수 있다.

그림:4_4_3.jpg

위의 지표 추이를 확인해 보면

 1) latch free 대기가 먼저 발생 
 2) latch를 획득한 프로세스가 SQL문을 수행하여 execute count와 parse count가 증가함.
 3) enqueue 대기 발생함.

latch free 이벤트의 발생 후 parsing 횟수가 증가하였으므로, 파싱 과정에서 래치 경합이 발생함을 추측할 수 있으며, 이는 먼저 언급된 library cache latch 이벤트로 추측된다.

그림:4_4_4.jpg

원인으로 추측되는 SQL문의 문제시점 확인 결과, 특정 시간대에 1900회 이상 수행되고 있다. 총 응답시간인 elapsed(sum) 은 약 1700초이며, 이 중 cpu time은 21초, wait time은 1678초이다. 즉, SQL의 수행 시 대기 시간이 대부분을 차지 하며, 해당 대기는 library cache latch 이벤트이다.

library cache latch 이벤트를 해결하면, shared pool이 현재보다 잘 활용되어 락 홀더가 대기한 enqueue 구조체에 대한 래치는 발생하지 않을 것으로 기대된다.


[편집] 4. Function 사용으로 인한 수행횟수 증가 현상

Query에서 개발의 편의를 위해 만든 Function을 사용하는 경우가 있다. 이 경우에 발생한 성능저하 사례이다.

성능문제가 발생한 인스턴스에서 수집된 로그로부터 추이 그래프를 보면, Active Session과 CPU, Wait Events 의 그래프가 급증하고 있다.

그림:6_3_1.jpg

Wait Events 그래프의 추이는 Latch Free 이벤트와 일치한다.

그림:6_3_2.jpg

Active session List를 확인해 보면, 다수의 세션들이 latch free(library cache) 대기 이벤트를 겪고 있다. 대기하는 Active Session이 수행하고 있는 SQL문은 다음과 같다.

SELECT emp.empno ,
            emp.ename ,
            emp.hiredate , 
            emp.comm ,
            emp.sal ,
            emp.depno ,
            get_dept(&DEPT , ‘1’) 
FROM emp, salgrade
WHERE ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT )

get_dept 라는 Function을 사용하고 있으며, 이는 다음과 같은 소스로 구성되어 있다.

FUNCTION         get_dept  (dept_kind  comcod.code_kind%TYPE , dept_gubun varchar2)
return varchar2 as
   v_code_dname    comcod.code_kname%TYPE;
   v_code_loc   comcod.code_skname%TYPE;
   v_code_deptno    comcod.code_ename%TYPE;
begin
   select  dname , loc , deptno
   into    v_code_dname, v_code_loc, v_code_deptno
   from    dept
   where deptno=dept_kind;
--3개의 값을 가져 온후 구분 코드에 따라 1개의 값만 return함 
  if dept_gubun = '1' then
      return  v_code_dname;
  elsif dept_gubun = '2' then
      return  v_code_loc;
  elsif dept_gubun = '3' then
      return  v_code_deptno;
  else
      return ' ';
  end if;     
exception
   when    others  then
       v_code_dname :=  ' ';
       return  v_code_dname ;
end ;

위와 같이 Query에서 Function을 사용할 경우 많은 Parsing에 의해 Execute Count가 증가하게 된다.

단순히 하나의 결과를 조회하기 위해 Function을 실행한다면 Function을 다른 방법으로 대체하여 library cache latch를 획득하기 위한 Session의 대기를 줄이도록 해야 한다.

Function을 통해 가져오는 결과 값이 많지 않을 경우 해당 Function을 Scalar Sub-Query 나 Outer-Join Query로 변경하여 Function을 제거하여 Execute Count를 줄이는 방법을 권장한다.

[편집] 해결방안 1. Scalar Sub-Query로 변형
SELECT emp.empno ,
            emp.ename , 
            emp.hiredate , 
            emp.comm ,
            emp.sal ,
            emp.deptno ,
    --     get_dept(&DEPT , '1') 
           (select decode(&dept_gubun,'1',dname , '2',loc,'3',deptno) From dept where deptno=&DEPT)
FROM emp, salgrade
WHERE ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT );
[편집] 해결방안 2. outer-join Query로 변형
SELECT emp.empno ,
            emp.ename ,
            emp.hiredate , 
            emp.comm ,
            emp.sal ,
            emp.deptno ,
    --     get_dept(&DEPT , '1') 
            decode(&dept_gubun,'1',c.dname,'2',c.loc,'3',c.deptno)
FROM emp, salgrade,  (select * From dept ) c 
WHERE ( salgrade.grade = 2 )
AND    ( emp.sal between salgrade.losal and salgrade.hisal)
AND    ( emp.deptno = &DEPT )
AND    (c.deptno(+)=&DEPT);

Function이 단순히 하나의 결과를 가져오는 경우 위의 방법으로 대체하게 되면 Function을 수행하면서 겪게 되는 execute count를 줄일 수 있게 되며, 이를 통해 library cache latch를 대기하는 Session의 수를 줄일 수 있다.