Latch: shared pool

EXEM Knowledge Base

(Heap dump에서 넘어옴)
Jump to: navigation, 찾기

목차

[편집] 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)에 대한 프리리스트 정보를 관리한다.

위의 내용을 그림으로 표현하면 아래 그림과 같다.

그림:SharedPool힙구조도.jpg

힙 덤프(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.jpg

하드 파싱으로 인해 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)
Type=EVENT, Name=events in waitclass Other, Value=7(cs)
Type=EVENT, Name=library cache load lock, Value=3(cs)
Type=EVENT, Name=library cache pin, Value=3(cs)
Type=EVENT, Name=row cache lock, Value=1(cs)
Type=EVENT, Name=latch: row cache objects, Value=1(cs)
Type=EVENT, Name=latch: library cache pin, Value=0(cs)
Type=EVENT, Name=latch: library cache lock, Value=0(cs)
Type=EVENT, Name=latch: cache buffers chains, Value=0(cs)
Type=EVENT, Name=cursor: mutex S, Value=0(cs)
Type=EVENT, Name=enq: TX - row lock contention, Value=0(cs)
Type=EVENT, Name=buffer busy waits, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=3325096
Type=STATS, Name=session logical reads, Value=42162
Type=STATS, Name=execute count, Value=40570
Type=STATS, Name=parse count (total), Value=40268
Type=STATS, Name=parse count (hard), Value=40063
Type=STATS, Name=parse time elapsed, Value=7195
Type=STATS, Name=redo size, Value=5692
Type=STATS, Name=session cursor cache hits, Value=409
Type=STATS, Name=sorts (memory), Value=199
Type=STATS, Name=redo entries, Value=11
Type=STATS, Name=user commits, Value=4
Type=STATS, Name=physical reads, Value=0
Type=STATS, Name=sorts (disk), Value=0

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)
Type=EVENT, Name=library cache pin, Value=3(cs)
Type=EVENT, Name=cursor: mutex S, Value=0(cs)
Type=EVENT, Name=enq: TX - row lock contention, Value=0(cs)
Type=EVENT, Name=latch: library cache, Value=0(cs)
Type=EVENT, Name=buffer busy waits, Value=0(cs)
Type=EVENT, Name=row cache lock, Value=0(cs)
Type=STATS, Name=session pga memory max, Value=2800808
Type=STATS, Name=execute count, Value=40391
Type=STATS, Name=session cursor cache hits, Value=40263
Type=STATS, Name=parse count (total), Value=40199
Type=STATS, Name=redo size, Value=4932
Type=STATS, Name=session logical reads, Value=1655
Type=STATS, Name=parse time elapsed, Value=151
Type=STATS, Name=sorts (memory), Value=119
Type=STATS, Name=parse count (hard), Value=52
Type=STATS, Name=redo entries, Value=16
Type=STATS, Name=user commits, Value=4
Type=STATS, Name=physical reads, Value=0
Type=STATS, Name=sorts (disk), Value=0
Type=TIME, Name=parse time elapsed, Value=190(cs)
Type=TIME, Name=hard parse elapsed time, Value=23(cs)
Type=TIME, Name=sql execute elapsed time, Value=5(cs)
Type=TIME, Name=DB time, Value=2(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사용률의 추이그래프 그림:Case4_1.jpg

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

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

[편집] 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임이 확인 된다.

그림:Case4_5.jpg Active Session의 급증에 대한 latch free 대기이벤트의 연관성을 규정하기 위해, 대기이벤트와의 발생패턴을 비교해 본 결과, 「Active Session」의 발생 추이와 상당히 유사하고, 문제시점에 발생한 Wait Events(Wait Time)의 약 74%(전체 392.53초 중에서, 291.91초를 점유 함)를 차지하고 있는 점에서, Active Session의 급증은 latch free 대기이벤트의 급격한 발생과 연관이 있음을 추측할 수 있다.

그림:Case4_6.jpg

실제로, 같은 시점의 상세데이터를 표시하는 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 의 추이를 확인해 본다.

그림:Case4_7.jpg

추이그래프의 분석결과, 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」 프로그램임을 확인할 수가 있다. 그림:Case4_8.jpg

과다하게 하드 파싱을 수행하고 있는 위의 세션을 조사해 본 결과, 대부분이 유사한 SQL을 사용하고 있고, 바인드 변수를 사용하지 않은 「Literal SQL」임이 판명 된다. 그림:Case4_9.jpg

[편집] 결 론

Latch free대기이벤트의 급증에 의한 Active session의 급증 ☞

바인드 변수를 사용하지 않은 Literal SQL에 의한 Hard parsing의 과다한 수행

[편집] 해결방안

1. Literal SQL의 Bind 변수화

2. Prepared Statement의 사용을 통해 JDBC P/G내의 Literal SQL을 제거