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

시스템 메모리는 디스크의 데이터가 임시 저장되는 공간으로, 디스크 I/O를 줄여 시스템 성능을 향상시키는 역할을 한다.

오라클 데이터베이스는 이와 유사한 SGA(System Global Area)와 PGA(Program Global Area)라는 메모리 아키텍처를 사용한다. 이 두 가지 메모리 영역은 오라클 데이터베이스의 설정에 따라 자동으로 관리된다. 이러한 편리한 기능이 있음에도 때때로 성능에 악영향을 끼치기도 한다.

오라클 데이터베이스의 메모리 관리 팁을 한 사례에서 배워보자.

어느날 정상 운영되던 오라클 데이터베이스 서버가 CPU 자원을 풀(full)로 사용하면서 HANG 상태(특정 시점에서 더 이상 진행되지 않는 상태)가 된 적이 있다. 이에 필자는 데이터베이스를 재가동한 후 상세한 점검을 수행했다. 그 결과, 공유 풀(Shared Pool) 영역이 SGA의 2/3을 사용하면서 Literal SQL이 빈번히 수행됐음을 확인했다. Literal SQL이 SGA 영역에 어떠한 영향 미친 것일까?

오라클 데이터베이스의 메모리 자동 관리 기능은 버전에 따라 두 가지 방식이 있다. SGA와 PGA를 동적으로 관리하는 방법과 SGA, PGA별로 메모리를 동적으로 관리하는 방법 이 두 가지다.

우선 초기화 매개변수 파일의 내용을 수정해 오라클 데이터베이스가 자동으로 메모리를 관리하도록 설정한다. 관련 매개변수는 [표 1]을 참고하자.

[표 1] 자동 메모리 관리 설정 관련 매개변수

초기화 매개변수 이름	값	내용
MEMORY_TARGET	500M	전체 메모리 설정(SGA+PGA), 0(manual)
SGA_TARGET	300M	SGA에서 사용할 메모리 사이즈, 0(manual)
PGA_AGGREGATE_TARGET	100M	PGA에서 사용할 메모리 사이즈, 0(manual)
SHARED_POOL_SIZE	0	SHARED_POOL 영역 설정 - 0(자동), mM(manual)
LARGE_POOL_SIZE	0	LARGE_POOL 영역 설정 - 0(자동), mM(manual)
DB_CACHE_SIZE	0	Data Buffer CACHE 영역 설정 - 0(자동), mM(manual)
JAVA_POOL_SIZE	0	JAVA_POOL 영역 설정 - 0(자동), mM(manual)

공유 메모리를 자동으로 설정하고 Literal SQL을 수행했을 때 SGA 영역의 메모리에는 어떠한 변화가 있을까?

이를 확인하기 위해 먼저 초기 데이터베이스가 동작할 때의 SGA 현황과 동적으로 변경되는 컴포넌트에는 어떤 것들이 있는지 V$SGAINFO로 확인해 보자.

[리스트 1] 초기 메모리 상황 확인

SELECT * FROM v$SGAINFO;

NAME                                  BYTES  RESIZEABLE
-------------------------------- ----------  -----------
Fixed SGA Size                      1336232  No
Redo Buffers                        6336512  No
Buffer Cache Size                 121634816  Yes
Shared Pool Size                   88080384  Yes
Large Pool Size                     4194304  Yes
Java Pool Size                      4194304  Yes
Streams Pool Size                         0  Yes
Shared IO Pool Size                       0  Yes
Granule Size                        4194304  No
Maximum SGA Size                  313860096  No
Startup overhead in Shared Pool    58720256  No
Free SGA Memory Available         130023424

초기 메모리 설정값을 확인했다면 Literal SQL을 수행해 데이터베이스 내부적으로 하드파싱(Hard Parsing)이 발생되도록 한다. SGA 내부의 Buffer Cache Size, Shared Pool Size, Large Pool Size, Java Pool Size의 크기 변화는 GV$SGA_RESIZE_OPS 뷰에서 확인하면 된다

[리스트 2] SGA 내부 메모리 변화 확인

SELECT parameter, oper_type, initial_size, 
       target_size, start_time, end_time
  FROM GV$SGA_RESIZE_OPS;


PARAMETER           OPER_TYPE     INITIAL_SIZE TARGET_SIZE START_TIM END_TIME
------------------- ------------- ------------ ----------- --------- ---------
db_cache_size       INITIALIZING     121634816   117440512 04-SEP-14 04-SEP-14
db_cache_size       STATIC           121634816   117440512 04-SEP-14 04-SEP-14
db_cache_size       STATIC           125829120   121634816 04-SEP-14 04-SEP-14
db_cache_size       STATIC                   0           0 04-SEP-14 04-SEP-14
db_cache_size       STATIC                   0   125829120 04-SEP-14 04-SEP-14
db_cache_size       STATIC           130023424   125829120 04-SEP-14 04-SEP-14
........중략...
shared_pool_size    GROW              88080384    92274688 04-SEP-14 04-SEP-14
db_cache_size       SHRINK           100663296    96468992 04-SEP-14 04-SEP-14
shared_pool_size    GROW              92274688    96468992 04-SEP-14 04-SEP-14
db_cache_size       SHRINK            96468992    92274688 04-SEP-14 04-SEP-14
shared_pool_size    GROW              96468992   100663296 04-SEP-14 04-SEP-14
db_cache_size       SHRINK            92274688    83886080 04-SEP-14 04-SEP-14
shared_pool_size    GROW             100663296   104857600 04-SEP-14 04-SEP-14
db_cache_size       SHRINK            83886080    79691776 04-SEP-14 04-SEP-14
shared_pool_size    GROW             104857600   109051904 04-SEP-14 04-SEP-14
db_cache_size       SHRINK            79691776    75497472 04-SEP-14 04-SEP-14
shared_pool_size    GROW             109051904   113246208 04-SEP-14 04-SEP-14
shared_pool_size    GROW             113246208   117440512 04-SEP-14 04-SEP-14
shared_pool_size    GROW             109051904   113246208 04-SEP-14 04-SEP-14
shared_pool_size    GROW             113246208   117440512 04-SEP-14 04-SEP-14

이를 수행한 결과, Shared_pool_size의 초기 설정값이 880 80384에서 117440512로 증가했다. db_cache_size도 125829120에서 75497472로 줄었다. Shared pool 크기가 증가로 인한 성능 이슈에는 무엇이 있을까?

Literal SQL을 수행하는 동한 발생한 이벤트 내용을 AWR Report에서 확인하면, Literal SQL로 인해 latch: shared pool wait event가 발생했고, Shared Pool 크기 증가로 Library Cache를 탐색하는 시간과 latch 보유시간도 길어졌다.

[그림1] AWR Report

또한 Shared Pool 크기가 증가하자 데이터베이스 메모리 영역이 가상 메모리 영역인 물리 디스크(Swap)로 페이지 인/아웃되는 일이 발생했다.

이로 인해 성능 저하가 더욱 심해질 수 있다. 여기서 유의할 점은 spfile 매개변수 파일을 사용할 경우 공유 메모리 컴포넌트들의 resize 값이 기록되기 때문에 재기동 후에도 정지 전 상태의 메모리 크기가 설정값에 할당된다.

즉 동적으로 변경된 컴포넌트 메모리 크기는 spfile 매개변수 파일에 기억되기 때문에 Shared Pool 크기가 점차 증가하게 된다. 이로 인해 앞서 사례에서 데이터베이스가 HANG 상태에 빠진 것이다.

그렇다면 메모리를 어떻게 관리해야 할까?

그러므로 Spfile 매개변수 파일을 사용해야 할 경우 공유메모리 컴포넌트의 메모리 크기를 지정해야 한다. 또한 Pfile을 사용해 SGA 컴포넌트들의 메모리 크기가 수동(manual)으로 관리되도록 하고, LOCK_SGA 매개변수를 true로 설정해 가상 메모리로 페이징(Paging)되는 것을 막는 게 바람직하다.

하지만 LOCK_SGA 매개변수와 MEMORY_TARGET, SGA_TARGET 이 두 매개변수를 함께 사용할 수 없으므로 자동 메모리 관리를 사용할 수 없게 된다.

이처럼 안정장치를 마련할 수도 있지만, Shared Pool 크기가 증가하는 가장 근본적인 이유는 자동 메모리 관리보다는 Literal SQL이라는 점에 유의하자. 이 부분은 DBA가 처리할 수 있는 영역이 아니므로 개발자들도 이런 SQL은 바인드 변수를 활용해 개발하는 게 좋다.