7. Sort Area 크기 조정
2011.06.13 19:25
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 크기 조정
- auto 모드의 단일 프로세스가 사용할 수 있는 최대 work area 크기
인스턴스 기동 시 오라클에 의해 내부적으로 결정_smm_max_size 파라미터로 확인 가능(단위 : KB)
- Work Area 크기 조회
SELECT a.ksppinm name, b.ksppstvl VALUEFROM sys.x$ksppi a, sys.x$ksppcv bWHERE 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 총량
- 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
- 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
20 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3397 |
19 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2800 |
18 | 6. 히스토그램 | 실천하자 | 2011.04.24 | 11008 |
17 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4640 |
16 |
6. Sort Area를 적게 사용하도록 SQL 작성
![]() | 실천하자 | 2011.06.13 | 8928 |
15 | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.08 | 17043 |
14 | 7. 비용 | 휘휘 | 2011.05.02 | 6218 |
13 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 9617 |
» | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.13 | 15240 |
11 | 8. 인덱스 설계 | 멋진넘 | 2011.03.06 | 8389 |
10 | 7. 조인을 내포한 DML 튜닝 | 실천하자 | 2011.04.03 | 7228 |
9 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.29 | 32211 |
8 | 8. 공통 표현식 제거 | darkbeom | 2011.06.06 | 5296 |
7 |
9. 비트맵 인덱스
![]() | 실천하자 | 2011.03.05 | 12417 |
6 |
8. 고급 조인 테크닉-1
[1] ![]() | darkbeom | 2011.04.03 | 13367 |
5 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.06 | 7927 |
4 |
8. 고급 조인 테크닉-2
![]() | suspace | 2011.04.05 | 7074 |
3 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5572 |
2 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.07 | 6039 |
1 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.02 | 6702 |