[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)
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)
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
- 분석함수 수행 시 발생