꿈꾸는 개발자, DBA 커뮤니티 구루비

PGA_AGGREGATE_TARGET(PAT)

정의 : Oracle 9i부터 제공된 파라미터. 하나의 인스턴스에서 구동되는 모든 서버 프로세스(Server Process)가 사용하는 PGA 메모리의 합계 크기에 대한 목표치(Target)를 설정하는 파라미터
특징
1. 사용자가 Sort Area Size와 같은 Workarea Size를 개별 지정하지 않아도 오라클이 목표치를 기준으로 각 프로세스마다 적합한 크기의 PGA를 할당
2. 이 값이 설정되면 WORKAREA_SIZE_POLICY 파라미터 값은 AUTO로 세팅된 것으로 간주
     (SORT_AREA_SIZE, HASH_AREA_SIZE와 같은 Workarea Size를 결정하는 파라미터 값들은 무시)
3. 최대 설정 가능 사이즈가 아니고 한 세션에서 가질 수 있는 값을 할당하기 위한 지표로 사용 됨
pga_aggregate_target와 다른 파라미터들 간의 관계


예제】 
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=1G;
pga_aggregate_target = 1073741824 = 1G
_smm_max_size = 104850 = 100M
_smm_px_max_size = 524288 = 500M
_pga_max_size = 214732800 = 200M

-# _SMM_MAX_SIZE : 하나의 서버 프로세스가 사용 가능한 최대 Wokrarea 크기.
  위의 예를 보면, PAT 값을 1G로 지정하더라도 실제 하나의 서버 프로세스가 사용 가능한 최대 Workarea는 100M로 제한
-# _SMM_PX_MAX_SIZE : 하나의 병렬 실행에 속한 병렬 슬레이브들이 사용 가능한 최대 Workarea 크기
  위의 예를 보면, 모든 병렬 슬레이들이 사용 가능한 Workarea 영역의 최대 크기를 500M로 제한
-# _PGA_MAX_SIZE : 하나의 서버 프로세스가 사용 가능한 최대 PGA 크기
  PGA는 Wokarea외에 Session 정보, Cursor 정보 등의 정보를 포함

Hash/Sort 작업과의 관계
1. PGA_AGGREGATE_TARGET 파라미터에 의해 지정된 Workarea의 크기는 Hash 작업과 Sort 작업의 성능을 결정하는 중요한 값
2. 사용 가능한 Workarea의 크기에 따라 Hash/Sort 작업이 Optimal, Onepass 또는 Multipass로 실행
    실행 방법에 따라 Hash/Sort 작업의 성능이 크게 달라짐
3. Hash/Sort 작업 : PGA_AGGREGATE_TARGET 값을 이용해 Workarea의 크기를 증가 시키고 가능한 Optimal이나 Onepass 방법으로 실행
매우 큰 Sort Area가 필요할 경우 : 이 경우 큰 크기의 Worarea가 필요함
1. PGA_AGGREGATE_TARGET을 사용하는 경우 각 서버 프로세스가 사용 가능한 최대 Workarea의 크기가 오라클에 의해 일정한 크기로 제한
2. 오라클에 의해 지정된 크기 이상의 Sort Area가 필요한 경우 : 해당 세션에 대해서 WORKAREA_SIZE_POLICY 값을 Manual로 전환 후 SORT_AREA_SIZE 조정


예제 】
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 2000000;

PGA 개념 및 특징
1. 정의 : PGA(작업공간. Work Area) : 정렬, 해시 조인(Hash Join), 비트맵(Bitmap) 연산 등의 수행을 위한 메모리 영역
2. 특징
  1. 8i까지 : SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE,
    CREATE_BITMAP_AREA_SIZE와 같은 파라미터를 이용해서 관리자가 직접 개별 작업공간의 크기를 조정
  2. 9i 이후 : PGA_AGGREGATE_TARGET 파라미터를 이용하면 이러한 작업 공간의 크기 동적 관리 가능. 세션 별로 PGA_AGGREGATE_TARGET의 값을 골고루 분배
  3. Multi pass sort가 발생하는 경우에는 정렬작업 중간에 정렬결과를 정렬 세그먼트(Sort Segment)영역에 읽고 쓰는 작업이 발생하게 되고, 이로 인해 direct path read temp, direct path write temp 대기가 발생
    {warning:title=Warning}
    1. 해당 대기가 광범위하게 발생 시 : PGA_AGGREGATE_TARGET의 값을 적절히 늘려줌으로써 문제 해결
    2. 주의점 : 서버 프로세스가 실제 사용 가능한 메모리의 크기에 제한 있음
    {warning}
    - Optimal pass sort : 모든 작업이 메모리상에서 이루어지는 경우
    - One pass sort : 프로세스에 할당된 작업공간의 크기 내에서 디스크 상에서 한번에 정렬이 이루어지는 것
    - Multi pass sort : One pass sort 의 반대 경우.
  4. PGA_AGGREGATE_TARGET에 지정된 값의 일부분만을 개별 세션이 사용 가능
    {info}
    i) PGA_AGGREGATE_TARGET에 지정된 값에 따라 개별 프로세스에 할당 가능한 최대 메모리영역을 계산하는데, 그 값은 히든 파라미터인 _SMM_MAX_SIZE에 저장되며 단위는 Kbyte.
    ii) _SMM_PX_MAX_SIZE : Parallel Query 수행 시 전체 슬레이브 세션들이 사용 가능한 최대 메모리 크기
      Cf) V$SESSTAT : "session pga memory max" 값을 조회하면 세션이 실제로 사용한 최대 메모리 영역 조회 가능
    {info}
  5. PGA_AGGREGATE_TARGET을 그대로 사용하면서 특정 세션에 대해서만 작업 공간의 크기를 크게 해 줄 경우 : 해당 세션의 PGA 관리정책만 변경
    - "alter session set workarea_size_policy = manual" 로 변경 → "alter session set sort_area_size = 필요한 크기" (오라클 튜닝 가이드 권고 사항)
  6. 크기 설정 : OS 레벨에서 20% 정도의 메모리를 사용한다고 가정
    {info}
    i) OLTP : PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
      ☞ OLTP 시스템의 경우에는 여유메모리의 20% 정도
    ii) DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
      ☞ DSS 시스템은 정렬작업이 많으므로 50% 정도를 사용
    iii) 메모리용량 산정이 기본 정책으로 활용 가능하나, 절대적이지 않고, 불필요하게 많은 메모리를 할당하는 것은 바람직하지 않음
    {info}
  7. 물리적인 메모리 크기를 초과하게끔 PGA 메모리영역의 크기를 할당 시 발생되는 문제점 : 페이지 아웃/페이지 인이 발생할 확률 높아짐(페이징 현상이 자주 발생하면 시스템 성능이 크게 저하되는 현상 발생)
  8. PGA_AGGREGATE_TARGET 값 적절하게 설정 : direct path I/O가 사라지고 이로 인해 direct path read temp, direct path write temp 대기현상이 완전히 사라지고, 성능도 크게 개선됨
출처
1. http://wiki.ex-em.com/index.php/Direct_path_read/write_temp
2. http://wiki.ex-em.com/index.php/PGA_AGGREGATE_TARGET

문서에 대하여

최초작성자 : 박혜은
최초작성일 : 2009년 11월 19일
이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

PGA_AGGREGATE_TARGET (by joang77) [2009.11.20]

PGA_AGGREGATE_TARGET(PAT)

문서에 대하여