소트 오퍼레이션은 수행과정에서 CPU와 메모리를 많이 사용하고, 데이터량이 많을 때는 디스크 I/O까지 일으킨다. 많은 서버 리소스를 사용하는 것도 문제지만 부분범위처리를 불가능하게 해 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인으로 작용하기도 한다.
01 소트 수행 원리
(1) 소트 수행 과정
SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다.
- 메모리 소트: 전체 데이터의 정렬 작업을 메모리 내에서 완료 (Internal Sort)
- 디스크 소트: 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우 (External Sort)
SGA -> PGA -> Temp Tablespace -> PGA
데이터 양이 많을 때는 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장한다. Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 'Sort Run' 이라고 부른다. Sort Run 생성을 마치면, 이를 다시 Merge해야 정렬된 최종 결과집합을 얻게 된다.
(3) Sort Area
Sort Area는 소트 오퍼레이션이 진행되는 동안 공간이 부족해질 때마다 청크(Chunk) 단위로 조금씩 할당한다.
Sort Area는 어떤 메모리 영역에 할당될까?
PGA
-> 각 오라클 서버 프로세스는 자신만의 PGA 메모리 영역을 할당받고, 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.
UGA
-> 세션이 프로세스 개수보다 많아질 수 있는 구조로서, 하나의 프로세스가 여러 개 세션을 위해 일한다. 각 세션을 위한 독립적인 메모리 공간이 필요해지는데, 이를 UGA (User Global Area)라고 한다.
CGA
-> Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다.
Sort Area 할당 위치
Sort Area가 할당되는 위치는 SQL문 종류와 소트 수행 단계에 따라 다르다.
DML 문장은 하나의 Execute Call 내에서 모든 데이터 처리를 완료하며, Execute Call이 끝나는 순간 자동으로 커서가 닫힌다. 따라서 DML 수행 도중 정렬한 데이터를 Call을 넘어서까지 참조할 필요가 없으므로 Sort Area를 CGA에 할당한다.
Select문에서의 데이터 정렬은 상황에 따라 다르다. Fetch Call에서 사용되므로 마지막 소트를 위한 Sort Area는 UGA에 할당한다.
반면 마지막보다 앞선 단계에서 정렬된 데이터는 첫 번째 Fetch Call 내에서만 사용되므로 Sort Area를 CGA에 할당한다.
(4) 소트 튜닝 요약
소트 오퍼레이션은 메모리 집약적일뿐만 아니라 CPU 집약적이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 가장 중요한 요소다.
따라서 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.
- 데이터 모델 측면에서의 검토
- 소트가 발생하지 않도록 SQL 작성
- 인덱스를 이용한 소트 연산 대체
- Sort Area를 적게 사용하도록 SQL 작성
- Sort Area 크기 조정
