07 Sort Area 크기 조정

세션 레벨에서 Sort Area 크기를 조정하거나, 시스템 레벨에서 각 세션에 할당될 수 있는 총 크기를 조엉해야 할 때가 있다. Sort Area 크기 조정을 통한 튜닝의 핵심은 디스크 소트가 발생하지 않도록 하는 것을 1차 목표로 삼고 불가피할 때는 Onpass 소트 처리되도록 하는 데 있다.
오라클은 9i부터 두가지 PGA 메모리 관리 방식을 지원하므로 이에 대한 개념부터 살펴보자.

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

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

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

SQL> connect /as sysdba
연결되었습니다.
SQL>
SQL> SELECT a.ksppinm name ,
  2         b.ksppstvl VALUE
  3  FROM   sys.x$ksppi a ,
  4         sys.x$ksppcv b
  5  WHERE  a.indx = b.indx
  6  AND    a.ksppinm = '_smm_max_size' ;

NAME             VALUE
---------------  ----------
_smm_max_size    39321

이 파라미터 값을 결정하는 내부 게산식은 버전에 따라 다르며, 오라클 9i부터 10gR1까지는 아래 계산식을 따른다.

_smm_max_size = least((pga_aggregate_target X 0.05), (_pga_max_size X 0.5))

즉, DB 관리자가 지정한 pga_aggregate_target의 5%와 _pga_max_size 파라미터의 50% 중 작은 값으로 설정된다.
10gR2 부터는 조금 더 복잡하다.

*pga_aggregate_target <= 500MB 일 때
_smm_max_size = pga_aggregate_target X 0.2

*pga_aggregate_target <= 1000MB 일 때
_smm_max_size = 100MB

*pga_aggregate_target > 1000MB 일 때
_smm_max_size = pga_aggregate_target X 0.1

_pga_max_size는 거꾸로 _smm_max_size에 의해 결정된다.

_pga_max_size = _smm_max_size X 2

참고로 auto 모드에서 병렬 쿼리의 각 슬에이브 프로세스가 사용할 수 있는 Work Area 총량은 _smm_px_max_size 파라미터(KB)에 의해 제한된다.
PGA는 자동 PGA 메모리 관리를 사용한다고 해서 pga_aggregate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다. 이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다.

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

manual 모드로 설정한 프로세스는 pga_aggregate_target 파라미터의 제약을 받지 않는다. 따라서 manual 모드로 설정한 많은 세션에서 Sort Area와 Hash Area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼 페이징이 발생하면서 시스템 전체 성능을 크게 떨어뜨릴 수 있다.
특히, workarea_size_policy 파라미터를 manual로 설정한 상태에서 병렬 쿼리를 사용하면 각 병렬 슬레이브(Slave) 별로 sort_area_size 크기만큼의 Sort Area를 사용할 수 있다는 사실을 반드시 기억해야 한다. 만약 아래와 같은 쿼리를 수행할 때 어떤 일이 발생할지 상상해보라.

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

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

(4) PGA_AGGREGATE_TARGET의 적정 크기

pga_aggregate_target 파라미터 설정을 위한 적정 크기는 얼마일까? 오라클이 권고하는 값은 아래와 같다

-OLTP 시스템 : (Total Physical Memory X 80%) X 20%
-DSS 시스템 : (Total Physical Memory X 80%) X 50%

이것은 일반적인 권고 사항일 뿐이며 애플리케이션 특성에 따라 모니터링 결과를 바탕으로 세밀한 조정이 필요하다. 대부분(90% 이상, 순수 OLTP 시스템에서는 100%) Optimal 소트 방식으로 수행되고 나머지 일부(10% 미만)만 Onepass 소트 방식으로 수행되는 것을 목표로 삼아야 한다. 시스템에 Multipass 노트가 종종 발생하는 것으로 측정되면 크기를 늘리거나 튜닝이 필요한 것으로 받아들여야 하며, 이는 대용량 소트와 해시 조인이 많이 발생하는 DSS 시스템에서도 마찬가지다.

(5) Sort Area 할당 및 해제

여기서는 Sort Area가 할당되는 시점과 해제되는 시점에 대해 살펴보려고 한다.
예전에는 소트 오퍼레이션이 시작되는 시점에 sort_area_size 크기만큼 메모리를 미리 할당했지만 오라클 8.0부터는 db_block_size 크기에 해당하는 청크(Chunk) 단위로 필요한 만큼 조금씩 할당한다. 즉, sort_area_size를 할당할 수 있는 최대 크기를 지정하는 파라미터로 바뀐 것이다.
그리고 오라클 8i, 이전에는 프로세스를 위해 할당된 PGA 공간을 프로세스가 해제될 때까지 OS에 반환하지 않았다. 9i에서 자동 PGA 메모리 관리 방식이 도입되면서부터는 프로세스가 더 이상 사용하지 않는 공간을 즉각 반환함으로써 다른 프로세스가 사용할 수 있도록 한다.(버그로 인해 PGA 메모리가 반환되지 않는 경우는 지금도 종종 발견된다.)

실제 Sort Area가 할당되고 해제되는 과정을 측정해보자. 아래는 세션별로 현재 사용중인 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 메모리 관리 방식으로 시스템 레벨에서 사용할 수 있는 총량을 아래와 같이 24M으로 제한하고 테스트를 진행해 보자.

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 하지 않으 상태
• 커서를 닫은 후 : 정렬된 결과집합을 끝까지 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_aggreate_target를 24MB로 설정한 상태에서 한 세션은 Sort Area 크기가 50MB까지 도달했다. 앞에서 얘기했듯이 manual 모드로 설정한 프로세스는 이 파라미터의 제약을 받지 않음을 알 수 있다.

문서에 대하여

• 최초작성자 : 황종필
• 최초작성일 : 2010년 06월 05일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*