7. Sort Area 크기 조정

조회 수 5490 추천 수 0 2011.06.14 21:38:56
실천하자 *.115.31.16


7. Sort Area 크기 조정

  • Sort Area 크기 조정을 통한 튜닝의 핵심
    • 디스크 소트 발생 방지
    • 불가피 시, Onepass 소트 처리
  • 9i 부터 두가지 PGA 메모리 관리 방식 지원


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

  • Work Area : 데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용되는 메모리 공간
  • 조정 파라미터 : sort_area_size, hash_area_size, bitmap_merge_area_size,create_bitmap_area_size
  • 8i까지 Work Area 기본 값을 관리자가 지정 및 직접 조정
  • 9i부터 "Automatic PGA Memory Management" 기능 도입으로 사용자가 일일이 그 크기 조정 하지 않아도 됨
    • 인스턴스 전체적으로 이용가능한 PGA 메모리 총량 지정 (pga_aggregate_target 파라미터)
  • 자동 PGA 메모리 관리
    • "workarea_size_policy = auto" (9i부터 Default 값 = "auto")
    • 오라클이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리 할당
    • 이 파라미터의 설정 값은 인스턴스 기동 중에 자유롭게 늘리거나 줄일 수 있음
    • *_area_size 파라미터는 모두 무시되며 오라클이 내부적으로 계산한 값 사용
    • 시스템, 세션 레벨에서 '수동 PGA 메모리 관리' 방식 전환 가능

  • 수동 PGA 메모리 관리
    • "workarea_size_policy = manual"
    • 트랜잭션이 거의 없는 야간에 대량의 배치 job 수행 시 효과적
      • 자동 PGA 메모리 관리가 활성화 시, 프로세스 당 사용할 수 있는 최대 크기 제한으로 여유 메모리가 있어도 충분히 메모리를 활용하지 못해 작업 시간이 오래 걸릴 수 있음
    • Sort Area (최대 2,147,483,647 byte) 와 Hash Area 크기 조정


(2) 자동 PGA 메모리 관리 방식 하에서 크기 결정 공식
  • auto 모드의 단일 프로세스가 사용할 수 있는 최대 work area 크기 
인스턴스 기동 시 오라클에 의해 내부적으로 결정
_smm_max_size 파라미터로 확인 가능(단위 : KB)


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


  • _smm_max_size 파라미터 값을 결정하는 내부 계산식
    • 9i 부터 10gR1 까지
_smm_max_size = least((pga_aggregate_target * 0.5), (_pga_max_size * 0.5))


☞ DB관리자가 지정한 pga_aggrate_target 의 5%와 _pga_max_size 파라미터의 50% 중 작은 값으로 설정


    • 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 파라미터 값
_pga_max_size = _smm_max_size * 2


  • AUTO 모드의 병렬 쿼리의 각 슬레이브 프로세스 사용가능한 work area 총량
  ☞ _smm_px_max_size 파라미터(KB)에 의해 제한

  • SGA : sga_max_size 파라미터로 설정된 크기만큼 공간 미리 할당
  • PGA : 자동 PGA 메모리 관리 기능을 사용하더라도 pga_aggregate_target 크기 만큼의 메모리를 미리 할당하지 않음
  • pga_aggregate_target 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당 받을 수 있는 work area의 총량을 제한하는 용도로 사용


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

  • manual 모드로 설정한 프로세스는 pga_aggregate_target 파라미터 제약 받지 않음
  • sort area와 hash area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼 페이징(paging)이 발생하면서 시스템 전체 성능 저하 가능 (심할 경우, 시스템 마비까지 가능)

※ 참고) *_area_size 설정 가능 범위 : 0 ~ 2147483647 (2GB - 1Byte)

  • manual 모드에서 병렬 쿼리를 사용하면 각 병렬 슬레이블 별로 sort_area_size크기 만큼의 Sort Area 사용 가능
  • manual 모드에서 병렬 Degree를 크게 설정할 때는 sort_area_size와 hash_area_size 반드시 확인



(4) PGA_AGGREGATE_TARGET 의 적정 크기

  • 오라클의 권고 값

    • OLTP 시스템 : (Total Physical Memory * 80%) * 20%
    • DSS   시스템 : (Total Physical Memory * 80%) * 50%



(5) Sort Area 할당 및 해제

  • Sort Area 할당 시점과 해제 시점
    • 8i 전 : 소트가 수행되는 시점에 sort_area_size 크기만큼의 메모리 미리 할당
    • 8i 이후  : db_block_size 크기에 해당하는 청크(chunk)단위로 필요한 만큼 조금씩 할당
    • sort_area_size는 할당할 수 있는 최대 크기를 지정하는 파라미터로 바뀐 것

  • PGA 공간
    • 8i 까지 : PGA 공간은 프로세스가 해제될 때까지 OS에 반환하지 않음
    • 9i 부터 : 자동PGA 메모리 관리 방식 도입으로 프로세스가 더 이상 사용하지 않는 공간을 즉시 반환함으로써 다른 프로세스가 사용 가능 (버그로 인해 PGA메모리가 반환되지 않는 경우가 종종 있음)

  • Sort Area 가 할당되고 해제 되는 과정 측정 테스트

/* 세션별 현재 사용 중인 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


    • 자동 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 ) ;


    • order by 절을 포함하는 쿼리 수행

SELECT * FROM   t_emp ORDER  BY empno ;

  • 최초  : 쿼리 수행 직전
  • 수행도중  : 쿼리가 수행 중이지만 아직 결과가 출력되지 않은 상태 (→ 값이 계속 변함)
  • 완료 후  : 결과를 출력하기 시작했지만 데이터를 모두 fetch하지 않은 상태
  • 커서를 닫은 후  : 정렬된 결과집합을 끝까지 fetch하거나 다른 쿼리를 수행함으로써 기존 커서를 닫은 직후

   ▼ 위의 쿼리 측정 결과 요약 표

  단    계 

           PGA(KB) 

    PGA_MAX(KB) 

          UGA(KB) 

    UGA_MAX(KB) 

  최초

376 

632 

153 

401 

  수행 도중 

5,560 

6,584 

4,308 

5,331 

  완료 후

3,000 

6,584 

2,774 

5,331 

  커서를 닫은 후    

376 

6,584 

153 

5,331 


    • 수행 도중'과 '완료 후' 에 UGA, PGA 크기가 MAX 값을 밑도는 이유

    ☞ 소트해야 할 총량이 할당 받을 수 있는 Sort Area 최대치 초과

                    ☞ Sort Area 초과마다 중간 결과집합(sort run)을 디스크에 저장하고 메모리를 반환했다가 다시 할당 받음


  • 수동 PGA 메모리 관리 방식으로 전환 테스트

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 ;


    ▼ 위의 쿼리 측정 결과 요약 표

  단    계 

           PGA(KB) 

    PGA_MAX(KB) 

          UGA(KB) 

    UGA_MAX(KB) 

  최초

376 

6,584 

153 

5,331 

  수행 도중 

48,760 

52,792 

43,049 

47,077 

  완료 후

4,792 

52,792 

4,315 

47,077 

  커서를 닫은 후    

440 

52,792 

153 

47,077 


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

   ( ∵ 파라미터로 설정한 값보다 더 큰 값 52,792(KB) = 54059008 byte 까지 도달 )



  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 위충환 (실천하자)
  • 최초작성일: 2011년 06월 13 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^