7. Sort Area 크기 조정
2011.06.16 05:09
07. Sort Area 크기 조정.
Sort Area 크기 조정을 통한 튜닝의 핵심
- 디스크 소트가 발생하지 않도록 하는 것이 1차 목표.
- 1차 목표가 안될때는 Onepass 소트로 처리.
(1) PGA 메모리 관리 방식의 선택
- 9i부터 '자동 PGA 메모리 관리'(Automatic PGA Memory Management) 기능이 도입.
- DB관리자는 pga_aggargate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량만 지정.
- 자동PGA 메모리기능 활성화 : 9i부터 workarea_size_policy가 기본 auto. auto 상태에서는 *_area_size 파라미터는 모두 무시.
- auto모드에서 단일 프로세스가 사용할 수 있는 최대 Work Area크기는 인스턴스 기동 시 오라클에 의해 내부적으로 결정. _summ_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' |
- 이 파라미터 값을 결정하는 내부 계산식은 버전에 따라 다르다.
9i와 10gR1까지는 동일한 계산식 사용, 10gR2부터 pga_aggargate_target 용량에 따라 계산식이 바뀜.
- auto 모드에서 병렬 쿼리의 각 슬레이브 프로세스가 사용할 수 있는 work_area 총량은 _smm_px_max_size 파라미터(kb)에 의해 제한
- SGA는 sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당. 이와 대조적으로 PGA는
자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggargate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다. 이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다.
- 수동PGA 메모리기능 : 트랜잭션이 거의 없는 야간에 대량의 배치 JOB을 수행할때 수동 방식으로 변경.
장점 : 자동 PGA 메모리 관리 방식 하에서는 프로세스당 사용할 수 있는 최대 크기가 제한. 즉 Work Area를 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸수 없음. 결구 수 GB의 여유 메모리를 두고도 이를 충분히 활용하지 못해 작업 시간이 오래 걸릴수 있음. 이럴 때 workarea_size_policy 파라미터를 세션 레벨에서 manual로 변경하고, 필요한 만큼 Sort Area와 Hash Area 크기를 늘림으로써 성능을 향상시키고 궁극적으로 전체 작업 시간을 단축. |
단점 : manual 모드로 설정한 pga_aggargate_target 파라미터의 제약을 받지 않는다. 따라서 manaual 모드로 설정한 많은 세션에서 Sort Area와 Hash Area를 아주 큰 값으로 설정하고 실제 매우 큰 작업을 동시에 수행 한다면 가용한 물리적 메모리가 고갈돼 페이징이 발생 하면서 시스템 전체의 성능을 크게 떨어뜨릴 수 있다. 따라서 manual 모드에서 병렬 Degree를 크게 설정할 대는 sort_area_size와 hash_area_size를 반드시 확인해야 한다. |
(2) PGA_AGGREGATE_TARGET의 적정 크기
* OLTP 시스템 : (Total Physical Memory * 80%) * 20%
* DSS시스템 : (Total Physical Memory * 80%) * 50%
10G 물리적 메모리 | |||
구분 | os | ORACLE | |
SGA | PGA | ||
OLTP | 2G | 6.4G | 1.6G |
DSS | 2G | 4G | 4G |
이것은 일반적인 권고사항일 뿐이며, 각 애플리케이션의 특성에 따라 세밀한 조정이 필요하다.
대부분(90% 이상, 순수 OLTP 시스템에서는 100%) Optimal 소트 방식으로 수행하고 나머지 일부(10% 미만) 만 Onepass 소트 방식으로 수행되는 것을 목표로 삼아야 한다.
(3) Sort Area 할당 및 해제.
오라클 8i 이전에는 프로세스를 위해 할당된 PGA 공간을 프로세스가 해제될 때가지 OS에 반환하지 않음. 9i에서 자동 PGA메모리 관리 방식이 도입되면서 부터는 프로세스가 더이상 사용하지 않는 공간을 즉각 반환.
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
15 |
1. 소트 수행 원리
![]() | balto | 2011.06.12 | 8016 |
14 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 오예스 | 2011.06.14 | 4205 |
13 | 5장. 소트 튜닝 | balto | 2011.06.16 | 4283 |
12 | 3. 데이터 모델 측면에서의 검토 | AskZZang | 2011.06.16 | 2724 |
11 | 4. 소트가 발생하지 않도록 SQL 작성 | AskZZang | 2011.06.16 | 3109 |
10 | 5. 인덱스를 이용한 소트 연산 대체 | AskZZang | 2011.06.16 | 4954 |
» | 7. Sort Area 크기 조정 | 오예스 | 2011.06.16 | 5973 |
8 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11836 |
7 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28272 |
6 |
5. 병렬 처리에 관한 기타 상식
[1] ![]() | balto | 2011.06.26 | 28162 |
5 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15597 |
4 | 3. 병렬 조인 | 오예스 | 2011.06.29 | 4969 |
3 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10107 |
2 | 진행기록 | 운영자 | 2011.08.23 | 2383 |
1 | 4장._쿼리_변환 | 휘휘 | 2011.09.07 | 2394 |