by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1. 소트 수행 원리

Added by 안종식, last edited by 안종식 on 11월 03, 2010



소트 수행 원리

소트 수행 과정

  • 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'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (2)

 

  1. 11월 01, 2010

    장태길 says:

    one pass, multi pass 정확한 차이가 먼가요 ? 한번만 disk 에 쓴다, 두번 이상 disk 에 쓴다 이 차이로 나와 있는데.....

    one pass, multi pass 정확한 차이가 먼가요 ?
    한번만 disk 에 쓴다, 두번 이상 disk 에 쓴다 이 차이로 나와 있는데..
    100 메가를 한번 쓰나, 50메가씩 2번 쓰나 같은거 같은데...
    차이를 알고 싶습니다.

    1. 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과 파라메터를 설정하는 것이 중요할 것이고 생각합니다.
      이상 허접한 답변이였습니다. ^^;;