¿À¶óŬ SGA Æ©´× (¿À¶óŬ °í°´¼¾ÅÍ) | ÀÛ¼ºÀÏ : 2003/08/05 01:21 |
Á¶È¸¼ö : 8577 |
¿¹Àü¿¡ ¿À¶óŬ °í°´¼¾Å͸¦ ÅëÇؼ ¹Þ¾Ò´ø Æ©´× ±ÛÀÔ´Ï´Ù. 1. 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 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 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°¡ °ÅÀÇ 0¿¡ °¡±î¿ö¾ß ÇÕ´Ï´Ù. ¸¸¾à ÀÌ * prompt * ¼öÄ¡°¡ ¿¬¼ÓÀûÀ¸·Î Áõ°¡Çϸé log_bufferÀÇ size¸¦ Áõ°¡ ½ÃÄÑ¾ß * prompt * ÇÕ´Ï´Ù. Áõ°¡ ½Ã ¾à 5% ¾¿ Áõ°¡ÇÑ ÈÄ ´Ù½Ã monitoring. * prompt *************************************************************** spool off; 2. MTS·Î ¿î¿µ ÁßÀÎÁö È®ÀÎÇÏ´Â ¹æ¹ý cd $ORACLE_HOME/dbs/initORACLE_SID.ora ÈÀÏ ³»¿¡ ´ÙÀ½°ú °°Àº ÆĶó¹ÌÅÍ°¡ ÀÖ´ÂÁö È®ÀÎÇØ º¾´Ï´Ù. mts_dispatchers="tcp,3" mts_max_dispatchers=10 mts_servers=5 mts_max_servers=10 mts_service=ORA7 mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(port=1521) (host=hostname))" 3. alert.log, trace fileÀ» È®ÀÎÇÏ´Â ¹æ¹ý. oracle server process °ü·ÃÇؼ ¿¡·¯µéÀ» È®ÀÎÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀÔ´Ï´Ù. <ÂüÁ¶> SVRMGR>show parameter background_dump_dest; ¸¦ Ä¡¸é background processµéÀÌ »Ñ¸®´Â ¸Þ½ÃÁö¸¦ È®ÀÎÇÏ½Ç ¼ö ÀÖ½À´Ï´Ù. oracle 7.2 ÀÌÇÏ´Â default $ORACLE_HOME/rdbms/log ÀÌ°í oracle 7.3 ÀÌ»óÀº ORACLE_BASE/app/oracle/admin/ORACLE_SID/bdumpÀÔ´Ï´Ù. user_dump_dest, core_dump_dest µð·ºÅ丮µµ ¸¶Âù°¡Áö·Î È®ÀÎÇÕ´Ï´Ù. |
Ä¿ÇǴнº, ½Ã½ºÅÛ ¿£Áö´Ï¾îÀÇ ½°ÅÍ / URL : http://coffeenix.net/board_view.php?bd_code=10 |