Memory 튜닝

김기석

데이터베이스를 사용하다 보면 갑자기 작업속도가 떨어지는 경우가 있다. 그럴 때는 OS 리소스(디스크 I/O 및 free space, CPU, ..)도 체크해 보고, alert 파일에 에러가 기록되었는지도 확인 해보고 그래도 별 이상이 없는 것으로 나타나면 성능저하로 의심해 볼 수 있다.
여기선 여러가지 튜닝 방법 중 메모리 관련 파라미터를 이용한 튜닝을 소개한다.

오라클이 사용하는 메모리 영역 중 튜닝이 필요한 곳은 database buffer, shared sql pool, redo log buffer가 있다. 먼저, 현재의 속도와 튜닝 후의 속도의 비교를 위하여 자주 사용하는 SQL 문에 대하여 tkprof 를 이용한 trace file을 만들어 둔다.

1.DATABASE BUFFER

database buffer와 관련된 ORACLE parameter 는 db_block_buffers로서 메모리 내에 있는 데이타 베이스 블럭의 수를 나타낸다. 이 값을 크게 하면, 디스크의 입출력을 줄일 수 있지만 메모리를 낭비하게 된다. 따라서, 알맞은 값은 데이타베이스가 기동 되어 peak time 에 다음의 query 를 수행하여, ratio 필드의 평균값이 92 % 이상이면 된다(참고 : 다음은 ORACLE V7.1 이상부터 가능.)　　　　　

$sqlplus system/manager

SQL>select round(100*(a.value+b.value-c.value)/(a.value+b.value),2) ratio

from sys.v_$sysstat a, sys.v_$sysstat b, sys.v_$sysstat c
where a.statistic# = 37 and b.statistic# = 38 and c.statistic# = 39;　　　　　

이 ratio가 지나치게 낮으면, $ORACLE_HOME/dbs/init.ora 내부에 있는 db_block_buffers 를 조금씩 늘려가며 위의 query 를 수행하여 변화를 monitoring 한다.　　　　　　

2.SHARED POOL SIZE
공유 커서와 공유 SQL, 공유 procedure 등을 포함하고 있는 영역으로 library cache와 dictionary cache의 사용율을 조사하여 shared_pool_size 의 크기를 변경한다.　　　　　　

$sqlplus system/manager　　　　　

1)library cache

SQL>col "Library Cache Miss Ratio(%)" format a40

SQL>select to_char(sum(reloads)/sum(pins)*100,"990.99") "Library Cache Miss Ratio(%)" from v$librarycache;　　　　　

위의 값이 1 ~ 2 % 보다 작은 것이 좋다.　　　　　　

2)dictionary cache

SQL>col "Dic. Cache Miss Ratio (%)" format a40

SQL>select to_char(sum(getmisses)/sum(gets)*100,"990.99") "Dic. Cache Miss Ratio (%)" from v$rowcache;

위의 값은 10% 보다 작은 것이 좋다.　　　　　

library cache 와 dictionary cache 의 miss ratio 가 위의 값보다 클 경우,　 shared_pool_size 를 증가시킨다.　　　　　　

3)redo log buffer　　

redo log buffer 를 포함하고 있는 디스크에 입출력 병목이 발생한다면, log_buffers 를 증가시켜야 한다 (단, 1M 를 넘지 않도록 한다.) commit 수행 시 db_block_buffers 의 내용을 redo log buffer 로 복사하게 되는데, 이 때 내부적으로 latch 를 사용하게 된다. 다량의 update 또는 OLTP 업무에서는 다음의 SQL 문을 수행하여 latch waiting 이 얼마나 되는지 조회하여 관련된 parameter 를 변경한다.　　　

$sqlplus sys/xxxxxxxx

SQL>col　 "misses_gets_ratio" format 90.999

SQL>col "immediate misses_gets ratio" format 90.999

SQL>select ln.name, nvl(misses/decode(gets,0,1),0) "misses_gets_ratio",

nvl(immediate_misses/decode(immediate_gets,0,1),0)

"immediate misses_gets ratio"

from v$latch l, v$latchname ln

where ln.name in ("redo allocation","redo copy") and

ln.latch# = l.latch#
order by ln.name desc;　　　

위의 값이 1% 를 초과하면 latch contention 이 일어나고 있는 것이므로, 대용량의 online transaction이 발생하는 경우에는 log_simultaneous_copies 를 CPU 갯수의 2배 정도로 정의하고, log_small_entry_max_size 는 OLTP 의 경우에는 400 정도로 하고, batch job 의 경우에는 800 이상으로 설정한다.　　　　

한번의 튜닝으로 최적의 값을 얻어내기는 어려우므로 철저한 계획을 세운 후에 변경하고 여러 날에 걸쳐 monitoring 하여야 한다. 또한, 변경에 문제가 있을 경우 다시 이전으로 돌아갈 수 있도록 이전 값들을 잘 보관해야 한다.　

---

자료출처 : 오라클 RCnote

제공 : DB포탈사이트 DBguide.net