- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=6259850&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
소트 수행 원리
소트 수행 과정
- SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다.
- 메모리 소트(in-memory sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말한다.
- 디스크 소트(to-disk sort) : 할당받은 Sort Area 내에서 정렬를 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, 'External Sort'라고도 한다.
![]() | qsort 함수 사용법 #include <stdlib.h> 설명 qsort() 함수는 nmemb의 크기를 가지는 배열을 QuickSort한다. 배열의 요소는 size크기를 가진다. 마지막 인자로 정렬에 사용될 함수인 compar의 포인터를 넘겨준다. |
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로 설정하면 오라클이 내부적으로 결정한다.
![]() |
|
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'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=6259850&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
Comments (2)
11월 01, 2010
장태길 says:
one pass, multi pass 정확한 차이가 먼가요 ? 한번만 disk 에 쓴다, 두번 이상 disk 에 쓴다 이 차이로 나와 있는데.....one pass, multi pass 정확한 차이가 먼가요 ?
한번만 disk 에 쓴다, 두번 이상 disk 에 쓴다 이 차이로 나와 있는데..
100 메가를 한번 쓰나, 50메가씩 2번 쓰나 같은거 같은데...
차이를 알고 싶습니다.
11월 02, 2010
안종식 says:
PGA_AGGREGATE_TARGET 값의 크기에 따라서 workarea의 크기에 따라서 onepass/multipass가 결정된다고 합니다....PGA_AGGREGATE_TARGET 값의 크기에 따라서 workarea의 크기에 따라서 onepass/multipass가 결정된다고 합니다.
그리고 저의 개인적인 생각으로는 커널에서 메모리에 있는 내용을 디스크에 내리기 위해서는 OS 커널 함수를 이용하여 파일 입/출력과 open/close 또한 발생하고 다시 파일을 읽으면서 sort 작업이 발생할 것으로 생각됩니다.
optimal 같은 경우는 제 경험상 정렬을 한 포인터 메모리를 할당한 후 거기에 저장한 후 qsort를 통하여 정렬 작업을 한 후 다른 메모리에 복사를 한 후 메모리 free를 해주었습니다.
여기서도 multipass도 파일 입출력이 작을 경우는 크게 성능을 미치지 않으나 많은 데이터를 정렬할 경우 n번의 파일 I/O가 발생과 메모리 정렬 작업이 발생하여 성능에 영향을 미칠 것으로 판단됩니다.
V$SQL_WORKAREA,V$SQL_WORKAREA_ACTIVE,V$SQL_WORKAREA_HISTOGRAM 뷰를 통하여 WORKAREA의 크기를 확인할 수 있습니다.
결론은 one pass는 한 번의 파일 I/O가 발생하는 것이고 multi pass는 여러 번의 파일 I/O가 발생한다고 생각하면 될 것 같습니다. 즉 최대한 메모리에서 작업을 하는 것이 좋고 아니다라도 최소한의 파일 I/O를 발생하도록 SQL과 파라메터를 설정하는 것이 중요할 것이고 생각합니다.
이상 허접한 답변이였습니다. ^^;;