[SQL] 튜닝 - 소트 튜닝🔥

소트 튜닝(Sort Tunning)

앞서 작성한 글을 참고하면 알 수 있듯이

소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어진다.

Sort Area가 다 차면, 디스크 Temp를 활용한다.

 

Sort Area에서 작업을 완료할 수 있는지에 따라 소트를 두가지 유형으로 나눌 수 있다.

• 메모리 소트
  전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것 (Internal Sort)
• 디스크 소트
  할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우 (External Sort)

소트 연산은 메모리 집약적일 뿐 아니라 CPU 집약적이다.

많은 서버 리소스를 사용하고 디스크I/O가 발생하는 것도 문제지만

부분범위 처리를 불가능하게 함으로써 OLTP환경에서 애플리케이션의 성능을 저하시키는 주요인이 되기도 한다.

 

따라서, 가능하면 소트가 발생하지 않게 SQL을 작성하고, 소트가 불가피하다면 메모리 내에서 수행할 수 있도록 해야한다.

---

소트 오퍼레이션

소트를 발생시키는 오퍼레이션

1. Sort Aggregate

• 전체 로우를 대상으로 집계를 수행할 때 나타나는데, 실제 소트가 이루어지지는 않는다.

2. Sort Order By

• 데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.

3. Sort Group By

• 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.
• 오라클 10gR2버전부터 Hash Group By 방식이 도입되면서
  order by절을 함께 명시하지 않으면 대부분  Hash Group By 방식으로 되며 소트를 하지 않음

4. Sort Unique

• 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없을 때 Unnesting 되어 조인으로 풀릴 때 Sort Unique 오퍼레이션 수행
  (메인쿼리와 조인하기 전 중복 레코드부터 제거)
• 만약 서브쿼리의 컬럼이 PK 또는 Unique인 경우 Sort Unique 오퍼레이션은 생략
• Union, Minus, Intersect, Distinct 구문도 마찬가지로 Sort Unique 오퍼레이션이 나타남

5. Sort Join

• Sort Join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.

6. Window Sort

• Window Sort는 분석함수를 수행할 때 나타난다.

---

소트가 발생하지 않도록 SQL작성

Union vs. Union All

• Union은 중복을 제거하기 위해 소트작업 수행
• Union All은 중복을 확인하지 않고 단순히 결합하므로 소트작업 수행x (가능하면 Union All 사용)

/* 중복을 제거하기 위해 소트 수행 */
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  결제일자 = '20180316'
UNION
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  주문일자 = '20180316'

Execution Plan
------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=106)
1   0   SORT (UNIQUE) (Cost=2 Card=2 Bytes=106)
2   1    UNION-ALL
3   2     FILTER
4   3      TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 ...)
5   4       INDEX (RANGE SCAN) OF '결제_N2' (INDEX) (Cost=0 Card=1)
6   5     FILTER
7   6      TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 ...)
8   7       INDEX (RANGE SCAN) OF '결제_N3' (INDEX) (Cost=0 Card=1)

/* 소트가 발생하지 않도록 UNION ALL을 사용하면서 데이터 중복을 피하기 */
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  결제일자 = '20180316'
UNION ALL                                  /* UNION ALL로 변경 */
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  주문일자 = '20180316'
and    결제일자 <> '20180316'              /* 중복을 제거해주는 조건절 */

Execution Plan
------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=2 Bytes=106)
1   0    UNION-ALL
2   1     FILTER
3   2      TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 Card=1 ...)
4   3       INDEX (RANGE SCAN) OF '결제_N2' (INDEX) (Cost=0 Card=1)
5   4     FILTER
6   5      TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 Card=1 ...)
7   6       INDEX (RANGE SCAN) OF '결제_N3' (INDEX) (Cost=0 Card=1)

Exist 활용

Exist 서브쿼리는 데이터 존재 여부만 확인되면 되기 때문에 조건절을 만족하는 데이터를 모두 읽지 않는다. (성능 향상)

 

Exist 활용 예

/* DISTINCT는 소트 발생 */
select DISTINCT p.상품번호, p.상품명 p.상품가격, ...
from   상품 p, 계약 c
where  p.상품유형코드 = :pclscd
and    c.상품번호 = p.상품번호
and    c.계약일자 between :dt1 and :dt2
and    c.계약구분코드 = :ctpcd

Execution Plan
------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=80)
1   0   HASH (UNIQUE) (Cost=3 Card=1 Bytes=80)
2   1    FILTER
3   2     NESTED LOOPS
4   3      NESTED LOOPS (Cost=2 Card=1 Bytes=80)
5   4       TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=1 ...)
6   5        INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=1 Card=1)
7   6       INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=1 Card=1)
8   7      TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE) (Cost=1 ...)

/* Exist 활용, 소트x */
select p.상품번호, p.상품명, p.상품가격, ...
from   상품 p
where  p.상품유형코드 = :pclscd
and    EXIST (select 'x' from 계약 c
              where  c.상품번호 = p.상품번호
              and    c.계약일자 between :dt1 and :dt2
              and    c.계약구분코드 = :ctpcd)
              
Execution Plan
------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=80)
1   0   FILTER
2   1    NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=80)
3   2     TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=1 ...)
4   3      INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=1 Card=1)
5   4     TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE) (Cost=1 ...)
6   5      INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=1 Card=1)

NOT EXIST 활용 예

select st.상황접수번호, st.관제일련번호, st.상황코드, st.관제일시
from   관제진행상황 st
where  상황코드 = '0001'   -- 신고접수
and    관제일시 between :v_timefrom || '000000' and :v_timeto || '235959'
MINUS
select st.상황접수번호, st.관제일련번호, st.상황코드, st.관제일시
from   관제진행상황 st, 구조활동 rpt
where  상황코드 = '0001'
and    관제일시 between :v_timefrom || '000000' and :v_timeto || '235959'
and    rpt.출동센터ID = :v_cntr_ID
and    st.상황접수번호 = rpt.상황접수번호
order by 상황접수번호, 관제일시


/* MINUS 연산자를 NOT EXIST 서브쿼리로 변환 */
select st.상황접수번호, st.관제일련번호, st.상황코드, st.관제일시
from   관제진행상황 st
where  상황코드 = '0001'   -- 신고접수
and    관제일시 between :v_timefrom || '000000' and :v_timeto || '235959'
and    NOT EXIST (select 'x' from 구조활동
                  where  출동센터ID = :v_cntr_ID
                  and    상황접수번호 = st.상황접수번호)
order by st.상황접수번호, st.관제일시

---

인덱스를 이용한 소트 연산 생략

Top N 쿼리

전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리

select TOP 10 거래일시, 체결건수, 체결수량, 거래대금
from   종목거래
where  종목코드 = 'KR123456'
and    거래일시 >= '20180304'
order by 거래일시

오라클에서는 인라인 뷰로 한번 감싸야하는 불편함이 있다.

select * from (
   select 거래일시, 체결건수, 체결수량, 거래대금
   from   종목거래
   where  종목코드 = 'KR123456'
   and    거래일시 >= '20180304'
   order by 거래일시
)
where rownum <= 10

---

Sort Area를 적게 사용하도록 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 상품번호

/* 인라인뷰에서 한번 정렬 후 합침 (SortArea를 더 적게 사용) */
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 상품번호
  )

2. Top N 쿼리의 소트 부하 경감 원리

 

1. 전교생을 운동장에 집합시킨다.
2. 맨 앞줄 맨 왼쪽에 있는 학생 열 명을 단상 앞으로 불러 다음 그림처럼 키 순서대로 세운다.
3. 나머지 990명을 한 명씩 교실로 들여보내면서 현재 TOP  10 위치에 있는 학생과 키를 비교한다.
   더 큰 학생이 나타나면, 현재 TOP 10 위치에 있는 학생을 교실로 들여보낸다.
4. TOP 10에 새로 진입한 학생 키에 맞춰 자리를 재배치한다.기존에 10명이 정렬되어 있어서 빠르다.

• 정렬되어 있을 때 새로운 데이터가 들어올 경우 삽입정렬