[Oracle] 오라클 소트

작년 SQLP 시험에 소트 연산에 관한 문제가 출제됐었는데 

시험장을 나온 후 틀렸음을 깨닫고 내 머리를 쥐어박았던 기억이 있다. 

다시 나온다면 결코 틀리지 않으리라는 각오로 

지금부터 소트에 대해 파보려고 한다.

소트 수행 원리

- SQL 수행 도중 데이터 정렬이 필요할 시 PGA 메모리에 Sort Area를 할당

① 메모리 소트 (in-memory sort): 전체 데이터의 정렬 작업을 메모리 내에서 완료(=Internal Sort)

② 디스크 소트 (to-dist sort): 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용(=External Sort)

※ 디스크 소트 과정

① PGA에서 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장

* Sort Run: Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합

② Temp 영역에서 Sort Run 생성이 완료되면 다시 Sort Area로 merge

무려 1500만건의 데이터가 저장된 ORD 테이블로 소트 연산을 유도해보자.

SELECT *

  FROM (

          SELECT ORD_DT, 

                 BRANCH_CD,

                 COUNT(ORD_NO) OVER(PARTITION BY BRANCH_CD, ORD_DT) CNT

            FROM ORD

       )

 WHERE CNT > 150

 ORDER BY BRANCH_CD;

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      4      0.00       0.00          0          0          0           0

Fetch      126     47.12      72.67    1419171    1022758         84        1821

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      133     47.12      72.67    1419171    1022758         84        1821

* disk에 표시된 개수에 디스크 소트 과정에서 발생한 디스크 I/O가 포함

Rows     Row Source Operation

-------  ---------------------------------------------------

    607  SORT ORDER BY (cr=340920 pr=473057 pw=132146 time=454 us)

    607   VIEW  (cr=340920 pr=473057 pw=132146 time=363 us)

15000000    WINDOW SORT (cr=340920 pr=473057 pw=132146 time=12253771 us)

15000000     TABLE ACCESS FULL ORD (cr=340920 pr=340910 pw=0 time=3018483 us)

* window sort 오퍼레이션 단계에서 실제 473,057개의 블록을 읽었고 132,146개의 블록을 Temp 세그먼트에 저장

※ 소트 과정에서 발생하는 디스크 I/O는 Direct Path I/O 방식을 사용하므로 버퍼 캐시를 경유하는 일반적인 디스크 I/O에 비해 무척 가볍다.

Sort Area

Sort Area는 PGA 메모리에 할당된다고 했는데 PGA 영역이란 정확히 어디일까?

PGA(Process/Program/Private Global Area)는 각 서버 프로세스가 갖는 자신만의 영역으로, 독립적인 공간이기 때문에 Latch 메커니즘이 필요 없어 SGA 영역보다 훨씬 빠르다.

- 하나의 프로세스는 하나의 PGA를 갖는다 .

- 하나의 세션은 하나의 UGA를 갖는다 .

- PGA에는 세션과 독립적인 프로세스만의 정보를 관리한다 .

- UGA에는 프로세스와 독립적인 세션만의 정보를 관리한다.

- UGA는 전용 서버 방식으로 연결할 때는 PGA에, 공유 서버 방식으로 연결할 때는 SGA에 할당된다.

* CGA : Call이 진행되는 동안만 필요한 정보 저장 (PGA에 할당)

* UGA : Call을 넘어서 다음 Call까지 계속 참조되는 정보 저장

소트를 발생시키는 오퍼레이션

① Sort Aggregate

- 전체 로우를 대상으로 집계를 수행할 때 발생, 실제 소트가 발생하지는 않는다.

② Sort Order By

- order by 오퍼레이션 수행 시 발생

③ Sort Group By

- 소팅 알고리즘을 사용해 그룹별 집계를 수행할 시 발생

- SQL문에 order by를 명시하지 않았을 때는 결과 집합의 정렬을 보장할 수 없다.

- distinct count 연산 시 발생

* Hash Group By

- group by만 사용하고 order by가 없는 경우 대부분 hash group by 방식으로 처리 (정렬 수행 X)

Sort Unique

SELECT /*+ ordered use_nl(dept) */ * 

  FROM DEPT

 WHERE DEPTNO IN ( SELECT /*+ UNNEST */ DEPTNO

                     FROM EMP

                    WHERE JOB = 'CLERK');

- emp테이블에서 deptno가 unique하지 않고 세미 조인이 아닐 때 발생

- union, minus, intersect 집합 연산 시 발생

- distinct 연산 시 발생 (order by가 있을 때)

* distinct 연산 시 order by가 없으면 hash unique 방식으로 수행

Sort Join

- sort merge join 수행 시 발생

Window Sort

- 분석함수 수행 시 발생