소트 튜닝

소트 연산에 대한 이해

소트 수행 과정

정렬은 기본적으로 PGA의 Sort Area에서 이루어지고, 메모리 공간이 부족하면 디스크의 Temp 테이블스페이스를 활용하고, Sort Area에서 작업을 완료할 수 있는지에 따라 정렬을 두 가지 유형으로 나눈다.

In-Memory Sort(Internal Sort)
전체 데이터의 정렬 작업을 메모리 내에서 완료

To-Disk Sort(External Sort)
디스크 공간까지 사용해 정렬 작업을 완료

정렬은 다음과 같은 순서로 이루어진다.

1. 정렬 대상 집합을 SGA 버퍼캐시를 통해 읽어들인다.
2. Sort Area에서 정렬을 수행한다.
3. Sort Area가 찰 때마다 정렬된 중간집합을 Temp 테이블스페이스에 임세 세그먼트를 만들어 저장한다.
4. 이러한 중간 단계 집합을 Sort Run이라고 한다.
5. Sort Run을 Merge해서 정렬 최종 결과집합을 얻는다.

즉, 정렬 연산은 메모리와 CPU 집약적인 연산이고, 데이터량이 크다면 디스크 I/O까지 발생해 쿼리 성능에 큰 영향을 미친다. 또한 부분범위 처리도 불가능하기 때문에 OLTP 환경에서 성능 저하의 주요 원인이다.

가능한 정렬 연산이 발생하지 않게 쿼리를 작성하고, 불가피하면 메모리 내에서 처리할 수 있게 해야한다.

소트 오퍼레이션

정렬을 발생시키는 연산에 대해 알아보자

Sort Aggregate

실제로 데이터를 정렬하지 않고 Sort Area를 사용하기만 하는 연산으로, 전체 로우를 대상으로 집계를 수행할 때 나타난다. SUM, MAX, MIN, AVG를 구할 때, (SUM, MAX, MIN, COUNT) 변수를 Sort Area에 생성하고, 테이블의 전체 레코드를 읽어가면서 각 변수에 맞게 값들을 갱신해주면 된다.

테이블의 모든 레코드를 읽은 후 각 변수의 값을 그대로 얻거나, SUM에서 COUNT를 나누면 AVG를 구할 수 있기 때문이다. 즉, 정렬은 없고 Sort Area를 사용하기만 했을 뿐이다.

Sort Order By

데이터 정렬 시에 발생하는 연산이다.

Sort Group By

정렬 알고리즘을 사용해 그룹별 집계 시 발생하는 연산이다. 그룹핑 후에 그룹별 집계 데이터를 얻는 과정으로, 데이터 정렬이 추가되기만 하고, 집계 데이터를 구하는 방식은 Sort Aggregate와 같다.

Hash Group By 방식도 존재하는데, Group By 절 뒤에 Order By를 명시하지 않으면 오라클 10gR2 버전부터는 대부분 해당 방식으로 처리한다. 그룹별 집계를 기록하는 공간을 찾는데 정렬 알고리즘을 사용하는 것이 아닌 해싱 알고리즘을 사용한다.

또한 그룹핑 결과는 정렬 순서를 보장하지 않는 것을 조심해야 한다.

Sort Unique

중복을 제거하기 위해 정렬이 발생하는 것으로 다음과 같은 경우 발생한다.

• Unnesting 된 서브쿼리가 M쪽 집합인 경우에 중복 레코드를 제거가 필요한 경우
• 집합 연산자(Union, Minus, Intersect)을 사용하는 경우
• Distinct 연산자를 사용하는 경우(Order By 생략 시 Hash Unique 사용)

만약 PK 혹은 Unique 제약이나 Unique 인덱스를 통해 유일성이 보장된다면 Sort Unique 오퍼레이션 생략이 가능하다.

Sort Join

소트 머지 조인 수행 시 발생한다.

Window Sort

윈도우 함수 수행 시 발생한다.

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

Union vs. Union All

Union은 중복 제거를 위해 정렬 작업을 수행하지만, Union All은 중복 제거 없이 결합하기 때문에 정렬이 발생하지 않기에, 가능한 Union All을 사용해야 한다. 이를 위해서는 Union All을 사용해도 괜찮은지 판단하기 위해 데이터 모델에 대한 이해가 필요하다.

EXIST 활용

중복 제거 시 Distinct 연산자를 사용하면 조건에 해당하는 모든 데이터를 읽은 후 중복을 제거한다. 이러면 부분범위 처리가 불가능하고, 많은 I/O가 발생해 비효율적이다. 반면에 EXIST 서브쿼리는 데이터 존재 여부만 확인해서 조건절을 만족하는 데이터를 모두 읽는 비효율도 없고 부분범위 처리도 가능하다.

Distinct(EXIST), Minus(NOT EXIST) 연산자를 사용한 쿼리는 대부분 EXIST 서브쿼리로 변환가능하다.

조인 방식 변경

예를 들어 Hash Join은 정렬을 보장하지 않기에 정렬 사용 시 정렬 연산 생략이 불가능하지만, 인덱스가 있어서 정렬을 생략할 수 있다면 NL 조인을 사용하도록 조인 방식을 변경하면 정렬 연산을 생략할 수 있다. 물론 정렬 기준이 조인 키 컬럼이면 소트 머지 조인도 정렬 연산을 생략할 수 있다.

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

키 컬럼 순으로 정렬된 상태를 유지하는 인덱스의 특성을 활용해 정렬 연산을 생략할 수 있고, Top N 쿼리 특성을 결합해 OLTP에서 대량 데이터 조회 성능을 향상시킬 수 있다.

Sort Order By 생략

여태까지 많이 봤던 방식으로, 조건절과 정렬에 필요한 컬럼으로 인덱스를 구성해 정렬 연산을 생략하는 방법이다. 인덱스가 적절하게 구성되어 있다면 쿼리에 Order By 연산이 포함되어 있어도 이를 생략할 수 있다. 이미 정렬된 상태기에 당연히 부분범위 처리도 가능하다.

부분범위 처리를 활용한 튜닝 기법, 아직도 유효한가?

최근 DB 애플리케이션은 대부분 3-Tier 환경에서 작동해서 부분범위 처리가 의미 없다고 생각할 수도 있지만, 여전히 부분범위 처리는 유효하게 사용할 수 있고, Top N 쿼리를 사용하면 된다.

Top N 쿼리

전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리다. 인덱스가 적절하지 않다면 인라인 뷰로 정의한 집합을 모두 읽어 정렬 후 상위 N개 레코드를 선택하지만, 적절한 인덱스가 존재하면 정렬 연산을 생략해 N개의 인덱스만 읽고 멈추고, 당연히 부분범위 처리도 가능하다.

적절한 인덱스를 사용해 Top N 쿼리를 날리면 실행계획에서 Top N Stopkey 알고리즘이 사용되어, 조건절에 부합하는 레코드가 아무리 많아도 지정한 N개의 데이터만 읽고 멈춘다.

페이징 처리

오라클은 조회 시 모든 ROW에 1부터 넘버링 하는 것을 이용해 3-Tier 환경에서 부분범위 처리를 할 수 있다.

SELECT *
FROM (
    SELECT 
        ROWNUM no, inline.*
    FROM (
        SELECT * 
        FROM table_name 
        ORDER BY column_name DESC    
    ) inline
    WHERE ROWNUM <= (:page * 10)
)
WHERE no >= (:page - 1) * 10 + 1

보면 알겠지만 인라인 뷰에서는 페이지가 올라갈 때마다 읽는 데이터가 많아지는 단점이 있지만, 보통 사용자는 앞쪽 페이지들만 보기 때문에 큰 문제는 되지 않는다.

페이징 처리 ANTI 패턴

인라인 뷰의 결과 집합을 필터링 하는 ROWNUM 조건절을 쿼리를 간단하게 만들기 위해 메인쿼리의 페이징 조건절과 합쳐 버리면 Top N Stopkey 알고리즘과 Top N 소트 알고리즘을 사용하지 못하게 된다. 그래서 두 조건절을 합치고 쿼리의 실행계획을 보면 N개만 읽는 것이 아닌 전체범위를 읽고 필요한만큼 가져가는 비효율이 발생하는 것을 볼 수 있다.

최소값/최대값 구하기

최소값/최대값은 정렬은 발생하지 않지만, Sort Aggregate라는 테이블 전체를 스캔하면서 값을 비교하는 방식을 사용한다. 하지만 인덱스를 통해 정렬된 데이터가 존재한다면 전체 데이터를 읽지 않고도 처음과 끝 값만 읽으면 최소/최대값을 구할 수 있다.

인덱스를 이용해 최소/최대값을 구하기 위한 조건

조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함돼 있어야, 테이블 액세스 없이 인덱스만 이용해 값을 구할 수 있다. 인덱스가 잘 구성되어 있다면, 실행계획에 First Row Stopkey 알고리즘이 적용된 것을 확인할 수 있다.

Top N 쿼리를 이용해 최소/최대값 구하기

ROWNUM <= 1

위의 조건을 사용해 상위 하나의 레코드만 찾으면 된다. 다른 점이 있다면 필요한 모든 컬럼이 인덱스에 포함돼 있지 않아도 전체 레코드를 읽지 않고 값을 구할 수 있다는 것이다.

인덱스가 제대로 되어있지 않다면 무작정 MIN/MAX 함수를 사용해서 테이블 전체 스캔을 하는 것보다는 상황을 봐서 Top N 쿼리를 사용해보는 것이 성능적으로 더 좋다.

이력 조회

일반 테이블은 현재 값만 저장하기에 변경 전의 값을 알 수 없어, 이를 따로 관리하기 위해 이력 테이블을 따로 관리하기도 한다.

점점 복잡해지는 이력 조회

여러 값을 서브쿼리로 가져와야 하는 경우에 쿼리를 간단하고 편하게 작성하기 위해 인덱스 컬럼을 가공하는 경우가 많은데 이러면 First Row Stopkey 알고리즘을 사용할 수 없다. 차라리 같은 테이블을 여러 번 조인해서, First Row Stopkey 알고리즘을 사용하는 것이 더 효율적일 수도 있다.

하지만 그렇게하면 읽어야할 컬럼이 늘어날 때마다 같은 테이블에 조인이 계속 늘어날 것이다.

INDEX_DESC 힌트 활용

해당 힌트를 사용해 인덱스를 역순으로 읽도록 사용한 후, ROWNUM <= 1 조건을 사용하면 최근 변경 이력 데이터 조회 성능을 향상 시킬 수 있다. 물론 인덱스 구성이 완벽해야 한다.

11g/12c 신기능 활용

분명 어제 읽은 내용인데 기억이 나지 않는 Predicate Pushing을 이용해 11g에서는 인라인 뷰에서 메인쿼리 컬럼을 참조할 수 있다. 12c에서는 애초에 Predicate Pushing 없이도 인라인 뷰에서 참조할 수 있다.

윈도우 함수/Row Limiting

이력 조회 시 인덱스로 정렬 연산을 생략할 수 있다면, 윈도우 함수를 사용하지 않고, Top N Stopkey 알고리즘을 사용하는 것이 더 효율적이다. 12c에 추가된 Row Limiting 절도 윈도우 함수를 사용하는 형태로 쿼리가 변환되어 Top N Stopkey 알고리즘이 작동하지 않는다.

페이징 처리 시 윈도우 함수를 사용하면 Top N Stopkey 알고리즘이 작동할 수 있지만, 카디널리티와 비용 계산이 불완전해 정렬을 생략할 수 있어도 인덱스를 사용하지 않는 경우도 있다. 그래서 페이징 처리 시 해당 방식을 사용할 때는 index/index_desc 힌트를 사용해야 한다.

만약 소트 생략 가능 인덱스가 없어서 Top N 소트 알고리즘 작동 시, 기존 Top N 쿼리보다 윈도우 함수가 Sort Area를 더 많이 사용하는 단점도 있다. Row Limiting도 윈도우 함수 사용과 성능 특성이 똑같다.

상황에 따라 달라져야 하는 이력 조회 패턴

전체 혹은 대량의 이력을 조회할 때는 인덱스를 이용한 Stopkey보다 윈도우 함수를 이용해 테이블 전체 스캔과 해시 조인을 사용하는 것이 인덱스를 이용하는 것보다 더 효율적이다.

선분이력

변경일자가 아닌 시작일자와 종료일자를 모두 사용해서 이력을 관리하는 모델이다. 두 일자를 모두 사용하면 최신 데이터 조회가 더 간편하고, 특정 시점 데이터 조회가 용이하다.

Sort Group By 생략

정렬이 아니라도 그룹핑 연산도 적절한 인덱스가 존재한다면 인덱스를 활용할 수 있고, 실행계획에서 Sort Group By Nosort라는 부분을 발견할 수 있다.

인덱스에서 구간별로 스캔하면서 테이블을 액세스하다 구간이 바뀌는 순간, 현재까지 집계한 값을 운반단위에 저장하는 식으로 모든 구간을 반복하는 식으로 진행한다. 정렬이 발생하지 않기에 당연히 부분범위 처리도 가능하다.

Sort Area를 적게 사용하도록 SQL 작성

정렬 연산이 불가피하면 메모리를 최대한 효율적으로 사용할 수 있게라도 방법을 찾아야 한다.

소트 데이터 줄이기

가공된 결과집합을 정렬하기 보단 가공전에 정렬을 완료하고 최종 출력 때 가공을 하거나, 결과집합을 정렬해야 한다면 최대한 불필요한 컬럼을 줄이고 정렬하는 것이 데이터를 줄일 수 있는 방법이다.

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

인덱스로 소트 연산을 생략할 수 없으면 Top N 소트 알고리즘은 다음과 같이 동작한다.

1. 처음 N개의 데이터를 정렬시킨다.
2. 나머지 데이터를 순서대로 기존 N개의 데이터 중 N번째 데이터와 비교한다.
3. N번째 데이터보다 크다면 둘을 교체한다.(오름차순이면 작은 경우에 교체)
4. N개의 데이터를 재정렬한다.
5. 2번부터의 과정을 반복한다.

해당 연산이 사용된 것은 실행계획에서 SORT ORDER BY STOPKEY 부분을 확인하면 된다. 정렬 시 N개의 배열 공간만 사용하기 때문에 Sort Area를 최소한으로 사용할 수 있고, 정렬 연산 횟수를 줄일 수 있다.

Top N 쿼리가 아닐 때 발생하는 소트 부하

이전에 살펴봤던 것처럼 쿼리를 간결하게 표현하기 위해 ROWNUM 조건절을 제거하면 Stopkey를 사용할 수 없어, Top N 소트 알고리즘을 사용할 수 없다. 그러면 Top N 소트 알고리즘의 장점인 Sort Area를 효율적으로 사용하는 방식을 사용할 수 없기 때문에 디스크를 사용해서 정렬을 할 확률이 높아진다.

분석함수에서의 Top N 소트

rank와 row_number 함수는 Top N 소트 알고리즘이 사용되어 max 함수보다 정렬 부하가 적다.