No. 11454
SGA TUNING SCRIPT
=================
shared pool size, block buffers, redo log buffer로 구성되는 SGA는 시스템
성능에 많은 영향을 미친다.
다음 script는 SGA 부분의 tuning을 위한 script이며, 각각의 조회 결과에 따른
조치 사항에 대한 Guide도 나타나도록 하고 있다.
여기에서 나타나는 shared_pool_size, db_block_buffers, log_buffer 등은
$OARCLE_HOME/dbs/init<SID>.ora 화일 내에 들어있는 initial parameter들이다.
이 script는 sqlplus system/manager 상에서 수행하도록 한다.
set feedback off
clear columns;
spool sysstat.out
prompt *******************************************************
prompt * *
prompt * LIBRARY CACHE TUNING *
prompt * *
prompt *******************************************************
prompt
select to_char(trunc(sum(reloads)/sum(pins)*100, 5),99.99999)||'%
(less than 1%)' "Library Cache MISS RATIO"
from v$librarycache;
prompt **************************************************************
prompt * libary cache miss ratio is good if it is less than 1 -2 % *
prompt * ratio를 늘리려면 shared_pool_size의 값을 증가 시켜야 한다 *
prompt * shared_pool_size의 값이 너무 적으면 SQL의 공유율이 너무 *
prompt * 떨어져서 performance에 지장을 준다. 하지만 SGA의 크기가 *
prompt * O/S의 memory의 50%가 넘지 않도록 권장한다.(예외 상황 있음) *
prompt * *
prompt ***************************************************************
pause ... please press enter key ....
prompt ***************************************************************
prompt * *
prompt * DICTIONARY CACHE TUNING *
prompt * *
prompt ***************************************************************
prompt
select trunc(sum(getmisses)/sum(gets)*100, 5)||'% (less than 9.8%)'
"Data dictionary miss ratio "
from v$rowcache;
prompt ****************************************************************
prompt * *
prompt * data dictionary miss ratio is good if it is less than 9.8% *
prompt * ratio를 늘리려면 shared_pool_size의 값을 증가 시켜야 한다. *
prompt * *
prompt ****************************************************************
pause ... please press enter key ....
rem 계산의 편의를 위한 temporary table 생성
create table buffer_cache
( aa number(10),
bb number(10),
cc number(10)
);
insert into buffer_cache (aa) select value from v$sysstat where name =
'db block gets';
update buffer_cache set bb = (select value from v$sysstat where name =
'consistent gets');
update buffer_cache set cc = (select value from v$sysstat where name =
'physical reads');
prompt *******************************************************
prompt * *
prompt * BUFFER CACHE TUNING *
prompt * *
prompt *******************************************************
select trunc((1 - (cc/(aa+bb)))*100, 5)||'% (more than 60-70%)'
"Buffer Cache hit ratio"
from buffer_cache;
prompt ****************************************************************
prompt * *
prompt * buffer_cache hit ratio is good if it is more than 70% *
prompt * *
prompt * ratio를 늘리려면 parameter file의 db_buffer_blocks의 값을 *
prompt * 증가 시켜야 한다.(90% 이상을 유지하는 것이 좋다.) *
prompt ****************************************************************
pause ... please press enter key ....
prompt ************************************************************
prompt * *
prompt * INTERNAL SORT AND EXTERNAL SORT *
prompt * *
prompt ************************************************************
prompt
select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');
prompt **************************************************************
prompt * *
prompt * MEMORY SORT에 비해 DISK SORT가 상대적으로 많으면 *
prompt * SORT_AREA_SIZE, sort_area_retained_size 의 크기를 *
prompt * 늘려 주어야 한다. *
prompt * 일반적인 application에서는 memory:disk == 1000:1 정도 *
prompt * sort_area_size : sort_area_retained_size == 2 : 1 정도 *
prompt * 증가시킬 때에 O/S의 memory도 고려를 해서 증가를 시켜야 한다. *
prompt * *
prompt **************************************************************
rem 1M 이내의 sort : 이 작업은 sort_area_retained_size보다 적으므로 memory
rem 에서 작업을 하고 memory에 보관한다.
rem 1M~10M의 sort : 이 작업은 sort_area_size를 넘지 않았으므로 메모리 내에서
rem 정렬 처리를 하지만 sort_area_retained_size를 넘었으므로
rem 정렬한 결과를 즉시 디스크에 쓰고 메모리에서 지워 버린다.
rem 10M 이상의sort : 이 작업은 sort_area_size, sort_area_retained_size를 모두
rem 넘었으므로 sort_area_size 만큼만을 정렬하여 디스크에 쓴다.
rem 계속해서 나머지를 정렬시킨다. 하지만 그 결과는 아직 완전
rem 하게 정렬된 것이 아니므로 몇 번에 걸쳐서 정렬 작업을 한다.
rem 추가 정보: 만약 memory와 temp space가 많으며, 대량의 sort를 많이 하는
rem system 이면 init parameter중에 sort_direct_writes를 true로 setting해라.
rem 이와 같이 하면, buffer cache를 사용하지 않고 Disk로 바로 쓰기 위해
rem memory에 여러개 (rem sort_write_buffer)의 커다란(sort_write_buffer_size)
rem memory를 allocation한다.
rem 그러므로, 필요한 memory는 sort_write_buffers*sort_write_buffer_size의
rem 크기 만큼이다.
pause ... please press enter key ....
prompt
prompt *******************************************************
prompt * *
prompt * ROLLBACK SEGMENT'S WAIT RATIO *
prompt * *
prompt *******************************************************
prompt
column 'rollback segment name' format a25
column miss_ratio format a10
column rssize format 99,999,999
column writes format 99,999,999
column xacts format 999
column status format a8
select name "rollback segment name",
rssize, writes, xacts, status,
trunc(waits/gets*100, 5)||' %' miss_ratio
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
order by waits/gets desc;
prompt ***************************************************************
prompt * *
prompt * *
prompt * MISS RATIO가 1~2% 이하 이어야 한다. *
prompt * 1~2%보다 크면 ROLLBACK SEGMENT의 갯수를 늘려주어야 한다. *
prompt * *
prompt * N : Number of Concurrent Transaction *
prompt * N < 16 --> 4 rbs , 16<=N<=32 --> 8 rbs *
prompt * 32<N --> N/4 rbs , But no more than 50 *
prompt * *
prompt ***************************************************************
pause ... please press enter key ....
prompt
prompt *******************************************************
prompt * *
prompt * REDO LOG FILE'S WAIT RATIO *
prompt * *
prompt *******************************************************
select value "Redo log request"
from v$sysstat
where name = 'redo log space requests';
prompt ***************************************************************
prompt * Redo log space requests가 작을 수록 좋습니다. 이 값은 비율이 *
prompt * 아닌 실제 wait의 횟수를 나타내므로 db open하고 transactin이 *
prompt * 많아 request 자체가 많았다면 실제 값은 크더라도 비율상으로는 *
prompt * 적당할 수 있습니다. 만약 이 수치가 연속적으로 증가하면 *
prompt * log_buffer의 size를 증가 시켜야 합니다. 증가 시 약 5% 씩 *
prompt * 증가한 후 다시 monitoring. *
prompt ***************************************************************
spool off;
