커피닉스, 시스템 엔지니어의 쉼터 커피향이 나는 *NIX
커피닉스
시스템/네트웍/보안을 다루는 곳
* HanIRC의 #coffeenix 방
[ 장비 및 회선 후원 ]
HOME > 데이터베이스(database) > DB 튜닝 도움말
검색 : 사이트 WHOIS 웹서버 종류


  오라클 SGA 튜닝 (오라클 고객센터) 작성일 : 2003/08/05 01:21
 
  • 글쓴이 : 좋은진호
  • 조회수 : 7088
          [ 이전화면 / 수정 ]   비밀번호 :     인쇄용 화면
      예전에 오라클 고객센터를 통해서 받았던 튜닝 글입니다.

    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 디렉토리도 마찬가지로 확인합니다.
      커피닉스 카페 최근 글
    [02/24] базовая процентная
    [10/20] Cross Compiler 깔
    [07/14] SSL АО
    [04/26] Re: 도스화면 원격조종 여부
    [04/25] 도스화면 원격조종 여부
    [10/30] Cshell에서 난수 설정
    [10/23] 공항철도주식회사 SE 구인 件
    [01/26] Re: wget으로 다른서버에있는 디렉토리를 가져오려고합니다.
    [01/25] wget으로 다른서버에있는 디렉토리를 가져오려고합니다.
    [01/11] 특정 안드로이드 WebView 버전에서 SSL 문제 (WebView 버그)
    [08/01] DNS forwarder (전달자) 서버를 통해서 쿼리하면 역방향을 받아오질 못합니다.
    [05/16] (주)후이즈 시스템엔지니어 (경력자) 모집
    [02/15] [AWS] Cloudfront edge 확인하기
    [01/20] Mobile Service/eCommerce 기업에서 Server / Java / PHP 개발자 구인
    [01/11] 탄탄한 퍼블리싱 모바일기업에서 Mobile 개발자를 모십니다.
      New!   최근에 등록한 페이지
      KiCad EDA Suite project (Free/Libre/Open-Source EDA Suite) (CAD)
      오픈캐스케이드 캐드 (OpenCASCADE CAD)
      QCad for Windows --- GNU GPL (Free Software)
      The Hello World Collection
      IPMI를 활용한 리눅스 서버관리
      DNS 설정 검사
      nagiosgraph 설치 방법
      Slony-I 설치 방법 (postgresql replication tool)
      Qmail기반의 Anti spam 시스템 구축하기
      clusterssh

    [ 함께하는 사이트 ]




    운영진 : 좋은진호(truefeel), 야수(yasu), 범냉이, sCag
    2003년 8월 4일~