01.소트 수행 원리

소트 오퍼레이션 측정

create table t_emp
as
select *
from emp, (select rownum no from dual connect by level <= 100000);

--수동으로 workarea 조정
alter session set workarea_size_policy = manual;

alter session set sort_area_size = 1048576;

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;

Auto Trace 내용
--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    12M|   915M|       |   782K  (1)| 02:36:29 |
|   1 |  SORT ORDER BY       |       |    12M|   915M|  1131M|   782K  (1)| 02:36:29 |
|*  2 |   VIEW               |       |    12M|   915M|       |   401K  (1)| 01:20:13 |
|   3 |    WINDOW SORT       |       |    12M|   915M|  1131M|   401K  (1)| 01:20:13 |
|   4 |     TABLE ACCESS FULL| T_EMP |    12M|   915M|       | 19664   (2)| 00:03:56 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NO"=1)

Statistics
----------------------------------------------------------
        575  recursive calls
          8  db block gets
      71367  consistent gets
     216723  physical reads
          0  redo size
       2047  bytes sent via SQL*Net to client
        302  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          1  sorts (memory) => 메모리 소트 발생
          1  sorts (disk)   => 디스크 소트 발생
        114  rows processed

DBMS_XPLAN.DISPLAY_CURSOR 내용
------------------------------------------------------------------------------------
| Id  | Operation            | Name  | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |        |       |       |          |         |
|   1 |  SORT ORDER BY       |       |     12M| 16384 | 16384 |14336  (0)|         |
|*  2 |   VIEW               |       |     12M|       |       |          |         |
|   3 |    WINDOW SORT       |       |     12M|   640M|  8307K| 1057K (1)|     571K| => DISK SORT가 일어난 것을 알 수 있다.
|   4 |     TABLE ACCESS FULL| T_EMP |     12M|       |       |          |         |
------------------------------------------------------------------------------------

OMem : Optimal 소트에 필요한 예상 메모리
1Mem : One Pass 소트에 필요한 예상 메모리

두 번의 소트 오퍼레이션(실행계획에 나타난 sort order by, window sort)이 발생했는데 그 중 한 번은 메모리내에서 처리를 완료했고,
다른 한 번은 디스크 소트가 발생했다.
소트할 데이터량을 감안할 때, 전자는 1번 오퍼레이션 단계(ID=1)에 해당하고 (no = 1 조건으로 필터링된 결과집합만 정렬하므로),
후자는 3번 오퍼레이션 단계(ID=3)에 해당한다.
디스크 I/O시 버퍼캐시를 경유하므로 일반적으로 디스크 블록 I/O 개수가 메모리 블록 I/O 개수를 초과할 수 없다.
그런데 여기서는 physical reads 값(216723)이 consistent gets와 db block gets를 합한 값(71375)보다 훨씬 크다.
디스크 소트 과정에서 발생한 디스크 I/O까지 physical reads에 포함됐기 때문이다.

Sort Area 할당 위치

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    | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |        |       |       |          |
|   1 |  SORT ORDER BY                 |         |   7013 | 14336 | 14336 |12288  (0)| -> UGA
|   2 |   WINDOW SORT                  |         |   7013 | 14336 | 14336 |12288  (0)| -> CGA
|   3 |    MERGE JOIN                  |         |   7013 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |       |       |          |
|   5 |      INDEX FULL SCAN           | DEPT_PK |      4 |       |       |          |
|*  6 |     SORT JOIN                  |         |   7013 | 12288 | 12288 |10240  (0)| -> CGA
|   7 |      TABLE ACCESS FULL         | EMP     |   7013 |       |       |          |
--------------------------------------------------------------------------------------

SELECT문 마지막 단계더라도 sort_area_retained_size 제약이 있으면, 소트 작업은 CGA에서 수행하고
이 제약만큼 공간을 UGA에 할당해 소트 결과를 그곳에 옮겨 담는다.
그럼으로써 이후 발생하는 Fetch Call을 통해 조금씩 결과 집합을 전송할 수 있도록 한다.
물론 마지막 단계에서 실제 사용한 CGA 공간이 sort_area_retained_size로 지정한 값보다 크다면
모두 옮겨 담을 수 없으므로 일부 Temp 세그먼트에 저장한다.

마지막보다 앞선 단계에서 정렬을 수행할 때도 sort_area_retained_size 제약이 있다면 다음 단게로 넘어가기 전에
이 값을 초과한 분량을 Temp 세그먼트에 저장하고, 초과한 만큼의 CGA메모리는 반환한다.