소트 튜닝

[ 소트 수행 과정 ]

소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어진다. Sort Area에서 작업을 완료할 수 있는지에 따라  소트를 두가지 유형으로 나눈다.

1. 메모리 소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, 'Internal Sort'라고도 한다.

2. 디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, 'External Sort'라고도 한다.

메모리 소트 과정

소트할 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고 일차적으로 Sort Area에서 정렬을 시도한다. Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이지만, 양이 많을 때는 정렬된 중간집합을 Temp 테이블스페이스에 임시 세그먼트를 만들어 저장한다. Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 Sort run이라고 한다. 정렬된 최종 결과집합을 얻기 위해 다시 Merge한다.

 

소트 연산은 메모리 집약적일 뿐만 아니라 CPU 집약적이기도 하다. 처리할 데이터량이 많을 때는 디스크 I/O까지 발생하므로 쿼리 성능을 좌우하는 매우 중요한 요소다. 디스크 소트가 발생하는 순간 SQL 수행 성능은 나빠질 수 밖에 없다.

-> 많은 서버 리소스를 사용하고 디스크 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가능하게 함으로써 OLTP화경에서 애플리케이션 성능을 저하시키는 주요인이 되기도 한다.

 

[ Union vs Union all ]

Union을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 소트 작업을 수행한다.

Union 사용 예시-두 쿼리가 독립적이다.

Union all은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다.

따라서 union all을 사용하는 게 좋으나 union에서 union all로 변경하려다 자칫 결과집합이 달라 질 수 있어서 주의해야 함

 

중복 제거는 발생하지만 소트 작업 발생

중복 제거 및 소트 작업 발생하지 않음

만약 결제일자가 Null 허용 컬럼이면 맨 아래 조건절을 아래와 같이 변경해야 한다.

and (결제일자<> '20180316' or 결제일자 is null)
# 또는 LNNVL함수를 이용한다
and LNNVL(결제일자 ='20180316')

[ 중복제거 ]

중복 레코드를 제거할 목적으로 Distinct 연산자를 종종 사용한다. 조건에 해당하는 데이터를 모두 읽어서 중복을 제거한다.

-> 부분범위 처리는 당연히 불가능하고, 모든 데이터를 읽는 과정에 많은 I/O가 발생한다.

Exists 서브쿼리는 데이터 존재 여부만 확인하면 되기 때문에 조건을 만족하는 데이터를 모두 읽지 않는다.

 

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

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다. 이를 활용하여 Order By 또는 Group By 절이 있어도 소트연산을 생략할 수 있다.

 

 

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝