소트를 발생시키는 오퍼레이션
Oracle 실행계획에 나타나는 오퍼레이션 형태를 기준으로 설명한다.
Sort Aggreation
전체 로우를 대상으로 집계를 수행할 때 나타난다.
실제 소트를 하진 않으며, SQL Server에선 Stream Aggregate라고 표시된다.
select sum(sal), max(sal), min(sal) from emp
Execution Plan
-------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)
1 0 SORT (AGGREGATE) (Card=1 Bytes=4)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=56)Sort Order By
결과 집합을 단순히 정렬했을 때 나타나는 오퍼레이션이다.
select * from emp order by sal desc
Execution Plan
-------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=518)
1 0 SORT (ORDER BY) (Cost=4 Card=14 Bytes=518)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)Sort Group By
Sorting 알고리즘을 사용해서 그룹별 집계를 수행할 때 나타난다.
select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
Execution Plan
------------------------------------
-------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165)
1 0 SORT (GROUP BY) (Cost=4 Card=11 Bytes=165)
2 1 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210) 왜 Sorting 알고리즘을 사용하는 게 하이라이팅 됐냐면, 그룹화 하는 데 sorting 뿐 아니라 hashing으로도 가능하기 때문이다.
hashing으로 하면 다음과 같이 나타난다.
Execution Plan
-------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165)
1 0 HASH (GROUP BY) (Cost=4 Card=11 Bytes=165)
2 1 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210)id = 1에서 SORT (GROUP BY) 대신 HASH (GROUP BY)가 된 게 보인다.
Sort Unique
선택된 결과집합에서 중복 레코드를 제거할 때 나타난다.
Union 연산자나 distinct 를 쓸 때가 대표적이다.
select distinct deptno from emp order by deptno
Execution Plan
-------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=6)
1 0 SORT (UNIQUE) (Cost=4 Card=3 Bytes=6)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=26)sort unique는 서브쿼리 unnesting 하면서 서브쿼리 블록과 메인쿼리 집합을 조인할 때, 서브쿼리의 결과와 메인쿼리 간 pk/fk 제약이 없거나 서브쿼리에 unique index가 존재하지 않아서 서브쿼리(inner 테이블)의 unique함이 보장되지 않고 따라서 1쪽 집합임이 확실하지 않을 때 드라이빙 테이블의 조인 칼럼값을 unique 하게 만들기 위해 사용되기도 한다.
select *
from emp
where deptno in (
select deptno
from dept
);
-- 다음과 같이 변환된다.
select b.*
from (
select /*+ no_merge */ distinct deptno
from dept
order by deptno
) a, emp b
where b.deptno = a.deptno
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 440 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 4 | 148 |
| 2 | NESTED LOOPS | | 11 | 440 |
| 3 | SORT UNIQUE | | 4 | 12 |
| 4 | INDEX FULL SCAN | DEPT_DEPTNO_IDX | 4 | 12 |
|*5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | |
-----------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------
5 - access("DEPTNO"="DEPTNO")자세한 것은 여기를 참조하면 좋다.
Sort Join
Sort Merge Join을 수행할 때 나타난다.
Window Sort
윈도우 함수를 수행할 때 나타난다.
select
empno, ename, job, mgr, sal,
row_number() over (order by hiredate)
from emp;
Execution Plan
-------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=364)
1 0 WINDOW (SORT) (Cost=4 Card=13 Bytes=364)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=364)소트 튜닝
소트 오퍼레이션을 할 때는 PGA의 소트 영역을 할당해서 거기서 소트를 한다.
소트에 필요한 로우를 다 옮기기 때문에, 소트 영역에 다 안 들어가면 디스크를 써야 한다.
디스크를 쓰게 되면 속도는 매우 나빠진다.
전체 데이터의 정렬 작업을 소트 영역 내에서 수행하는 것을 In-Memory Sort, Internal Sort, Optimal Sort라고 하고, 메모리가 부족해서 메모리에 못 담는 일부를 디스크로 가져가서 소팅하는 것을 To-Dist Sort, External Sort라고 한다.
To-Disk Sort의 경우, 초과되는 데이터를 디스크에 임시로 기록하는 횟수에 따라 Onepass, Multipass Sort로 나눈다.
소트 오퍼레이션은 메모리 집약적일 뿐만 아니라 CPU 집약적이기도 하다. 위와 같이 메모리(소트 영역)을 벗어나는 양이 들어오면 디스크 I/O까지 발생하므로 쿼리 성능을 크게 떨어뜨린다.
소트는 되도록이면 발생하지 않도록 잘 SQL을 짜야하고, 불가피하다면 최소한 메모리 내에서 수행되도록 해야 한다.
데이터 모델 측면에서의 검토
자주 사용되는 데이터 액세스 패턴을 고려하지 않고 설계를 진행하거나 M:M 관계의 테이블을 해소하지 않아서 핵심 프로그램이 맨날 소트하게 되면 성능이 좋지 않다.
위 테이블의 PK는 상품라인이다.
함수는 아래와 같다.
X(상품라인) -> Y(고객, 상품id, 가입일시, 상태코드)
X(고객,상품id) -> Y(가입일시)
하지만 위와 같이 가입일시의 경우 고객과 상품id만으로 알 수가 있다.
따라서 제2정규화를 해서 테이블을 '고객,상품id,가입일시'의 가입상품 테이블과 상품라인-상태코드를 나타내는 테이블로 나누면 위와 같이 된다.
고객별 가입상품 레벨에서 데이터 조회가 발생한다면, 즉 고객1이 상품A에 지불한 금액의 총액을 조회한다고 하자.
정규화를 하지 않았더라면 고객, 상품 단위로 group by를 해야 했을 것이다.
정규화를 한 후에는 가입상품 테이블과 과금 테이블만 보면 되기 때문에 그렇지 않아도 된다.
소트가 발생하지 않도록 SQL 작성
Union을 Union All로 대체
select empno, job, mgr from emp where deptno = 10
union
select empno, job, mgr from emp where deptno = 20;위 쿼리는 or연산을 union으로 대체한 쿼리이다.
기본적으로, 10 or 20을 한다면 table full scan을 해볼 것이다.
하지만 In-list나 union을 쓴다면 각각 10과 20으로 index range scan 하고 결과를 합칠 수 있다.
그런데 여기서 union 대신 union all 을 쓰면 더 좋다. 왜냐하면 union을 쓰면 오라클은 중복값을 제거하기 위해 sort unique 오퍼레이션을 실행하기 때문이다.
union all 을 쓰면 중복을 허용하면서 결과 집합을 합치므로 소트를 안 한다.
위 경우에 deptno가 10이면서 20일 수가 없으므로 중복은 당연히 없다. 그래서 union all 을 쓰나 union 을 쓰나 결과는 똑같다. union 을 쓰면 불필요한 일만 더 하는 셈이다.
Distinct를 Exists 서브쿼리로 대체
distinct를 exists 서브쿼리로 대체할 수 있다.
예를 들어, 아래는 특정 지역에서 특정 월 이전에 과금이 발생했던 연월을 조회하는 쿼리이다.
select distinct 과금연월
from 과금
where 과금연월 <= :yyyymm
and 지역 like :reg || '%'이는 아래로 대체될 수 있다.
select 연월
from 연월테이블 a
where 연월 <= :yyyymm
and exists (
select 'x'
from 과금
where 과금연월 = a.연월
and 지역 like :reg || '%'
)exists 서브쿼리는 메인쿼리에서 값을 입력받아서 (여기선 연월), 필터링을 하고 (지역 like ...) 두 조건을 만족하는 레코드가 있으면 바로 true를 반환한다.
불필요한 count 연산 제거
데이터 존재여부만 확인하면 되면 count 할 필요 없이 exists를 쓰면 된다.
인덱스를 이용한 소트 연산 대체
결합 혹은 단일 인덱스의 칼럼으로 order by 가 들어가면 sort order by를 대체할 수 있다. 선두 인덱스 칼럼으로 group by 되면 이것도 소트 없이 할 수 있다. 이미 정렬되어 있기 때문이다.
또한 MIN, MAX도 레코드 전체를 읽지 않고도 가능하다. FIRST ROW 만 보면 되기 때문이다.
물론 인덱스를 가공 안 했을 때의 이야기이다.
소트 영역을 적게 사용하도록 SQL 작성
소트 완료 후 데이터 가공
-- 1번
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10)
|| lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from 주문상품
where 주문일시 between :start and :end
order by 상품번호
-- 2번
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10)
|| lpad(상품명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from (
select 상품번호, 상품명, 고객ID, 고객명, 주문일시
from 주문상품
where 주문일시 between :start and :end
order by 상품번호
)상품번호 상품명 고객ID 고객명 주문일시 로 이루어진 일정한 양식의 Key값을 뽑으려는 쿼리이다.
차이점은, 1번은 그 key값을 양식에 맞게 죄다 공백을 채워넣은 문자열을 정렬하고, 2번은 각 값을 정렬한 다음에 양식에 맞춘다는 점이다.
소트 공간에는 2번이 훨씬 더 공간을 덜 차지 한다.
Top N 쿼리
Top N 쿼리는 위에서 N개만 뽑는 쿼리이다.
SQL 같은 경우엔 select 다음에 top n 만 적어주면 된다.
select top 10 거래일시, 체결건수, 체결수량, 거래대금
from 시간별종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20080304'select * from (
select 거래일시, 체결건수, 체결수량, 거래대금
from 시간별종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20080304'
order by 거래일시
)
where rownum = 10오라클은 인라인 뷰로 한번 감싸야 한다.
만약에 종목코드 거래일시로 인덱스가 있으면 소트 없이도 가능하다.
근데 종목코드 + 다른 칼럼이 인덱스라면 정렬이 불가피하다. 하지만 top n 쿼리 알고리즘이 동작해서 그나마 소트 영역을 최소한으로 사용할 수 있다.
Top N이면 N개 공간을 할당해서 소트 안 하고 위에서부터 N개를 일단 담고 정렬한다.
그리고 N+1번 레코드부터 읽으면서, 소트 영역의 맨 뒤, 그러니까 가장 큰 값이랑 비교해가면서 것보다 작은 데이터라면 이를 대체하고 다시 정렬한다.
예를 들어 Top5이고 맨 위 데이터가 4, 7, 10, 3, 1이라면 최초로 소트 영역에는 1, 3, 4, 7, 10이 들어가게 된다.
6번째 데이터가 13이라면 13은 10보다 크기 때문에 아무 일도 안 한다. top5이기 때문에 어차피 결과에 포함 안 될 것이기 때문이다.
그 다음 7번째 데이터가 5이라면, 5은 10보다 작기 때문에 10을 5로 대체한다.
그럼 1, 3, 4, 7, 5가 되고 이를 다시 정렬해서 1, 3, 4, 5, 7이 된다.
이를 전체 데이터를 읽으면 소트 영역에 전체 데이터를 안 옮기고도 정렬할 수 있다.
윈도우 함수를 이용해서 집계를 할 경우, max를 쓰는 것보다 rank나 row_number를 함수를 쓰는 것이 유리하다.
소트 영역 크기 조정
오라클은 9i부터는 PGA 메모리가 자동으로 관리된다.
이를 수동으로 바꾸기 위해선 workarea_size_policy를 manual로 변경하고 sort_area_size를 바꿔줄 수 있다.
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 10485760;