SQL 수행 도중 가공된 데이터 집합이 필요할때, 오라클은 PGA나 Temp Tablespace를 사용.
그 대표적인 예가 소트머지 조인, 해시조인, 데이터 소트와 그룹핑이다.
소트 수행 과정
- 소트는 기본적으로 PGA에 할당된 Sort Area에서 이루어짐.
- 그 공간이 다차면 Temp 테이블 스페이스 사용.
소트의 2가지 유형
- 메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말함. (=Internal Sort)
- 디스크 소트 : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것을 말함 (=External Sort)
디스크 소트 과정
- 소트할 대상을 SGA 버퍼캐시를 통해 읽고 Sort Area에서 정렬 시도.
- 양이 많으면 중간 집합을 Temp 테이블스페이스에 임시 세그먼트 만들어 저장.
(Temp 영역에 저장해둔 중간 단계 집합을 Sort Run 이라고 한다.) - 정렬된 최종 결과 집합을 얻으려면 다시 Merge 해야함.
소트연산은 메모리 집약적일 뿐만 아니라 CPU 집약적이기도 함.
처리할 데이터량이 많으면 디스크 I/O까지 발생해 쿼리 성능을 좌우한다.
많은 서버 리소스를 사용하고 디스크 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가하게 만들어 OLTP 환경에서 애플리케이션 성능을 저하시키는 주 요인이다.
그래서 소트가 발생하지 않도록 하고, 소트가 불가피하면 메모리 내에서 수행을 완료하게 하자!
소트 오퍼레이션
Sort Aggregate
전체 로우를 대상으로 집계를 수행할 때 나타난다. 하지만 실제로 데이터를 정렬하지 않고 Sort Area만 사용함.
select sum(sal), max(sal), min(sal), avg(sal) from emp;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------데이터를 정렬하지 않고 SUM, MAX, MIN, AVG 값을 구하는 절차
- Sort Area에 SUM, MAX, MIN, COUNT 값을 위한 변수를 각각 하나씩 할당함.
- EMP 테이블 첫번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN 변수에 저장하고 COUNT 변수는 1을 저장.
- EMP 테이블에서 레코드를 하나씩 읽어나가며 아래 동작을 수행
- SUM 변수에 값을 누적
- MAX 변수는 기존보다 큰 값이 나타날때 값 기록
- MIN 변수는 기존보다 작은 값이 나타날때 값 기록
- COUNT 변수는 SAL 값이 NULL이 아닐때마다 1씩 증가.
- EMP 레코드를 다 읽으면 SUM, MAX, MIN, COUNT 변수에 각 값이 저장되어 있음.
SUM, MAX, MIN는 그대로 출력. AVG는 SUM / COUNT 하여 출력
Sort Order By
데이터를 정렬할때 사용. 정렬과정은 디스크 소트로 설명함.
SELECT * FROM EMP ORDER BY SAL DESC;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 518 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------Sort Group By
소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 사용.
SELECT DEPTNO, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 11 | 165 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------예시
💡 수천명의 사원이 근무하는 회사를 가정.
부서는 4개. 부서코드는 10, 20, 30, 40을 사용한다.
컴퓨터의 도움을 받지 않고 SAL을 집계하라고 해보자.
각 4개의 부서에 SUM, MAX, MIN, COUNT을 기록할 수 있도록 환경을 만들어준다.
DBMS는 Sort Group by를 처리 할 때, Sort Aggregate에서 사용한 방식을 각 부서에 사용한다.
그래서 부서의 수가 많지 않다면 Sort Area가 클 필요도 없고, Temp 테이블 스페이스를 사용하지 않는다.
오라클 10gR2 버전에선 Hash Group by를 사용한다고 함. 책 참고.
(얘네는 버전도 특이해)
Sort Unique
옵티마이저가 서브쿼리를 풀어 일반 조인으로 변환하는게 서브쿼리 Unnesting인데,
Unnesting된 서브쿼리가 M쪽 집합이면 메인쿼리와 조인하기전에 중복 레코드부터 제거해야 함.
이때 Sort Unique 오퍼레이션 두둥등장!
SELECT /*+ ORDERED USE_NL(DEPT) */ * FROM DEPT
WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO
FROM EMP
WHERE JOB = 'CLERK');
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 87 | 4 (25) |
| 1 | NESTED LOOPS | | 3 | 87 | 4 (25) |
| 2 | **SORT UNIQUE** | | 3 | 33 | 2 (0) |
| 3 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 33 | 2 (0) |
| 4 | TABLE RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0) |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 18 | 1 (0) |
| 6 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0) |
---------------------------------------------------------------------------------- 만약 서브쿼리의 컬럼이 PK 또는 Unique인 경우 SORT UNIQUE 오퍼레이션은 생략
- Union, Minus, Intersect 같은 집합(Set) 연산자를 사용할때도 Sort Unique 두둥등장
- Distinct 연산자를 사용해도 Sort Unique 두둥등장
Sort Join
소트 머지 조인을 수행할때 사용한다.
SELECT /*+ ORDERED USE_MERGE(E) */ *
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 770 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 72 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 72 | 3 (0)| 00:00:01 |
| 4 | SORT JOIN | | 14 | 518 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------Window Sort
윈도우 함수(=분석 함수)를 사용할 때 나타난다.
SELECT EMPNO, ENAME, JOB, MGR, SAL,
AVG(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 406 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 406 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 406 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
뭉실뭉실 코더 운구름