🖥️ 들어가며
📌 소트를 발생시키는 오퍼레이션: JO GA UW
- Sort Join
- Sort Order by
- Sort Group by
- Sort Aggregate
- Sort Unique
- Window Sort
✏️ 1. 메모리 소트와 디스크 소트
| 구분 | 설명 |
| 메모리 소트 (In-Memeory) | ⭐⭐⭐ 전체 데이터의 정렬 작업을 → 할당받은 메모리 내에서 완료하는 것을 말합니다. Internal Sort 또는 Optimal Sort라고도 합니다. |
| 디스크 소트 (To-Disk) | ⭐⭐⭐ 할당받은 소트 영역 내에서 정렬을 완료하지 못해, 디스크 공간까지 사용하는 것을 말합니다. External Sort라고도 합니다. - Onepass Sort: 정렬대상집합을 디스크에 1번만 기록합니다. - Multipass Sort: 정렬대상집합을 디스크에 여러 번 기록합니다. |
- Oracle: 소트 영역을 PGA에 할당하고, SQL Server: 버퍼 캐시에 할당합니다.
- 소트 시 메모리 공간이 부족할 경우 디스크 공간을 사용합니다.
- SORT RUNS: 정렬된 중간결과집합을 임시로 저장하는 Temp 영역의 집합입니다.
[발생하는 경우]
- Sort Area 내에서 데이터 정렬을 마무리할 수 없을 때
- Sort할 데이터의 양이 많을 때 - SORT_AREA_SIZE
- 8버전: DBA가 직접 설정 필요 (HASH_AREA_SIZE)
- 9버전 이후: WORKAREA_SIZE_POLICY = Auto; DBMS가 자동 지정함
- SORT RUNS: 정렬된 중간결과집합을 임시로 저장하는 Temp 영역의 집합입니다.
👉🏻 PGA vs. UGA
📌 전용서버 방식(Dedicated Server)
- 각 클라이언트 요청에 대해 하나의 서버 프로세스가 전용으로 할당됩니다.
- 하나의 사용자 세션이 하나의 서버 프로세스를 독점적으로 사용합니다. (1:1)
📌 공유서버 방식(Shared Server)
- 여러 클라이언트의 요청이 공유서버프로세스(Shared pool)에 의해 처리됩니다.
- 클라이언트가 서버 프로세스를 공유합니다.
- 전용서버 방식일 경우
- PGA 내에는 CGA, UGA가 포함됩니다.
- CGA: 개별 Call인 진행되는 동안만 임시 작업 데이터 저장.
(개별 Call의 예시: SQL 실행) - UGA: 세션 상태 데이터를 저장, 현재 Call을 넘어 다음 Call까지 지속적으로 참조.
(세션 상태 데이터의 예시: 세션 변수, 커서 상태 등)
| 구분 | PGA | UGA |
| 영역 | Process Global Area | User Global Area |
| 설명 | 하나의 프로세스 또는 백그라운드 프로세스를 위한 메모리 영역 | 사용자 세션과 관련된 메모리 영역 |
| 위치 | 프로세스 메모리 공간에 위치(항상 프로세스에 종속적) | 서버 구성에 따라 UGA가 PGA 또는 SGA에 위치 - 전용서버 방식(Dedicate Server): PGA 내 - 공유서버 방식(Shared Server): SGA 내 |
| 공유여부 | 비공유 (프로세스 전용) | - 전용서버 방식(Dedicate Server): 비공유 - 공유서버 방식(Shared Server): 공유 |
| 메모리 할당과 수명 | - 프로세스 시작 시 할당 - 프로세스 종료 시까지 |
- 세션 시작 시 할당 - 세션 종료 시 까지 |
| 하나의 기준 | 하나의 프로세스는 하나의 PGA를 갖음 | 하나의 세션은 하나의 UGA를 갖음 |
| 정보 관리 | 세션과 독립적인 프로세스만의 정보 관리 즉, 작업 데이터 관리 (정렬, 해시 조인 등) |
프로세스와 독립적인 세션만의 정보 관리 즉, 세션 상태 데이터 관리 (세션 변수, 커서 상태, 패키지 상태 등) |
✏️ 2. 소트를 발생시키는 오퍼레이션
- ⭐⭐⭐ JO GA WU
- Sort Aggregate
- 전체 로우를 대상으로 집계를 수행할 때 나타납니다.
- SORT라는 표현을 사용하지만 실제 소트가 발생하진 않습니다.
select sum(sal), max(sal), min(sal) from emp;
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| EMP | 7013 |
--------------------------------------------
- Sort Order By
- 데이터 정렬을 위해 Order By 오퍼레이션을 수행할 때 나타납니다.
select * from emp order by sal desc;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT ORDER BY | | 7013 | 14336 | 14336 |12288 (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
- Sort Group By
- 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타납니다.
- ⭐⭐⭐ 정렬된 Group By 결과를 얻고자 한다면 "반드시 Order By"를 명시해야 합니다.
- ⭐⭐⭐ 실행계획에 Sort Group By 표시 = 소팅 알고리즘을 사용해 값을 집계한다 != 최종결과의 정렬
- Hash Group By
- 10gR2버전부터 Hash Group By 방식이 도입되었습니다.
- Order By절을 함께 명시하지 않으면 대부분 Hash Group By로 처리됩니다.
- ⭐⭐⭐ Hash Group By는 정렬을 수행하지 않고, 해싱 알고리즘을 사용해 데이터를 그룹핑합니다.
- 즉, 읽는 로우마다 Group By 컬럼의 해시 값으로 버킷을 찾아 그룹별로 집계항목을 갱신합니다.
- 집계할 대상의 로우가 많아도, 그룹 개수가 소수일 때는 두 방식 모두 Disk Sort가 전혀 발생하지 않습니다. (PGA 연산)
- 만약 직원테이블(EMP)에 100만 명의 직원 정보(EMP_ID), 각 직원은 10개 부서(DEPT_ID) 중 하나에 속해 있음
- 이 상태에서 각 부서별 평균급여를 계산하려면? (= 10개 그룹)
SELECT DEPT_ID, AVG(SALARY)
FROM EMP
GROUP BY DEPT_ID; - 여기서 중요한 점은 그룹의 수(=부서의 수)가 적다는 것입니다.
(전체 로우는 100만 개지만, 실제 메모리에 유지해야 하는 그룹은 10개뿐)- SORT GROUP BY
: 정렬 시 100만 개 로우를 모두 메모리에 올릴 필요 없이, 각 부서별로 현재까지의 합계와 처리한 로우의 수만 유지하면 됨 (즉, 10개의 부서에 대한 정보만 메모리에 유지하면 됩니다.) - HASH GROUP BY
: 10개의 해시 버켓만 메모리에 유지하면 됩니다.
- SORT GROUP BY
-- ☆ 정렬된 Group By 결과를 얻으려면 반드시 ORDER BY 필요 ☆
-- SORT GROUP BY: 소팅 알고리즘 -> 그룹별 집계 수행
select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
order by deptno, job;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT GROUP BY | | 4 | 3072 | 3072 | 2048 (0)| -- SORT GROUP BY
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
-- HASH GROUP BY: 해싱 알고리즘 -> 그룹별 집계 수행
select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH GROUP BY | | 4 | 780K| 780K| 481K (0)| -- HASH GROUP BY
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
- Sort Unique
- 주요 목적
- 중복 레코드를 제거하는 것입니다.
- 이는 메인쿼리와 서브쿼리의 조인 결과에서 중복을 없애기 위해 수행됩니다.
- Sort Unique 실행 케이스
- Case 1. Unnesting된 서브쿼리가 M쪽 집합인 경우
- SELECT *
FROM EMP
WHERE DEPT_ID IN (SELECT DEPT_ID FROM DEPT WHERE LOC_ID = 1700); - 여기서 서브쿼리는 여러 개의 DEPT_ID를 반환할 수 있습니다.
- 이 경우 Oracle은 서브쿼리를 Unnesting하여 메인쿼리와 조인을 시도합니다.
- 이 서브쿼리가 M쪽 집합이고 드라이빙한다면 Oracle은 중복을 제거하기 위해 Sort Unique 오퍼레이션을 수행합니다. (1쪽 드라이빙: 세미 조인)
- SELECT *
- Case 2. Unique 인덱스가 없는 경우
- 만약 조인컬럼(위 경우 DEPT_ID)에 Unique 인덱스가 없다면, Oracle은 중복을 제거하기 위해 Sort Unique을 수행합니다.
- UNION: 중복 제거
- DISTINCT: 중복 제거
- Case 3. 세미 조인으로 수행되지 않는 경우
- 세미 조인은 주로 EXISTS나 IN 조건을 사용할 때 발생합니다.
- SELECT DISTINCT d.deptno, d.dname
FROM dept d
WHERE d.deptno IN (SELECT e.deptno FROM emp e WHERE e.sal > 2000)
ORDER BY d.deptno;
- 보통 IN절이 일반적으로 세미조인의 후보가 되지만, 위 SQL은 DISTINCT와 ORDER BY절이 함께 사용되었습니다.
- 이 경우, 중복 제거(DISTINCT)가 필요하고 + 결과를 정렬해야 하고(ORDER BY) + IN 서브쿼리의 결과가 중복될 가능성이 있습니다.
- Sort Unique 연산은 중복 제거 + 정렬을 수행 => 두 가지 목적을 동시에 달성합니다.
- 예외: Sort Unique를 사용하지 않고 Hash Unique를 사용하는 경우
- Oracle 10gR2부터는 Distinct 연산에서도 Order By를 생략하면 => Hash Unique 방식으로 수행됩니다.
- "_convert_set_to_join" = true로 설정하면 집합 연산에서 => Hash Unique 방식으로 수행됩니다.
- Case 1. Unnesting된 서브쿼리가 M쪽 집합인 경우
- 주요 목적
-- 1. Unnesting된 서브쿼리가 M쪽 집합인 경우
select /*+ ordered use_nl(dept) */ *
from dept
where deptno in (select /*+ unnest */ deptno
from emp where job = 'CLERK');
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | | | |
| 3 | SORT UNIQUE | | 6996 | 3072 | 3072 | 2048 (0)|
|* 4 | TABLE ACCESS FULL | EMP | 6996 | | | |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | | | |
------------------------------------------------------------------------------------
-- 2. Unique 인덱스가 없는 경우
-- UNION 연산
select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT UNIQUE | | 7014 | 3072 | 3072 | 2048 (0)|
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | EMP | 7013 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | | |
--------------------------------------------------------------------------------------------
-- DISTINCT 연산
select distinct deptno from emp order by deptno;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT UNIQUE | | 1 | 3072 | 3072 | 2048 (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
-- 예외. HASH UNIQUE 사용하는 경우
-- 1. DISTINCT에서 ORDER BY절 생략
select distinct deptno from emp;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH UNIQUE | | 1 | 1518K| 1518K| 286K (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
-- 2. "_conver_set_to_join" = true
-- 집합 연산: union, union all, minus, intersect
alter session set "_convert_set_to_join" = true;
select job, mgr from emp where deptno = 10
minus
select job, mgr from emp where deptno = 20;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH UNIQUE | | 22 | 1115K| 1115K| 550K (0)|
|* 2 | HASH JOIN RIGHT ANTI | | 5610 | 1594K| 1594K|83968 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | | |
|* 5 | TABLE ACCESS FULL | EMP | 7013 | | | |
--------------------------------------------------------------------------------------------
- Sort Join
- 소트 머지 조인을 수행할 때 나타납니다. (use_merge)
select /*+ordered use_merge(e) */*
from dept d, emp e
where d.deptno = e.deptno;
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MERGE JOIN | | 7013 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | | | |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | | |
|* 4 | SORT JOIN | | 7013 | 14336 | 14336 |12288 (0)|
| 5 | TABLE ACCESS FULL | EMP | 7013 | | | |
------------------------------------------------------------------------------------
-- DEPT 테이블의 인덱스를 사용하였기 때문에(즉, 이미 정렬된 상태이므로)
-- EMP 테이블만 SORT JOIN 연산 발생
- Window Sort
- 분석함수를 수행할 때 나타납니다.
-- 분석함수
SELECT 분석함수(컬럼명)
OVER ([PARTITION BY 그룹 지을 컬럼 LIST]
[ORDER BY 정렬할 컬럼 LIST]
[WINDOWING 절 (Rows|Range|Unbounded Preceding|Unbounded Following|Current Row)]
)
FROM 테이블명
-- WINDOW SORT
select empno, ename, job, mgr, sal
, avg(sal) over (partition by deptno)
from emp;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW SORT | | 114 | 11264 | 11264 |10240 (0)|
| 2 | TABLE ACCESS FULL| EMP | 114 | | | |
-----------------------------------------------------------------------
✏️ 3. 소트 튜닝 요약
- 소트 오퍼레이션
- 메모리 집약적, CPU 집약적, 데이터량이 많을 때는 디스크 I/O까지 발생 시킵니다.
- 특히, 부분범위처리를 할 수 없게 만듭니다.
- 될 수 있으면 소트가 발생하지 않도록 쿼리를 작성해야 하고, 불가피하다면 메모리 내에서 수행하도록 해야 합니다.
- 소트 튜닝2에서 다룰 내용
- 소트가 발생하지 않는 SQL 작성, 인덱스를 이용한 소트 연산 대체, 소트 영역을 적게 사용하는 SQL 작성, 소트 영역 크기 조정
📒 정리하면
⭐⭐⭐ JO GA WU
- 메모리 소트: 할당된 메모리 내에서 정렬 완료
디스크 소트: 디스크 공간까지 사용합니다. - Sort Arregate: 전체 로우를 대상으로 집계할 때 나타납니다. (실제 소트 발생X)
- Sort Order By: 데이터 정렬을 위해 Order By 오퍼레이션 수행 시 나타납니다.
- Sort Group By: 소팅 알고리즘으로 그룹별 집계를 수행합니다.
- Hash Group By
: Oracle 10gR2부터, 해싱 알고리즘을 사용합니다. - 그룹 개수
: 적을 경우, Sort Group By와 Hash Group By 모두 PGA 내에서 연산을 완료할 수 있습니다. (디스크 소트 X)
- Hash Group By
- Sort Unique: 중복 레코드 제거를 위해 사용
- Sort Unique 발생 케이스
: Unnesting된 서브쿼리가 M쪽 집합인 경우, Unique 인덱스가 없는 경우, 세미 조인으로 수행되지 않을 경우 - Hash Unique (ORacle 10gR2부터)
: Distict 연산에서 Order By를 생략하는 경우, "_convert_set_to_join" = true로 설정 후 집합 연산을 하는 경우
- Sort Unique 발생 케이스
- Sort Join: 소트 머지 조인 수행 시, 조인 키로 정렬된 테이블에는 Sort Join이 발생하지 않습니다. (인덱스로 이미 기정렬)
- Window Sort: 분석 함수 수행 시 나타납니다.
반응형
