sort area 크기 조정

• 세션 레벨 혹은 시스템 레벨에서 각 세션에 할달될 수 있는 총 크기 조정 가능
• 크기 조정 1차 목표 : 디스크 소트가 발생하지 않게 함
• 크기 조정 2차 목표 : onepass 소트 처리

PGA 메모리 관리 방식의 선택

1. About Work Area
   1. 데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용되는 메모리 공간
   2. sort_area_size, hash_area_size, bitmap_merge_area_size,create_bitmap_area_size 파라미터를 통해 조정
   3. 9i부터 "Automatic PGA Memory Management" 기능 도입으로 사용자가 일일이 그 크기 조정 하지 않아도 됨
   4. pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용가능한 PGA 메모리 총량을 지정
      1. 오라클이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리 할당
      2. 이 파라미터의 설정 값은 인스턴스 기동 중에 자유롭게 늘리거나 줄일 수 있음
      3. 이 기능을 활성화하려면 "workarea_size_policy=auto"로 해야 함
   5. 9i부터 default "workarea_size_policy=auto" : *_area_size 파라미터는 모두 무시되며 오라클이 내부적으로 계산한 값 사용
   6. 수동 PGA 메모리 관리 : 주로 트랜잭션이 거의 없는 야간에 대량의 배치 job 수행 시 효과적
      1. 해당 경우에 "workarea_size_policy=auto"로 사용하면 프로세스 당 사용할 수 있는 최대 크기제한되므로 work area를 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸 수 없게 되고, 결국 수 GB의 여유 메모리가 있어도 충분히 메모리를 활용하지 못해 작업 시간이 오래 걸릴 수 있음
      2. "workarea_size_policy=manual"로 변경

자동 PGA 메모리 관리 방식 하에서 크기 결정 공식

1. workarea_size_policy = auto 모드에서 WORK AREA 크기
   1. 단일 프로세스가 사용할 수 있는 최대 work area 크기는 인스턴스 기동 시 오라클에 의해 내부적으로 결정
   2. _smm_max_size 파라미터 통해 확인 가능(value 단위 : KB)
   3. Work Area 크기 조회

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' ;

      NAME            VALUE(KB)
-------------- --------------------
_smm_max_size        15974

1. 1. 이 파라미터의 값을 결정하는 내부 계산식
      1. 9i ~ 10gR1 : _smm_max_size=least((pga_aggregate_target * 0.5), (_pga_max_size * 0.5))
         • DB관리자가 지정한 pga_aggrate_target의 5%와 _pga_max_size 파라미터(maximum size of the PGA memory for a single process. 단위는 byte)의 50% 중 작은 값으로 설정
      2. 10gR2
         • pga_aggregate_target <= 500MB : _smm_max_size = pga_aggregate_target * 0.2
         • 500MB < pga_aggregate_target <= 1000MB : _smm_max_size = 100MB
         • pga_aggregate_target > 1000MB : _smm_max_size = pga_aggregate_target * 0.1
         • _pga_max_size = _smm_max_size * 2
   2. 병렬 쿼리의 각 슬레이브 프로세스가 사용할 수 있는 work area 총량은 _smm_px_max_size 파라미터(KB)에 의해 제한 됨
   3. SGA : sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당
   4. PGA : 자동 PGA 메모리 관리 기능을 사용하더라도 pga_aggregate_target 크기 만큼의 메모리를 미리 할당하지 않음
   5. pga_aggregate_target 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당 받을 수 있는 work area의 총량을 제한하는 용도임

수동 PGA 메모리 관리 방식으로 변경 시 주의사항

1. workarea_size_policy=manual모드로 설정한 프로세스는 pga_aggregate_target 파라미터 제약 받지 않음
2. sort area와 hash area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼 페이징(paging)이 발생하면서 시스템 전체 성능 저하(페이징이 심하면 시스템 마비까지 가능)
3. *_area_size : 0 ~ 2147483647(2G - 1byte)
4. workarea_size_policy=manual
   1. 병렬 쿼리를 사용하면 각 병렬 슬레이블 별로 sort_area_size크기 만큼의 sort area 사용 가능
   2. sort order by나 해시 조인 등을 수행할 때는 사용자가 지정한 DOP(the drgree of parallelism)의 2배수만큼의 병렬 슬레이브가 떠서 작업 수행
   3. paralle1(t 64)의 경우 128개의 프로세스가 각각 최대 2GB의 sort area 사용
   4. manual 모드에서 병렬 degree를 크게 설정할 때는 sort_area_size와 hash_area_size를 반드시 확인
      (sort order by를 수행할 때 한쪽 서버 집합은 데이터 블록을 읽어 반대편 서버 집합에 분배하는 역학만 하므로 위 쿼리만으론 최대 64*2GB의 sort area가 필요)
   5. 병렬 쿼리 테스트
      1. 결과 : 시스템의 상태에 따라 작업을 수행하는 병렬 슬레이브의 수가 다를 수 있다.

1. 1. 1. 쿼리

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 2147483647;

SELECT /*+ full(t) parallel(t 64) */
       *
from t
ORDER  BY object_name;

PGA_AGGREGATE_TARGET 의 적정 크기

1. 오라클 권고
   1. OLTP : (Total Physical Memory * 80%) * 20%
   2. DSS : (Total Physical Memory * 80%) * 50%
2. 애플리케이션 특성에 따라 모니터링 결과를 바탕으로 세밀한 조정 필요
   1. 일반적인 목표 : Optimal 소트 방식으로 수행, 나머지(10%미만)만 onepass 소트 방식으로 수행
   2. 시스템에 multipass 소트가 종종 발생하는 것으로 측정되면 크기를 늘리거나 튜닝이 필요한 상태임

sort area 할당 및 해제

1. sort_area_size
   1. 8.0 이전 : 소트가 수행되는 시점에 sort_area_size 크기만큼의 메모리 미리 할당
   2. 8.0 이후 : db_block_size 크기에 해당하는 chunk단위로 필요한 만큼 조금씩 할당
      sort_area_size는 할당할 수 있는 최대 크기를 지정하는 파라미터로 바뀜
2. PGA
   1. 8i 이전 : PGA공간은 프로세스가 해제될 때까지 OS에 반환하지 않음
   2. 9i 이후 : 자동PGA 메모리 관리 방식 도입으로 프로세스가 더 이상 사용하지 않는 공간을 즉시 반환함으로써 다른 프로세스가 사용 가능
      (버그로 인해 PGA메모리가 반환되지 않는 경우가 종종 있음)
3. 실제 Sort Area 가 할당되고 해제 되는 과정 측정
   1. 최초 : 쿼리 수행 직전
   2. 수행도중 : 쿼리가 수행 중이지만 아직 결과가 출력되지 않은 상태(--> 값이 계속 변함)
   3. 완료 후 : 결과를 출력하기 시작했지만 데이터를 모두 fetch하지 않은 상태
   4. 커서를 닫은 후 : 정렬된 결과집합을 끝까지 fetch하거나 다른 쿼리를 수행함으로써 기존 커서를 닫은 직후
   5. 결과 : 수행도중과 완료 후에 UGA, PGA 크기가 max 값을 밑도는 이유 : 소트해야 할 총량이 할당받을 수 있는 sort area 최대치를 초과하기 때문.
      그 때마다 중간 결과집합(sort run)을 디스크에 저장하고 메모리에 반환했다가 필요한 만큼 다시 할당받음
   6. AUTO 모드로 설정한 프로세스는 이 파라미터의 제약을 받음
      1. PGA 및 UGA 크기 조회 쿼리

SELECT ROUND( MIN( decode( n.name , 'session pga memory' , s.value ) ) /1024 ) "PGA(KB)" ,
       ROUND( MIN( decode( n.name , 'session pga memory max' , s.value ) ) /1024 ) "PGA_MAX(KB)" ,
       ROUND( MIN( decode( n.name , 'session uga memory' , s.value ) ) /1024 ) "UGA(KB)" ,
       ROUND( MIN( decode( n.name , 'session uga memory max' , s.value ) ) /1024 ) "UGA_MAX(KB)"
FROM   v$statname n ,
       v$sesstat s
WHERE ( name LIKE '%uga%'
        OR   name LIKE '%pga%' )
AND    n.statistic# = s.statistic#
AND    s.sid = :sid

1. 1. 1. 자동 PGA 메모리 관리 방식으로 시스템 레벨에서 사용할 수 있는 총량 제한 - pga_aggregate_target = 24M

alter system set pga_aggregate_target = 24M;
System altered.
CREATE TABLE t_emp AS
SELECT *
FROM   emp ,
       (
        SELECT ROWNUM no
        FROM   dual
        CONNECT BY LEVEL <= 100000
       ) ;
Table created.

1. 1. 1. 정렬이 필요한 쿼리 수행

SELECT *
FROM   t_emp
ORDER  BY empno ;

1. 1. 1. 결과

1. 1. manual 모드로 설정한 프로세스는 이 파라미터의 제약을 받지 않음

alter session set workarea_size_policy = MANUAL;
alter session set sort_area_size = 52428800;
alter session set sort_area_retained_size = 52428800;

SELECT *
FROM   t_emp
ORDER  BY empno ;

1. 1. 1. 결과