1. 소트 수행 원리 (by csahn79) [2010.11.01]
소트 수행 원리
소트 수행 과정
- SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다.
- 메모리 소트(in-memory sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말한다.
- 디스크 소트(to-disk sort) : 할당받은 Sort Area 내에서 정렬를 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, 'External Sort'라고도 한다.
qsort 함수
사용법 #include <stdlib.h>
void qsort(void *base, size_t nmemb, size_t size,
int(*compar)(const void *, const void *));
설명 qsort() 함수는 nmemb의 크기를 가지는 배열을 QuickSort한다. 배열의 요소는 size크기를 가진다. 마지막 인자로 정렬에 사용될 함수인 compar의 포인터를 넘겨준다.
compar함수는 더 크면 0, 작거나 같으면 0보다 큰 수를 리턴하도록 만든다.
qsort 함수를 이용하여 메모리 안에서 비교를 할 경우 malloc 함수를 이용하여 메모리 공간을 할당한 후 그 안에서 정렬을 한다.
정렬이 완료되면 free 함수를 이용하여 메모리를 반환한다.
Sort Area 안에서 데이터 정렬을 하는 것이 최적(optimal sort)이지만, 양이 많을 때는 중간 결과집합을 Temp Segment에 임시 저장한다. 중간 집합을 Sort Run이라고 부른다. 이 과정이 끝나면 Merge해야 정렬된 최종 결과집합을 얻는다.
이와 같이 정렬된 결과를 Temp 영역에 임시 저장했다가 다시 읽는 과정은 디스크 소트가 발생한다. 만일 그 크기가 크다면 Sort Run으로부터 읽은 데이터를 다시 디스크에 썼다가 읽어 들이는 과정을 여러 번 반복하게 되므로 성능이 나뻐진다(multipass sort)
- Optimal Sort : 소트 오퍼레이션이 메모리 내에서만 이루어짐.
- Onepass Sort : 정렬 대상 집합이 디스크에 한 번만 쓰임.
- Multipass Sort : 정렬 대상 집합이 디스크에 여러 번 쓰임.
소트 오퍼레이션 측정
소트 오퍼레이션이 AutoTrace에서 어떻게 측정되는지 알아본다.
CREATE TABLE T_EMP AS SELECT * FROM EMP, (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 100000);
ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = 1048576;
-- AutoTrace 테스트
SET autotrace traceonly;
SELECT *
FROM (SELECT NO,EMPNO,ENAME,JOB,MGR,SAL
,AVG(SAL) OVER(PARTITION BY TO_CHAR(NO), DEPTNO) AVG_SAL
FROM T_EMP)
WHERE NO = 1
ORDER BY SAL DESC;
Execution Plan
----------------------------------------------------------
Plan hash value: 2032858773
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1309K| 97M| | 67057 (1)| 00:13:25 |
| 1 | SORT ORDER BY | | 1309K| 97M| 240M| 67057 (1)| 00:13:25 |
|* 2 | VIEW | | 1309K| 97M| | 34817 (1)| 00:06:58 |
| 3 | WINDOW SORT | | 1309K| 97M| 240M| 34817 (1)| 00:06:58 |
| 4 | TABLE ACCESS FULL| T_EMP | 1309K| 97M| | 2576 (2)| 00:00:31 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
104 recursive calls
3958 db block gets
9399 consistent gets
80275 physical reads
0 redo size
893 bytes sent via SQL*Net to client
240 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
14 rows processed
emp 테이블을 10만 번 복제한 t_emp 테이블을 생성하고 디스크 소트가 발생하도록 workarea_size_policy를 manual로 변경하고 sort_area_size는 1MB로 낮게 설정한다.
두 번의 소트 오퍼레이션이 발생했는데 그 중 한 번은 메모리에서 처리하고 한 번은 디스크에서 발생했다. 전자는 ID1에 해당하고 후자는 ID3에 해당한다. 여기서 physical reads 값이 consistent gets와 db block gets를 합한 값보다 큰 이유는 디스크 소트에서 발생한 디스크 I/O까지 physical reads에 포함되었기 때문이다. 최소 하드 파싱에서 발생한 디스크 I/O까지 포함되었을 것이다.
-- Trace 결과
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.012 0 121 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 26.970 134.809 71180 9261 3958 14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 26.990 134.821 71180 9382 3958 14
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
14 SORT ORDER BY (cr=9261 pr=71180 pw=71179 time=134808812 us)
14 VIEW (cr=9261 pr=71180 pw=71179 time=113696286 us)
1400000 WINDOW SORT (cr=9261 pr=71180 pw=71179 time=126296185 us)
1400000 TABLE ACCESS FULL T_EMP (cr=9255 pr=0 pw=0 time=106 us)
Wait Event Name Count Wait(sec) Max Wait
-------------------------------------------------- ------- ---------- --------
direct path write temp 13778 39.047 0.003
SQL*Net message to client 3 0.000 0.000
SQL*Net message from client 3 0.005 0.003
direct path read temp 57670 70.355 0.000
--------------------------------------------------- ------- --------- --------
Total 71454 109.41
위의 결과에서 보듯이, 테이블 액세스 시 pr 블록은 0이나, 분석함수에 의한 'WINDOW SORT'시 pr 블록이 71180으로 늘어났으므로 전량 소트에서 발생한 것을 알 수 있으며, 추가로 pw의 지표의 결과처럼 71179 블록을 Temp 세그먼트에 저장한 것을 볼 수 있다.
Sort Area
데이터 정렬을 위해 사용되는 Sort Area는 소트 오퍼레이션이 진행되는 동안 공간이 부족할 때마다 Chunk 단위로 할당된다. 세션마다 사용할 수 있는 최대 크기를 예전에는 sort_area_size 파라미터로 설정하였으나, 9i부터는 새로 생긴 workarea_size_policy 파라미터를 auto로 설정하면 오라클이 내부적으로 결정한다.
- sort_area_retained_size는 데이터 정렬을 끝내고 나서 결과집합을 모두 Fetch할 때까지 유지할 Sort Area 크기를 지정
- 이 크기를 초과한 데이터는 Temp 세그먼크에 저장했다가 Fetch 과정에서 다시 읽어들이므로, sort_area_size 크기보다 정렬된 결과 집합보다
작다면 디스크 I/O가 발생함. - 참고로, 0으로 설정하면 Fetch가 완료될 때까지 Sort Area 크기를 그대로 유지하겠다는 의미
PGA(Process Global Area)
각 오라클 서버 프로세스는 자신만의 PGA(Process/Program/Private Global Area) 메모리 영역을 할당받고, 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다. PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간으로서, 래치 메커니즘이 필요 없어 똑같의 개수의 블록을 읽더라도 SGA 버퍼 캐시에서 읽는 것보다 휠씬 빠르다.
UGA(User Global Area)
- 전용서버 방식으로 연결할 때는 프로세스와 세션이 1:1이지만, 공유서버 방식은 1:M 관계이므로, 공유서버에서 여러 세션을 관리하기 위해
UGA 공간을 사용한다.- 하나의 프로세스는 하나의 PGA를 갖는다.
- 하나의 세션은 하나의 UGA를 갖는다.
- PGA에는 세션과 독립적인 프로세스만의 정보를 관리한다.
- UGA에는 프로세스와 독립적인 세션만의 정보를 관리한다.
- 거의 대부분 전용 서버 방식을 사용하므로 세션과 프로세스는 1:1 관계고, 따라서 UGA도 PGA 내에 할당된다고 보면 된다.
3) CGA(Call Global Area)
오라클은 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고, Call이 진행되는 동안에 필요한
데이터는 CGA에 담음.
- CGA는 Parse, Execute, Fetch Call 마다 매번 할당받는데, Recursive Call이 발생하면 그 안에서도 단계별로 CGA가 추가로 할당
- CGA : Call이 진행되는 동안만 필요한 정보 저장.
- UGA : Call을 넘어서 다음 Call까지 계속 참조되는 정보 저장.
Sort Area 할당 위치
Sort Area가 할당되는 위치는 SQL문 종류와 소트 수행 단계에 따라 다르다.
DML 문장은 하나의 Execute Call 내에서 모든 처리를 완료하며, Execute Call이 끝나는 순간 자동으로 커서가 닫힌다. 즉 DML 수행 도중 정렬한 데이터를 Call을 넘어서까지 참조할 필요가 없으므로 Sort Area를 CGA에 할당한다. SELECT문에서의 데이터 정렬은 상황에 따라 다르다. SELECT 문장이 수행되는 가장 마지막 단계에서 계속 이어지는 Fetch Call에서 사용되야 한다. 즉 sort_retained_size 제약이 없다면 그 마지막 소트를 위한 Sort Area는 UGA에 할당된다. 반면 마지막보다 앞선 단계에서 정렬된 데이터는 첫 번째 Fetch Call 내에서만 사용되므로 Sort Area를 CGA에 할당한다.
SET autotrace traceonly;
SELECT /*+ ORDERED USE_MERGE(E) */ D.DEPTNO,D.DNAME,E.ENAME,E.SAL,E.JOB
,RANK() OVER(PARTITION BY D.DEPTNO ORDER BY E.SAL) SAL_RANK
FROM DEPT D,
EMP E
WHERE D.DEPTNO = E.DEPTNO
ORDER BY E.SAL DESC;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1092 | 14 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1092 | 14 (29)| 00:00:01 | --> UGA
| 2 | WINDOW SORT | | 14 | 1092 | 14 (29)| 00:00:01 | --> CGA
| 3 | MERGE JOIN | | 14 | 1092 | 12 (17)| 00:00:01 |
| 4 | SORT JOIN | | 4 | 116 | 6 (17)| 00:00:01 | --> CGA
| 5 | TABLE ACCESS FULL| DEPT | 4 | 116 | 5 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 686 | 6 (17)| 00:00:01 | --> CGA
| 7 | TABLE ACCESS FULL| EMP | 14 | 686 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------
SELECT문 마지막 단계라도 sort_area_retained_size 제약이 있을 경우, 소트 작업은 CGA에서 수행하고 제약만큼의 공간을 UGA에 할당해 소트된 결과를 옮긴다.
위의 내용을 요약하면 다음과 같다.
- DML 문장 수행 시 발생하는 소트는 CGA에서 수행
- SELECT 문장 수행 시
- 쿼리 중간 단계의 소트 : CGA에서 수행. sort_area_retained_size 제약이 있다면 다음 단계로 넘어가기 전에 이 값을 초과하는 CGA 영역을 반환
- 결과집합을 출력하기 직전 단계에서 수행하는 소트
- sort_area_retained_size 제약이 있다면, CGA에서 소트 수행. 이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이 후 Fetch Call에서 Array 단위로 전송
- sort_area_retained_size 제약이 없다면, 곧바로 UGA에서 소트 수행
소트 튜닝 요약
소트 우퍼레이션은 메모리 집역적일 뿐만 아니라 CPU 집약적이기도 하며, 데이터량이 많을 경우 DISK I/O까지 발생하여 성능에 영향을 끼치게 된다. 즉 될 수 있으면 소트를 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.
- 최초작성자 : 안종식
- 최초작성일 : 2010년 11월 03일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*