Latch: shared pool
EXEM Knowledge Base
목차 |
[편집] Basic Info
shared pool 래치는 Shared Pool 의 기본 메모리 구조인 힙을 보호하는 역할을 한다. 프리 청크를 찾기 위해 프리리스트를 탐색하고, 적절한 청크를 할당하고, 필요한 경우 프리 청크를 분할(Split)하는 일련의 작업들은 모두 shared pool 래치를 획득한 후에만 가능하다. shared pool 래치를 획득하는 과정에서 경합이 발생하면 latch: shared pool 이벤트를 대기한다.
힙과 관련된 일련의 작업들은 대부분 매우 빠른 시간 안에 완료되기 때문에 보통의 경우에는 shared pool 래치에서의 경합은 나타나지 않는다. 하지만 다음과 같은 사실들로 인해 latch: shared pool 대기가 증가할 수 있다.
- shared pool 래치는 기본적으로 전체 인스턴스에 하나만이 존재한다. 즉 하나의 Shared Pool에 하나의 shared pool 래치가 사용된다. 이것은 Shared Pool의 기본메모리 구조인 힙의 아키텍처에 기인하는 것이다. 따라서 동시에 여러 세션이 청크를 할당 받아야 하는 경우 하나의 shared pool 래치를 획득하기 위해 경쟁하기 때문에 경합이 발생하게 된다.
- 하드파싱이 심한 경우 청크를 쪼개는(Split) 현상이 자주 발생하고 이로 인해 프리리스트에 수많은 작은 크기의 프리 청크들이 붙는 현상이 발생한다. 이러한 현상을 흔히 Shared Pool의 단편화(fragmentation)라고 부른다. Shared Pool의 단편화로 인해 프리리스트를 탐색하는 시간이 길어지고 그 만큼 shared pool 래치를 보유하는 시간이 늘어나게 된다. Shared Pool 단편화는 shared pool 래치 경합을 발생시키는 근본적인 원인이다. ORA-4031 에러 또한 단편화로 인해 발생한다.
하드파싱이 발생하면 새로운 SQL 정보를 담을 청크를 할당 받기 위해 프리리스트를 탐색해야 한다. 이러한 일련의 행위 동안 오직 하나의 프로세스만이 shared pool 래치를 독점해야 하기 때문에 동시에 많은 세션이 하드파싱을 수행하는 경우 latch: shared pool 대기가 많이 발생하게 된다. 따라서 하드파싱이 과도하게 발생하는 시스템에서 shared pool 래치 대기를 줄이기 위해 Shared Pool의 크기를 늘려주는 것은 대단히 위험한 발상이라고 할 수 있다. Shared Pool의 크기가 커지면 그만큼 프리리스트의 개수와 프리리스트에서 관리해야 할 프리 청크의 개수도 증가한다. 따라서 프리리스트를 탐색하기 위해 shared pool 래치를 잡고 있어야 하는 시간이 늘어난다. 그와 비례해서 latch: shared pool 대기시간도 증가하게 된다
[편집] Parameter & Wait Time
[편집] Wait Parameters
latch free 대기이벤트와 동일하다.
[편집] Wait Time
latch free 대기이벤트와 동일하다.
[편집] Check Point & Solution
[편집] 서브풀의 사용
오라클 9i 이상부터는 Shared Pool을 여러개의 서브풀로 최대 7개까지 나누어서 관리할 수 있다. _KGHDSIDX_COUNT 히든 파라미터를 이용하면 서브풀의 개수를 관리할 수 있다. 오라클은 CPU 개수가 4 이상이고, Shared Pool의 크기가 250M 이상인 경우 _KGHDSIDX_COUNT의 값만큼 서브풀을 생성해서 Shared Pool을 관리한다. 서브풀은 그 자체가 독립적인 Shared Pool로 관리되며 독자적인 프리리스트(Freelist), LRU 리스트, shared pool 래치를 가진다. 따라서 Shared Pool의 크기가 큰 경우에는 서브풀로 쪼개서 관리함으로써 shared pool 래치 경합을 줄일 수 있다.
[편집] Shared Pool의 크기 감소
하드파싱에 의해 shared pool 래치 경합이 발생하는 경우 또 다른 해결책은 Shared Pool의 크기를 줄이는 것이다. Shared Pool의 크기가 줄어든 만큼 프리리스트에 딸린 프리 청크들의 개수도 감소하고 따라서 프리리스트 탐색에 소요되는 시간이 줄어들기 때문이다. 하지만 이 경우 ORA-4031 에러가 발생할 확률이 높아지며 Shared Pool에 상주할 수 있는 객체의 수가 줄어들어서 부가적인 하드파싱이 유발될 수 있다는 단점이 있다. 이 단점을 해소하기 위해서 dbms_shared_pool.keep 프로시저를 이용해서 자주 사용되는 SQL 커서나 패키지, 프로시저 등을 Shared Pool에 영구 상주시키는 방법을 사용할 수 있다. dbms_shared_pool.keep 을 이용해 지정된 객체들은 Shared Pool에 영구히 상주하게 되며, alter system flush shared_pool 명령으로도 내려가지 않는다. 요약하면, Shared Pool의 크기를 줄이고 동시에 dbms_shared_pool 패키지를 이용해 자주 사용되는 객체를 메모리에 상주시키는 것이 또 하나의 방법이 된다.
[편집] Cursor Sharing 기법 사용
Cursor Sharing 기법을 사용한다. Cursor Sharing이란 상수(Literal)를 사용한 SQL 문장을 자동으로 바인드 변수를 사용하게끔 치환해서 커서가 공유되도록 해주는 기능을 말한다. Cursor Sharing 기능은 기존의 Literal SQL을 바인드 변수로 변환할 시간적 여유가 없는 경우에만 사용하는 것이 바람직하다. 단, 이 기능을 사용할 경우 기존 SQL 문장의 실행계획이 변경되는 경우도 있으므로 적절한 테스트 후 적용하는 것이 바람직하다.
[편집] Event Tip
[편집] Shared Pool의 구조
Shared Pool은 SGA(System Global Area)를 이루는 구성요소 중 하나이다. 아래 명령을 이용해 SGA의 구성요소를 확인할 수 있다.
SQL> show sga
Total System Global Area 524288000 bytes Fixed Size 2007624 bytes Variable Size 142609848 bytes Database Buffers 373293056 bytes Redo Buffers 6377472 bytes
Shared Pool은 SGA 영역 중, Variable 영역에 속한다. Variable 영역은 Shared Pool + Java Pool + Large Pool + Streams Pool 등으로 이루어진다. Shared Pool은 다시 여러 종류의 메모리 영역으로 나누어진다. 대표적인 것들이 Library Cache와 Row Cache이다. Shared Pool이 어떤 메모리 영역으로 나누어 지는지는 V$SGASTAT 뷰를 아래와 같이 조회함으로써 확인가능하다.
SQL> select count(*) from v$sgastat where pool = 'shared pool';
COUNT(*)
----------
599 --> 총 599 개의 메모리 영역으로 나누어진다
-- 크기가 큰 순으로 20개를 조회해보면 아래와 같다. 각 메모리 영역의 크기는 동적으로 변하게 된다.
SQL> select * from (
select name, bytes from v$sgastat
where pool = 'shared pool'
order by bytes desc
) where rownum <= 20;
NAME BYTES
------------------------------ ----------
free memory 38515152
ASH buffers 8388608
sql area 8074192
row cache 7480368
library cache 5792120
kglsim hash table bkts 4194304
KCB Table Scan Buffer 3981120
KSFD SGA I/O b 3977064
CCursor 2586248
private strands 2396160
db_block_hash_buckets 2342912
PL/SQL MPCODE 2261672
KQR M PO 1860816
kks stbkt 1572864
event statistics per sess 1555840
KGLS heap 1485744
FileOpenBlock 1447104
PCursor 1339640
KTI-UNDO 1286064
PL/SQL DIANA 1282376
Shared Pool의 구성요소는 크게 다음과 같이 구분된다.
- Permanent Area : process, sessioin, segmented array(enqueue, transaction, ..) 등. 프로세스 목록, 세션 목록, Enqueue 목록, Transaction 목록 등의 자원들은 Shared Pool 의 영구 영역(Permanent Area)에 할당되며 인스턴스와 그 수명을 같이 한다.
- Library Cache : SQL문을 수행하는데 필요한 모든 객체(SQL, 테이블, 뷰, 프로시저 등)에 대한 정보를 관리한다.
- Row Cache : Dictionary Cache라고도 부르며, 오라클이 사용하는 딕셔너리 정보를 관리한다.
- Reserved Area : 예약영역. 동적인 메모리 할당을 위해 오라클은 예분의 예약 영역을 할당해둔다.
Shared Pool은 힙(Heap)이라고 불리는 메모리 관리 기법을 이용해 관리된다. Shared Pool의 힙으로부터 메모리를 할당 받고자 하는 모든 프로세스는 반드시 shared pool 래치를 획득해야 한다. 가령 하드파싱이 발생하는 경우 프로세스는 새로운 SQL문을 저장할 공간을 Shared Pool에서 할당 받아야 하며 반드시 shared pool 래치를 획득해야 한다. shared pool 래치는 기본적으로 전체 인스턴스에 하나만 존재하며, 필요한 메모리(청크)를 할당 받는 전체 과정 동안 보유해야 한다. 따라서 동시에 많은 프로세스가 Shared Pool 메모리를 사용하는 경우 래치를 획득하는 과정에서 경합이 발생하게 된다. shared pool 래치를 획득하는 과정에서 경합이 발생하면 latch: shared pool 이벤트를 대기한다.
오라클 9i 이상부터는 Shared Pool을 여러 개의 서브풀로 최대 7개까지 나누어서 관리할 수 있다. _KGHDSIDX_COUNT 히든 파라미터를 이용하면 서브풀의 개수를 관리할 수 있다. 오라클은 CPU 개수가 4 이상이고, Shared Pool의 크기가 250M 이상인 경우 _KGHDSIDX_COUNT의 값만큼 서브풀을 생성해서 Shared Pool을 관리한다. 서브풀은 그 자체가 독립적인 Shared Pool로 관리되며 독자적인 프리리스트(Freelist), LRU 리스트, shared pool 래치를 가진다. 따라서 Shared Pool의 크기가 큰 경우에는 서브풀로 나누어 관리함으로써 shared pool 래치 경합을 줄일 수 있다.
[편집] Heap과 heap dump
힙에 대한 기본적인 사실들을 정리하면 다음과 같다.
- Heap Manager(KGH, Kernel Generic Heap)에 의해 관리되며 오라클의 가장 기본적인 메모리 할당 방법이다. Heap Manager는 Memory Manager(KSM, Kernel Service Memory)가 제공하는 서비스를 이용하여 동적으로 메모리를 관리한다. Library Cache Manager(KGL, Kernel Generic Library Cache)은 Heap Manager를 이용하여 Library Cache를 관리한다.
- Shared Pool은 기본적으로 하나의 최상위 힙(Top-level heap)을 가지며, 최상위 힙은 다시 여러 개의 서브힙(Subheap)을 포함한다. 서브힙 또한 그 자신의 서브힙을 포함한다. 힙과 서브힙은 기본적으로 같은 구조를 지닌다. 힙의 관점에서 보면 힙에 속한 하나의 청크(Chunk)가 서브힙에 대한 포인터 역할을 한다. 오라클 9i 이후부터는 적절한 환경을 만족할때, 여러개의 최상위 힙을 가질 수 있다.
- 힙은 여러개의 익스텐트(Extent)를 링크드 리스트(Linked List) 형식으로 거느린다. 하나의 익스텐트는 물리적으로 하나의 그래뉼(Granule)을 사용한다. 그래뉼은 오라클 9i 이상에서 사용하는 연속된 물리적 메모리 덩어리 단위로 SGA 크기가 < 1G 이면 4M, SGA 크기 >= 1G 이면 16M 이다(10g 기준). 하나의 익스텐트는 여러개의 청크(Chunk)로 구성된다. 각 청크의 사용현황은 X$KSMSP 뷰에서 조회 가능하다. 서브힙의 경우에는 익스텐트의 크기는 가변적이다. 물리적인 관점에서 Shared Pool의 메모리는 그래뉼을 추가로 쌓는 형식으로 할당되기 때문에 힙이라는 용어를 사용한다.
- 청크의 상태는 크게 free(즉시 사용가능), recr(Recreatable. 재생성가능), freeabl(Freeable. Session이나 Call 구간동안만 필요한 객체를 담고 있는 상태), perm(Permanent. 영구. 재생성불가)으로 나뉜다. free 혹은 recr 상태인 청크들은 재사용이 가능하다.
- 힙은 프리리스트(Freelist)를 관리하는 버킷 테이블을 가진다. 각 버킷은 프리 청크(Free chunk)에 대한 프리리스트를 링크드 리스트 형식으로 거느린다. 하나의 힙에 총 255개의 버킷이 존재하며 버킷에 포함 가능한 프리 청크의 크기는 버킷 번호가 증가함에 따라 순차적으로 증가한다.
- 힙은 LRU 리스트를 가지고 있다. LRU 리스트는 Unpinned recreatable chunk, 즉 현재 사용중이지 않으면서 재생성 가능한 청크들의 리스트이다.
- 최상위 힙은 예약 프리리스트(Reserved freelist)라는 별도의 프리리스트를 관리한다. 예약 프리리스트는 크기가 큰 객체를 저장하는 Shared Pool 내의 예약영역(reserved part of shared pool)에 대한 프리리스트 정보를 관리한다.
위의 내용을 그림으로 표현하면 아래 그림과 같다.
힙 덤프(Heap Dump)를 이용하면 힙의 구조를 물리적으로 관찰할 수 있다.
SQL> alter session set events 'immediate trace name heapdump level 2';
위의 명령을 수행하면 User dump directory에 힙 영역에 대한 덤프 파일이 생긴다. 파일 내용을 살펴보면 다음과 같다.
****************************************************** HEAP DUMP heap name="sga heap" desc=c000000002e15030 <-- 최상위 Heap extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0 parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x210 EXTENT 0 addr=c000000096000000 <-- 익스텐트0 Chunk c000000096000058 sz= 2342824 free " " <-- 청크0 ... EXTENT 1 addr=c00000009ac00000 Chunk c00000009ac00058 sz= 48 R-freeable "reserved stoppe" ... Total heap size = 67107456 FREE LISTS: <-- 프리리스트 Bucket 0 size=32 <-- 버킷 Bucket 1 size=40 ... Bucket 7 size=88 Chunk c00000009dffffa8 sz= 88 free " " <-- 프리 청크 Bucket 8 size=96 ... Bucket 254 size=65560 <-- 최대 255개 버킷, Max(size of Bucket) = 64KByte Chunk c0000000963ddfe0 sz= 139296 free " " ... Total free space = 6571496 RESERVED FREE LISTS: <-- 예약 프리리스트 Reserved bucket 0 size=32 ... Reserved bucket 13 size=65560 Chunk c00000009ac00088 sz= 212808 R-free " " ... Total reserved free space = 3192120 UNPINNED RECREATABLE CHUNKS (lru first): <-- LRU 리스트 : 프리리스트가 소진되면 LRU 리스트를 사용한다. SEPARATOR Unpinned space = 0 rcr=0 trn=0 PERMANENT CHUNKS: <-- Permanent Chunk Chunk c00000009623c000 sz= 1712096 perm "perm " alo=1168888 ... Permanent space = 57342400 ******************************************************
위와 같이 힙 덤프를 수행해보면 힙의 구조에 대해 정확한 정보를 얻을 수 있다. 각 힙은 다시 서브힙 구조를 포함할 수 있다. 힙은 자신이 포함하는 특정 청크에 서브힙의 주소를 저장함으로써 서브힙의 위치를 관리한다. 힙 덤프에서 다음과 같이 "ds"(Heap Descriptor)값이 설정되어 있는 청크들이 서브힙의 위치를 저장하고 있는 청크이다.
Chunk c00000009a6f2800 sz=1112 freeable "CCursor" ds=c0000000993293e8
ds는 Heap Descriptor라는 의미이며 ds=c0000000993293e8 가 서브힙의 주소를 가리킨다. 아래 명령을 이용해서 해당 서브힙을 덤프할 수 있다.
SQL> alter session set events 'immediate trace name heapdump_addr addr 0xc0000000993293e8';
서브힙의 덤프 내용을 확인해보면 아래와 같이 힙 덤프와 거의 동일한 형식을 지님을 알 수 있다. 서브힙은 익스텐트 크기가 가변적이며 예약 프리리스트를 관리하지 않는다는 점을 제외하면 최상위 힙과 동일한 구조를 지닌다.
****************************************************** HEAP DUMP heap name="CCursor" desc=c0000000993293e8 extent sz=0x440 alt=32767 het=32 rec=9 flg=2 opc=0 ? 익스텐트 크기가 변동 parent=c000000002e15030 owner=c00000009a6f3110 nex=0000000000000000 xsz=0x440 EXTENT 0 addr=c00000009a6f2818 ? 익스텐트 Chunk c00000009a6f2828 sz= 504 perm "perm " alo=96 Chunk c00000009a6f2a20 sz= 384 free " " Chunk c00000009a6f2ba0 sz= 32 freeable "kksfbc:hash1 " Chunk c00000009a6f2bc0 sz= 152 freeable "kgltbtab " EXTENT 1 addr=c00000009a6f2c70 Chunk c00000009a6f2c80 sz= 1072 perm "perm " alo=1040 EXTENT 2 addr=c00000009a6f30e0 Chunk c00000009a6f30f0 sz= 776 perm "perm " alo=776 Chunk c00000009a6f33f8 sz= 240 perm "perm " alo=232 Chunk c00000009a6f34e8 sz= 32 free " " Total heap size = 3192 FREE LISTS: ? 프리리스트 Bucket 0 size=0 Chunk c00000009a6f34e8 sz= 32 free " " Chunk c00000009a6f2a20 sz= 384 free " " Total free space = 416 UNPINNED RECREATABLE CHUNKS (lru first): ? LRU 리스트 PERMANENT CHUNKS: ? Permanent Chunks Chunk c00000009a6f2828 sz= 504 perm "perm " alo=96 Chunk c00000009a6f33f8 sz= 240 perm "perm " alo=232 Chunk c00000009a6f2c80 sz= 1072 perm "perm " alo=1040 Chunk c00000009a6f30f0 sz= 776 perm "perm " alo=776 Permanent space = 2592 ******************************************************
[편집] Analysis Case
[편집] CASE1 : Cursor Sharing 기법에 의한 latch: shared pool 대기 감소
아래 그림은 하드 파싱이 과도하게 발생하는 어플리케이션을 모니터링한 결과이다.
하드 파싱으로 인해 latch: shared pool 이벤트 대기가 목격되는 것을 확인할 수 있다.
아래 결과는 V$SESSION_EVENT 뷰와 V$SESSTAT 뷰를 통해 관련된 대기 현상과 일량을 분석한 것으로 latch: shared pool 이벤트가 가장 보편적으로 발생하는 것을 확인할 수 있다. 또한 하드 파싱 회수가 전체 파싱 회수와 비슷할 정도로 하드 파싱이 과도하게 발생하는 것을 확인할 수 있다.
| 실행결과 | Type=EVENT, Name=latch: shared pool, Value=112(cs) Type=EVENT, Name=latch: library cache, Value=72(cs) |
Cursor Sharing 기법을 적용함으로써 하드 파싱을 피할 수 있다. 아래와 같은 SQL 문장을 통해 CURSOR_SHARING 파라미터 값을 FORCE로 변경하면, 모든 Literal SQL이 자동으로 매개변수 처리가 이루어진다.
ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=MEMORY;
아래 결과는 Cursor Sharing을 적용한 후의 성능 측정 결과이다. latch: shared pool 대기가 사라지고, 하드 파싱 회수가 크게 감소한 것을 확인할 수 있다.
| 실행결과 | Type=EVENT, Name=events in waitclass Other, Value=6(cs) Type=EVENT, Name=library cache load lock, Value=3(cs) |
[편집] CASE2 : Literal SQL의 Hard Parsing으로 인한 성능 저하 현상 분석
동시사용자가 많은OLTPOn-Line Transaction Process） 및 WEB환경에서, Literal SQL의 과다 사용은 성능상의 심각한 문제를 야기하는 경우가 많다. Oracle DBMS의 성능진단/분석 툴인 MaxGauge(맥스게이지)를 활용하여, Literal SQL의 과다한 사용에 의해 발생한 성능저하 문제의 원인을 규명해 보고자 한다.
[편집] 성능저하구간의 확인
성능문제가 발생한 인스턴스의 수집된 가동이력 로그로부터 일간 추이그래프를 확인해 보면, 「CPU사용률」에는 뚜렷한 변화가 없어 보이지만, 10시17분을 Peak로 「Active Session」 및 ｢Wait Events｣가 급증하는 것을 쉽게 확인할 수 있다.
■CPU사용률의 추이그래프
■Active Session수의 추이그래프
■Wait Events의 추이그래프（Wait Time）
[편집] Wait Events의 검출 및 분석
Active Session의 급증으로 인한 성능저하（Performance Slow-Down）의 원인을 규명하기 위해, 문제시점(10시17분)의 Wait Events의 발생내용을 확인해 본다. 그림:Case4 4.jpg ｢Stat/Wait｣에서 동 시점의 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)의 약 74%（전체 392.53초 중에서, 291.91초를 점유 함）를 차지하고 있는 점에서, Active Session의 급증은 latch free 대기이벤트의 급격한 발생과 연관이 있음을 추측할 수 있다.
실제로, 같은 시점의 상세데이터를 표시하는 Grid화면에서도, latch free 대기이벤트가 Top Wait Event이며, 그 중에서도 latch free(shared pool) 및 latch free(library cache) 대기 이벤트가 많이 발생해 있음을 확인할 수 있다.
[편집] Wait Event(Latch Free)발생원인의 조사
latch free 대기이벤트의 발생원인에는 여러 가지가 있으나, 일반적으로 latch free (shared pool) 과 latch free (library cache) 대기이벤트가 동시에 발생한 경우에는 하드 파싱이 그 원인일 경우가 많다. 그 내용을 확인하기 위해 파싱 관련지표인 parse count (hard), parse time cpu, parse time elapsed 의 추이를 확인해 본다.
추이그래프의 분석결과, Active Session이 급증한 시점에서 parse time elapsed의 값이 20,000 centi-sec / sec(= 200sec / sec)로 높아져 있고, 성능저하 현상이 발생하기 전 (09시26분)의 parse count (hard)의 값이 200회 /초 발생했던 사실을 확인할 수 있다. 이를 통해 전 시점의 과다한 하드 파싱으로 인한 일회성 SQL들에 의해 Shared Pool에 memory fragmentation이 발생하였고, 이로 인해 Library Cache에서의 SQL검색이나 신규 SQL의 등록 등을 수행할 시에 지연현상이 발생하고 있는 것으로 분석된다.
[편집] 세션 및 SQL의 분석을 통한 문제원인의 규명
Hard parsing의 수행이 많았던 09시00분 ~ 09시30분간에서, parse count (hard)의 값이 ｢1초에 10회 이상｣인 세션을 검색해 본 결과, ｢1초에 100회 이상 ｣하드 파싱을 수행한 세션은 대부분이 「JDBC Thin Client」 프로그램임을 확인할 수가 있다.
과다하게 하드 파싱을 수행하고 있는 위의 세션을 조사해 본 결과, 대부분이 유사한 SQL을 사용하고 있고, 바인드 변수를 사용하지 않은 ｢Literal SQL｣임이 판명 된다.
[편집] 결 론
Latch free대기이벤트의 급증에 의한 Active session의 급증 ☞
바인드 변수를 사용하지 않은 Literal SQL에 의한 Hard parsing의 과다한 수행
[편집] 해결방안
1. Literal SQL의 Bind 변수화
2. Prepared Statement의 사용을 통해 JDBC P/G내의 Literal SQL을 제거