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;