h1.07.Sort Area 크기 조정

• 세션 레벨에서 Sort Area 크기를 조정하거나, 시스템 레벨에서 각 세션에 할당될 수 있는 총 크기를 조정해야할 때가 있다.
  Sort Area 크기 조정을 통한 튜닝의 핵심은, 디스크 소트가 발생하지 않도록 하는 것을 1차 목표로 삼고 불가피할 때는 Onepass 소트로 처리되도록 하는 데에 있다.

h3.(1)PGA 메모리 관리 방식의 선택

• 데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용하는 메모리 공간을 'Work Area'라고 부르며
  sort_area_size, hash_area_size, bitmap_merge_area_size, create_bitmap_area_size 같은 파라미터를 통해 조정한다.
• 8i까지는 이들 Work Area의 기본 값을 관리자가 지정하고, 프로그램의 작업 내용과 필요한 크기에 따라 세션 레벨에서 이들 값을 직접 조정해야만 했다.
• 9i부터는 '자동 PGA 메모리 관리' 기능이 도입되었기 때문에 사용자가 일일이 그 크기를 조정하지 않아도 된다.
• DB 관리자는 pga_aggreagte_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량을 지정하기만 하면 된다.
  그러면 오라클이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리를 할당해 준다.
• 그리고 이 파라미터의 설정 값은 인스턴스 기동 중에 자유롭게 늘리거나 줄일 수 있다.
• 자동 PGA 메모리 관리 기능을 활성화하려면, workarea_size_policy를 auto로 설정하면 되는데, 9i부터 기본적으로 auto로 설정돼있다.
• 기본적으로 자동 PGA 메모리 관리 방식이 활성화되지만 시스템 또는 세션 레벨에서 '수동 PGA 메모리 관리' 방식으로 전환할 수 있다.
• 특히, 트랜잭션이 거의 없는 야간에 대량의 배치 job을 수행할 때는 수동 방식으로 변경하고 직접 크기를 조정하는 것이 효과적일 수 있다.
  왜냐하면 자동 PGA 메모리 관리 방식 하에서는 프로세스당 사용할 수 있는 최대 크기가 제한되기 때문이다.
  그럴 때 workarea_size_policy 파라미터를 세션 레벨에서 manual로 변경하고, 필요한 만큼(최대 2,147,483,647 바이트)
  Sort ARea와 Hash Area 크기를 늘림으로써 성능을 향상시키고, 궁극적으로 전체 작업시간을 크게 단축시킬 수 있다.

h3.(2)자동 PGA 메모리 관리 방식 하에서 크기 결정 공식

• auto 모드에서 단일 프로세스가 사용할 수 있는 최대 Work Area 크기는 인스턴스 기동 시 오라클에 의해 내부적으로 결정되며,
  _smm_max_size 파라미터를 통해 확인 가능하다.

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                                                                           
--------------------------------------------------------------------------------
_smm_max_size                                                                   
1048576                                                                         

• 이 파라미터 값을 경정하는 내부 계산식은 버전에 따라 다르다
  • 오라클 9i부터 10gR1까지는 아래 계산식을 따른다.
    • s_mm_max_size = least((pga_aggregate_target*0.05),(_pga_max_size*0.5))
  • 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에 의해 결정된다.
      _pga_max_size = _smm_max_size * 2

• auto 모드에서 병렬쿼리의 각 슬레이브 프로세스가 사용할 수 있는 Work Area 총량은 _smm_px_max_size 파라미터에 의해 제한된다.
• SGA는 sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당한다.
• 이와 대조적으로,PGA는 자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggregate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다.
• 이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 work_area의 총량을 제한하는 용도로 사용된다.

h3.(3)수동 PGA 메모리 관리 방식으로 변경 시 주의사항

• manual 모드로 설정한 프로세스는 pga_aggregate_target 파라미터의 제약을 받지 않는다. 따라서 manual 모드로 설정한 많은 세션에서
  Sort Area와 Hash Area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼 페이징이
  발생하면서 시스템 전체 성능을 크게 떨어뜨릴 수 있다.
• 페이징이 심하면 시스템을 마비시킬 수도 있으므로 이들 파라미터를 수동으로 변경할 때는 그런 상황이 발생하지 않도록 세심한 주의를 기울여야한다.
  참고로, *_area_size를 위해 설정 가능한 값은 0부터 2,147,483,647까지다.
• 특히, workarea_size_policy 파라미터를 manual로 설정한 상태에서 병렬 쿼리를 사용하면 각 병력 슬레이브 별로 sort_area_size 크기만큼의 Sort Area를
  사용할 수 있다는 사실을 반드시 기억해야 한다.

h3.(4)PGA_AGGREGATE_TARGET의 적정 크기

• 오라클이 권고하는 pga_aggregate_target 파라미터 적정 크기
  • OLTP 시스템 : (Total Physical Memory * 80%) * 20%
  • DSS 시스템 : (Total Physical Memory * 80%) * 50%
• Multipass 소트가 종종 발생한다면 크기를 늘리거나 튜닝이 필요한 것으로 받아들여야 한다.

h3.(5)Sort Area 할당 및 해제

• 예전에는 소트 오퍼레이션이 시작되는 시점에 sort_area_size 크기만큼 메모리를 미리 할당했지만,
  8.0부터는 db_block_size 크기에 해당하는 청크(chunk) 단위로 필요한 만큼 조금씩 할당한다.
  8i 이전에는 할당된 PGA를 프로세스가 해제될 때까지 OS에 반환하지 않았다.
  9i에서 자동 PGA 메모리 관리 방식이 도입되면서부터는 프로세스가 더 이상 사용하지 않는 공간을 즉각 반환한다.

• 세션별로 현재 사용중인 PGA와 UGA 크기, 가장 많이 사용했을 때의 크기를 측정하는 SQL

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

• 자동 PGA 메모리 관리 방식으로 시스템 레벨에서 사용할 수 있는 총량을 아래와 같이 24MB로 제한하고 테스트를 진행해보자.

alter system set pga_aggregate_target = 24M;

create table t_emp
as
select *
from emp, (select rownum no from dual connect by level <= 100000);

emp 테이블을 10만 번 복제한 t_emp 테이블을 생성했다.
order by 절을 포함하는 아래 쿼리문이 수행될 대 PGA, UGA 크기가 어떻게 달라지는지 측정해보자.

select * from t_emp order by empno;

위 쿼리문이 수행되는 동안 다른 세션에서 아래 4단계로 나눠 측정하면된다. 참고로, 쿼리 완료 시점과 커서를 닫는 시점을 구분해 측정하려면,
사용자의 명시적인 요청이 있을 때만 Fetch를 진행하는 TOAD, Orange, PL/SQL Developer 같은 툴을 사용하는 것이 편하다.
**최초 : 쿼리 수행직전
**수행 도중 : 쿼리가 수행 중이지만 아직 결과가 출력되지 않은 상태
**완료 후 : 겨로가를 출력하기 시작했지만 데이터를 모두 Fetch하지 않은 상태
**커서를 닫은 후 : 정렬된 결과집합을 끝까지 Fetch 하거나 다른 쿼리를 수행함으로써 기존 커서를 닫은 직후
측정 결과를 표로서 요약하면 아래와 같다.

PGA와 UGA를 각각 6,584KB, 5,331KB만큼 사용했지만 커서를 닫은 직후에는 모두 반환한다는 사실을 위 표를 통해 알 수 있다.
'수행 도중'과 '완료 후'에 UGA, PGA 크기가 Max 값을 밑도는 이유는, 소트해야할 총량이 할당받을 수 있는 Sort Area 최대치를 초과하기 때문이다.
그때마다 중간 결과집합(Sort Run)을 디스크에 저장하고 메모리를 반환했다가를 필요한 만큼 다시 할당받는다는 사실을 알 수 있다.

• 이번에는 workarea_size_policy를 manual로 변경하고 Sort Area는 50MB 크기로 설정한 상태에서 테스트를 진행해 보자.

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

아래 표는 같은 쿼리를 수행하면서 각 단계별로 PGA, UGA 크기 변화를 측정한 것이다.

pga_aggregate_target을 24MB로 설정한 상태에서 한 세션의 Sort Area 크기가 50MB까지 도달했다.
manual 모드로 설정한 프로세스는 파라미터의 제약을 받지 않음을 알 수 있다.