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 크기 조정

인스턴스 기동 시 오라클에 의해 내부적으로 결정

_smm_max_size 파라미터로 확인 가능(단위 : KB)

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 = least((pga_aggregate_target * 0.5), (_pga_max_size * 0.5))

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

_smm_max_size = pga_aggregate_target * 0.2

_smm_max_size = 100MB

_smm_max_size = pga_aggregate_target * 0.1

_pga_max_size = _smm_max_size * 2

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