메뉴 건너뛰기

bysql.net

7. Sort Area 크기 조정

2011.06.16 05:09

오예스 조회 수:5964

07. Sort Area 크기 조정.
  Sort Area 크기 조정을 통한 튜닝의 핵심
   - 디스크 소트가 발생하지 않도록 하는 것이 1차 목표.
    - 1차 목표가 안될때는 Onepass 소트로 처리.                   

(1) PGA 메모리 관리 방식의 선택
    - 9i부터 '자동 PGA 메모리 관리'(Automatic PGA Memory Management) 기능이 도입.
   - DB관리자는 pga_aggargate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량만 지정.
    - 자동PGA 메모리기능 활성화 : 9i부터 workarea_size_policy가 기본 auto. auto 상태에서는 *_area_size 파라미터는 모두 무시.


    - auto모드에서 단일 프로세스가 사용할 수 있는 최대 Work Area크기는 인스턴스 기동 시 오라클에 의해 내부적으로 결정.  _summ_max_size 파라미터(단위는 KB)를 통해 확인 가능.

select a.ksppinm name, b.ksppstvl value
   from sys.x$ksppi a, sys.x$ksppcv b
 where a.indx = b.indx
     and a.ksppinm = '_smm_max_size'

   - 이 파라미터 값을 결정하는 내부 계산식은 버전에 따라 다르다.
     9i와 10gR1까지는 동일한 계산식 사용, 10gR2부터 pga_aggargate_target 용량에 따라 계산식이 바뀜.
   - auto 모드에서 병렬 쿼리의 각 슬레이브 프로세스가 사용할 수 있는 work_area 총량은 _smm_px_max_size 파라미터(kb)에 의해 제한

    - SGA는 sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당. 이와 대조적으로 PGA는

  자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggargate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다. 이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다.


    - 수동PGA 메모리기능 : 트랜잭션이 거의 없는 야간에 대량의 배치 JOB을  수행할때 수동 방식으로 변경.

장점 : 자동 PGA 메모리 관리 방식 하에서는 프로세스당 사용할 수 있는 최대 크기가 제한. 즉 Work Area를 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸수 없음. 결구  수 GB의 여유 메모리를 두고도 이를 충분히 활용하지 못해 작업 시간이 오래 걸릴수 있음. 이럴 때 workarea_size_policy 파라미터를 세션 레벨에서 manual로 변경하고, 필요한 만큼 Sort Area와 Hash Area 크기를 늘림으로써 성능을 향상시키고 궁극적으로 전체 작업 시간을 단축.
단점 :  manual 모드로 설정한 pga_aggargate_target 파라미터의 제약을 받지 않는다.
      따라서 manaual 모드로 설정한 많은 세션에서 Sort Area와 Hash Area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행 한다면  가용한 물리적 메모리가 고갈돼 페이징이 발생 하면서 시스템 전체의 성능을 크게 떨어뜨릴 수 있다. 따라서 manual 모드에서 병렬 Degree를 크게 설정할 대는 sort_area_size와 hash_area_size를 반드시 확인해야 한다.


                       

(2) PGA_AGGREGATE_TARGET의 적정 크기
* OLTP 시스템 : (Total Physical Memory * 80%) * 20%
* DSS시스템 : (Total Physical Memory * 80%) * 50%

10G 물리적 메모리
구분 os ORACLE
SGA PGA
OLTP 2G 6.4G 1.6G
DSS 2G 4G 4G




이것은 일반적인 권고사항일 뿐이며, 각 애플리케이션의 특성에 따라 세밀한 조정이 필요하다.
대부분(90% 이상, 순수 OLTP 시스템에서는 100%) Optimal 소트 방식으로 수행하고 나머지 일부(10% 미만) 만 Onepass 소트 방식으로 수행되는 것을 목표로 삼아야 한다.

(3) Sort Area 할당 및 해제.

오라클 8i 이전에는 프로세스를 위해 할당된 PGA 공간을 프로세스가 해제될 때가지 OS에 반환하지 않음. 9i에서 자동 PGA메모리 관리 방식이 도입되면서 부터는 프로세스가  더이상 사용하지 않는 공간을 즉각 반환.