Sort Area 크기 조정

Current Page: http://www.bysql.net/index.php?document_srl=14752   <<==출처

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 까지 도달 )