oracle 윈도우 환경에서 메모리 설정하기

데이타베이스 사용자라면, 작업 속도가 갑자기 느려지는 현상을 경험하였을 것이다. 이 때에는 먼저, OS 리소스(디스크 I/O 및 free space, CPU 등)를 점검하고, alert.log 화일에 ORACLE 에러가 발생하지는 않았는지 확인하여 본다. 만약, 에러가 발견되지 않았다면, 정상적인 속도로 작업이 처리 되기 위하여 튜닝을 시도해 볼 수 있다. 다음은 dba 권한이 있는 사용자 만이 할 수 있다.　　　　　
튜닝은 OS 튜닝(OS parameter, 디스크 I/O), DBMS 튜닝, 응용 프로그램 튜닝의 순서대로 수행한다. OS 튜닝은 데이타 베이스 관리자의 권한만으로는 수행할 수 없다.　 참고로, 본문은 ORACLE 세미나 중에서 DBMS 튜닝 중 메인 메모리 튜닝만을 요약하였다.　　　　　　
　　　　　　
ORACLE이 사용하는 메모리 영역 중에 튜닝이 필요한 곳은 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 하여야 한다. 또한, 변경에 문제가 있을 경우 다시 이전으로 돌아갈 수 있도록 이전 값들을 잘 보관해야 한다.　

페이지 히스토리

2008-10-06 15:29 에 HwanyShine님이 마지막으로 수정