글쓴이 : 좋은진호

조회수 : 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.ora file 내에 들어있는 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/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가 거의 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 디렉토리도 마찬가지로 확인합니다.