메뉴 건너뛰기

bysql.net

7. Sort Area 크기 조정

2011.06.13 19:55

실천하자 조회 수:14702


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
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^

번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.15 149196
59 3. 인덱스 파티셔닝 darkbeom 2011.06.19 53386
58 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.18 33506
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.29 30475
56 2. 파티션 Pruning 실천하자 2011.06.21 25672
55 3. 뷰 Merging 실천하자 2011.05.15 23037
54 3. 해시 조인 file darkbeom 2011.03.20 21206
53 2. 서브쿼리 Unnesting darkbeom 2011.05.15 19292
52 4. 통계정보 Ⅰ darkbeom 2011.04.26 17818
51 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.08 16596
» 7. Sort Area 크기 조정 실천하자 2011.06.13 14702
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.23 14343
48 1. 인덱스 구조 [1] file 실천하자 2011.02.16 13932
47 4. 조인 순서의 중요성 운영자 2011.03.27 13918
46 1. 기본 개념 멋진넘 2011.06.28 13063
45 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.03 12955
44 9. 비트맵 인덱스 file 실천하자 2011.03.05 12068
43 1. 옵티마이저 file 실천하자 2011.04.18 10930
42 6. 히스토그램 실천하자 2011.04.24 10669
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.13 8589