소트 연산
SQL 수행 도중 가공된 데이터 집합이 필요할 때 오라클은 PGA 와 TEMP 테이블 스페이스를 활용한다 소트 머지 조인, 해시 조인 이 대표적이다
소트 수행 과정
소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어진다 메모리 공간인 Sort Area 가 다 차면 디스트 TEMP 테이블 스페이스를 활용한다 그래서 소트는 총 2가지 소트가 있다
메모리 소트 - 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며 Internal Sort라고 한다
디스크 소트 - 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며 External Sort라고 한다
소트 과정
소트 대상 집계 - 소트 할 대상을 SGA 버퍼캐시를 통해 읽어들임
PGA 소트 - 일차적으로 Sort Area에서 정렬을 시도 이때 Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이지만 양이 많을 때는 Temp tabelspace로 넘긴다
Temp Tablespace - PGA 소트가 완료되지 못하면 임시 세그먼트를 만들어서 저장한다
MERGE - 이는 정렬된 최종 결과 집합을 얻기 위한 작업인데 만약 오름차순 정렬이면 각각에서 가장 작은 값으로부터 PGA로 읽어들이면서 PGA 가 찰 때마다 쿼리 수행 다음 단계로 전달하거나 클라이언트에 전달
소트 연산 성능
소트 연산은 메모리 집약적일 뿐만 아니라 CPU 집약적이기도 하다 처리할 데이터양이 많으면 많을수록 디스트 I/O까지 발생하므로 쿼리 성능을 좌우하는 매우 중요한 요소이다 디스크 소트까지 발생하면 SQL 수행 성능은 나빠질 수밖에 없다 많은 리소스를 잡아먹는 것과 동시에 부분 범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이 되기도 한다 그렇기에 소트 연산이 발생하지 않는 것이 좋으며 불가피하다면 메모리 내에서 처리할 수 있도록 해야 한다
SORT AGGREGATE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXPLAIN PLAN FOR
SELECT SUM(sal) , MAX(sal) , MIN(sal) , AVG(sal) FROM EMP
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------+
Plan hash value: 2083865914 |
|
---------------------------------------------------------------------------|
| 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 ||
---------------------------------------------------------------------------|
소트 연산이 동작했는지 아닌지는 바로 SORT AGGREGATE 오퍼레이션이 있으면 소트 연산이 진행되었다는 것을 알 수 있다 하지만 지금 쿼리는 실제로 정렬을 위한 소트는 아니다 하지만 집계 쿼리를 사용할 때는 이렇게 정렬이 필요하다고 생각해서 소트 연산을 수행하게 됩니다
내부적으로는 이렇게 처리됩니다 EP 테이블에서 레코드를 하나씩 읽어 내려가면서 SUM 변수에는 값을 누적하고 MAX 변수에는 값을 비교하면서 더 큰 값이 나오면 저장하고 그렇지 않으면 이전 값을 계속 가지고 갑니다 MIN 은 MAX의 반대 메커니즘입니다 그리고 COUNT는 NULL 이 아닌 레코드를 만날 때마다 값을 1씩 누적하고 SUM 합계의 결과를 COUNT 합계 결과로 나누어서 AVG를 만들어냅니다
SORT ORDER BY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
EXPLAIN PLAN FOR
SELECT * FROM EMP
ORDER BY SAL DESC
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------+
Plan hash value: 150391907 |
|
---------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 14 | 532 | 4 (25)| 00:00:01 ||
| 1 | SORT ORDER BY | | 14 | 532 | 4 (25)| 00:00:01 ||
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------|
정렬할때 나타나는 오퍼레이션 SORT ORDER BY 입니다
SORT GROUP BY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXPLAIN PLAN FOR
SELECT DEPTNO , SUM(SAL) , MAX(SAL) , MIN(SAL) , AVG(SAL) FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------+
Plan hash value: 15469362 |
|
---------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 ||
| 1 | SORT GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 ||
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------|
소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다
먼저 DEPTNO로 정렬을 먼저 시작을 한다
정렬된 DEPTNO를 따라 내려가면서 SORT AGGREGATE 과 동일한 알고리즘으로 SUM(SAL) , MAX(SAL) , MIN(SAL) , AVG(SAL) 집계를 한다
이는 아무리 많은 데이터라도 부서 그룹 자체가 많지 않다면 Sort Area 가 클 필요가 전혀 없다 하지만 데이터가 많으면 많을수록 정렬할 때 많은 비용을 쏟아붓는다
HASH GROUP BY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXPLAIN PLAN FOR
SELECT DEPTNO , SUM(SAL) , MAX(SAL) , MIN(SAL) , AVG(SAL) FROM EMP
GROUP BY DEPTNO
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------+
Plan hash value: 4067220884 |
|
---------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 ||
| 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 ||
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------|
이는 정렬하지 않고 매번 새로운 DEPTNO 나올 때마다 바구니(SUM(SAL) , MAX(SAL) , MIN(SAL) , AVG(SAL) 담아둘)를 준비한다
정렬되지 않은 데이터를 읽어 내려가면서 ROW의 DEPTNO를 바라본다
만약 새로운 번호의 DEPTNO라면 바구니를 새롭게 만들고 그곳에 SORT AGGREGATE 메커니즘을 동작시킨다
ROW 별로 읽다가 기존의 DEPTNO 가 나오면 해시값을 읽어서 동일한 해시값의 바구니를 찾아서 그곳에 넣고 SORT AGGREGATE 메커니즘을 동작시킨다
역시 부서 데이터가 많지 않다면 Sort Area 가 클 필요가 없으며 이는 대용량 데이터라고 해서 정렬 비용이 필요하지 않습니다
Sort Unique
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
EXPLAIN PLAN FOR
SELECT job , mgr FROM EMP WHERE DEPTNO = 10
UNION
SELECT job , mgr FROM EMP WHERE DEPTNO = 20
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------+
Plan hash value: 3774834881 |
|
----------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 8 | 120 | 8 (25)| 00:00:01 ||
| 1 | SORT UNIQUE | | 8 | 120 | 8 (25)| 00:00:01 ||
| 2 | UNION-ALL | | | | | ||
|* 3 | TABLE ACCESS FULL| EMP | 3 | 45 | 3 (0)| 00:00:01 ||
|* 4 | TABLE ACCESS FULL| EMP | 5 | 75 | 3 (0)| 00:00:01 ||
----------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - filter("DEPTNO"=10) |
4 - filter("DEPTNO"=20) |
오라클이 결과 집합에서 중복을 제거하기 위해 정렬을 수행하는 작업을 말합니다
Sort Join
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_MERGE(e)*/
* FROM DEPT d , EMP e
WHERE d.DEPTNO = e.DEPTNO
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------+
Plan hash value: 1407029907 |
|
----------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 14 | 812 | 8 (25)| 00:00:01 ||
| 1 | MERGE JOIN | | 14 | 812 | 8 (25)| 00:00:01 ||
| 2 | SORT JOIN | | 4 | 80 | 4 (25)| 00:00:01 ||
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 ||
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 ||
| 5 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 ||
----------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
4 - access("D"."DEPTNO"="E"."DEPTNO") |
filter("D"."DEPTNO"="E"."DEPTNO") |
Merge Join 을 수행하기 전에 조인 대상 테이블의 데이터를 정렬해야 할 경우 즉 우리 앞에서 본 머지 조인에서 조인 키값을 기준으로 정렬할 때 나타나는 과정입니다
Window Sort
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
EXPLAIN PLAN FOR
SELECT empno , ename , job , mgr , sal , AVG(sal) OVER(PARTITION BY deptno)
FROM EMP
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------+
Plan hash value: 3145491563 |
|
---------------------------------------------------------------------------|
| 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 ||
---------------------------------------------------------------------------|
분석 함수를 처리하기 위해 내부적으로 데이터를 정렬하는 작업을 말합니다 필요한 이유는 범위지정 frame specification 필수이기 때문입니다 지금은 deptno 파티션을 나누었기 때문에 모든 데이터를 먼저 deptno 순으로 정렬을 해야 하는데 이때 사용되는 오퍼레이션이 Window Sort입니다