5장 소트 튜닝

친절한 SQL 튜닝 5장

5.1장 소트 연산에 대한 이해

1. 소트 수행 과정

소트는 기본적으로 PGA에 할당된 Sort Area에서 이루어짐. (최적)
공간이 다 차면 temp 테이블 스페이스 사용.

sort의 두가지 유형

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

디스크 소트가 발생하면 SQL 수행 성능은 나빠진다. 많은 서버 리소스를 사용하고 부분범위 처리도 불가능하기 때문에 애플리케이션 성능을 저하시키는 주요인이 된다. 될 수 있으면 소트가 발생하지 않도록 하고, 필요하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

2. Sort Operation

sort를 발생시키는 오퍼레이션은 다음과 같다.

Sort Aggregate

select sum(sal), max(sal), min(sal), avg(sal) from emp;

전체 로우를 대상으로 집계를 수행할 때 사용된다. 실제로 데이터를 정렬하진 않고, sort area를 사용한다는 의미이다.

Sort Order By

select * from emp order by sal desc;

데이터를 정렬할 때 사용된다.

Sort Group By

select deptno, sum(sal), max(sal), min(sal), avg(sal) from emp group by deptno order by deptno;

소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 사용된다.

Sort Unique

select job, mgr from emp where deptno = 10;

서브 쿼리에서 메인 쿼리와 조인하기 전에 중복 레코드를 제거하는 경우 사용된다.

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

SQL 작성할 때 불필요한 소트가 발생하지 않도록 주의해야 한다. union, minus, distinct 연산자는 중복 레코드를 제거하기 위해 소트 연산을 발생시키므로 꼭 필요한 경우에만 사용하자.

1. Union vs Union All

union: 두 집합 간 중복을 제거하려고 소트 작업을 수행
union all: 두 집합을 단순히 결합

→ 둘 중 사용을 고민한다면, 소트 연산이 없는 union all을 사용하자.

중복이 없는 데이터라면, union all 을 사용해도 union 과 같은 결과를 반환하므로 이런 경우에 사용하면 좋다.

2. Exists

distinct는 조건에 해당하는 데이터를 모두 읽어서 중복을 제거하므로 그 과정에서 많은 i/o가 발생한다.

SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P, 계약 C
 WHERE P.상품유형코드 = :pclscd
   AND C.상품번호 = P.상품번호
   AND C.계약일자 BETWEEN :DT1 AND :DT2
   AND C.계약구분코드 = :CTPCD

SELECT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P
 WHERE P.상품유형코드 = :pclscd
   AND EXISTS ( SELECT 'X' FROM 계약 C
                 WHERE C.상품번호 = P.상품번호
                   AND C.계약일자 BETWEEN :DT1 AND :DT2
                   AND C.계약구분코드 = :CTPCD)

exists 서브쿼리는 데이터 존재 여부만 확인하면 되므로 조건절을 만족하는 데이터를 모두 읽지 않는다. distinct 연산자를 사용하지 않았으므로 상품 테이블에 대한 부분범위 처리도 가능하다.

distinct, minus 연산자를 사용한 쿼리는 대부분 exists 서브쿼리로 변환 가능하므로 기억해두자.

3. 조인 방식 변경

조인 방식에 따라 소트 연산이 바뀌기도 하므로 잘 선택해야 한다.

두 테이블이 NL 조인하도록 조인 방식을 변경하면 소트 연산을 생략해 부분범위 처리 가능한 상황에서 큰 성능 개선 효과를 얻을 수 있다.

예시 : 계약_X01 인덱스 ⇒ [지점 ID + 계약일시]

SELECT C.계약번호, C.상품코드, P.상품명, P.상품구분코드, C.계약일시, C.계약금액
  FROM 계약 C, 상품 P
 WHERE C.지점ID = :brch_id
   AND P.상품코드 = C.상품코드
 ORDER BY C.계약일시 DESC

위의 쿼리 같은 경우는 인덱스를 사용한다고 해도 해시 조인이라 Sort Order by가 발생한다.

SELECT /*+ leading(C) use_nl(P) */
       C.계약번호, C.상품코드, P.상품명, P.상품구분코드, C.계약일시, C.계약금액
  FROM 계약 C, 상품 P
 WHERE C.지점ID = :brch_id
   AND P.상품코드 = C.상품코드
 ORDER BY C.계약일시 DESC

힌트로 정렬 기준을 조인 키로 설정해 인덱스를 사용하여 sort 연산을 생략했다.

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

인덱스를 활용하면 정렬이나 그룹화가 있어도 소트 연산을 생략할 수 있다.

1. Sort Order By 생략

SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
 ORDER BY 거래일시

인덱스 선두 컬럼을 [종목코드+거래일시] 순으로 구성하면 소트 연산을 생략할 수 있다.

부분범위 처리를 활용하여 튜닝하자.

부분범위 처리는 쿼리 수행 결과 중 앞쪽 일부를 우선 전송하고, 남은 데이터를 나눠 전송하는 방식을 말한다. 이 방식은 상위 n개를 조회하는 Top N 쿼리에서 성능을 발휘한다.

2. Top N 쿼리

SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
   AND 거래일시 >= '20180304'
 ORDER BY 거래일시
 LIMIT 10 OFFSET 0;

조건에 해당하는 레코드의 집합을 모두 읽어 거래일시 순으로 정렬한 중간 집합을 만들고, 상위 레코드를 조회하는 방식을 가진다. 여기서 인덱스 선두 컬럼을 [종목코드+거래일시] 순으로 구성한다면, 정렬을 생략할 것이다. 레코드가 아무리 많아도 N개를 읽는 순간 바로 멈춘다는 뜻이다.

Previous4.3장 해시 조인

Last updated 6 days ago