Oracle 정렬 작업은 많은 CPU와 메모리를 소비하는 비용이 많이 드는 작업입니다. 정렬할 데이터가 클수록 더 많은 디스크 I/O가 발생할 수 있습니다. 정렬 연산은 order by, group by, min, max 및 sum과 같은 작업을 수행할 때 사용됩니다. 이번 글에서는 정렬의 원리와 불필요한 정렬을 줄이는 방법에 대해 알아보겠습니다.
오라클 정렬 작업
oracle의 order by나 group by와 같은 정렬 작업은 각 세션에 할당된 PGA의 정렬 영역에서 수행하거나, 디스크 공간이 부족할 경우 임시 디스크 공간을 사용한다. 이를 더 설명하면 메모리 공간, 즉 PGA에서 정렬 과정을 수행하는 것을 인 메모리 정렬 또는 내부 정렬이라고 하며, 데이터가 크기 때문에 PGA의 정렬 영역에서 정렬 프로세스를 완료할 수 없으며, 심지어 디스크 저장소의 임시 공간까지 사용하는데 이를 디스크 간 정렬 또는 외부 정렬이라고 합니다. 정렬 프로세스는 다음과 같습니다. 정렬이 필요한 SQL이 실행되면 SGA는 버퍼 캐시에서 정렬할 대상 집합을 찾아 PGA 영역에서 정렬을 시작합니다. 보통은 PGA의 정렬 영역에서 정렬이 완료되지만, 정렬할 데이터가 많을 때는 임시 세그먼트를 만들어 디스크의 임시 테이블스페이스에 저장하는데 임시 영역을 ‘정렬 실행’이라고 합니다. 마지막으로 임시 테이블스페이스의 임시 세그먼트에 저장된 정렬 실행을 병합하는 과정이 필요하다. 정렬 실행이 이미 정렬되어 있기 때문에 병합이 어렵지 않습니다. 정렬은 메모리와 CPU를 많이 사용하는 작업입니다. 대용량은 디스크 I/O를 수반하므로 이는 쿼리 성능에서 매우 중요한 요소입니다. 따라서 가능하면 정렬이 발생하지 않도록 SQL을 작성하고 불가피할 경우 메모리 정렬을 유도하는 것이 좋다.
오라클 정렬 작업
Oracle에는 정렬 작업을 트리거하는 여러 작업이 있으므로 순서대로 살펴보겠습니다. 첫 번째는 정렬 집계입니다. 정렬 집계는 실제로 정렬되지 않지만 모든 행에서 집계가 수행될 때 표시됩니다. 일반적으로 정렬하지 않고 MIN, MAN, SUM, AVG 값을 얻는 데 사용됩니다. 먼저 PGA의 정렬 영역에서 찾고자 하는 SUM, MAX, MIN, COUNT 값에 대한 변수 값을 저장합니다. 테이블의 특정 레코드를 읽은 후 정렬 영역에 저장된 각 변수 값을 저장하고 카운트 값까지 저장합니다. SUM의 경우 레코드를 읽을 때마다 값이 누적되고, MAX, MIN 값의 경우 이전 값보다 크거나 작은 값을 가져올 때 값을 바꿔서 저장한다. AVG 값의 경우 MAX 변수에 저장된 마지막 값을 카운트 값으로 나눈 값을 나타냅니다. 두 번째 작업은 정렬 순서 기준으로 데이터를 정렬할 때 말 그대로 나타납니다. 세 번째는 Group Sort By로 그룹별 그룹핑을 할 때 사용합니다. group-by 연산과 합, 최소값, 최대값을 구하는 연산이 동시에 수행된다면 그 다음은 어떻게 될까요? group-by 작업을 팀 단위로 수행하는 경우 팀별로 정렬되어 각 팀별로 sum, min, max 값이 계산된다고 생각할 수 있지만 그 반대입니다. 위에서 설명한 집계 작업을 먼저 수행하고 각 팀에서 작업을 수행합니다. 네 번째는 정렬-병합 조인을 할 때 나타나는 정렬 조인이다. 다섯 번째는 윈도우 정렬로 윈도우 기능이 실행될 때 나타난다.
불필요한 정렬
정렬 작업은 메모리와 CPU를 소모하는 작업으로 정렬 작업에 필요한 데이터가 많을 경우 디스크의 임시 테이블스페이스를 사용하면서 디스크 I/O를 사용할 수 있는 비용이 많이 드는 작업이다. 이제 불필요한 정렬을 피하기 위해 SQL을 작성하는 방법을 살펴보겠습니다. Union을 사용하면 옵티마이저가 정렬 작업을 수행하여 상위 및 하위 두 세트가 결합될 때 중복 콘텐츠를 제거합니다. 이때 과도한 메모리와 CPU를 사용하게 되며 데이터 양에 따라 디스크 I/O가 발생할 수 있습니다. 그러나 union all을 사용하면 위와 아래의 두 집합이 단순히 결합되므로 정렬이 수행되지 않습니다. 이러한 이유로 가능할 때마다 union 대신 union all을 사용하는 것이 좋습니다. 하지만 단순히 union을 all로 바꾸면 결과가 달라질 수 있기 때문에 데이터 모델에 대한 이해가 필요합니다. SQL에서 union all 을 union 과 함께 사용하면 단순히 상한과 하한 집합을 합친 결과만 나오게 되는데 이 경우 중복된 결과값이 나오게 됩니다. 이 경우 위 SQL의 조건절에 사용된 조건이 다음 SQL에서는 사용되지 않는다는 조건을 지정함으로써 union all을 사용하더라도 중복 값을 피할 수 있다. 불필요한 정렬을 줄이는 또 다른 방법은 exists를 사용하는 것입니다. 경우에 따라 중복 레코드를 제거하기 위해 distinct를 사용하는데, 이 경우 조건에 맞는 데이터를 모두 읽어야 하고 부분 처리조차 불가능하기 때문에 I/O가 많이 발생한다. 단, exists 를 사용하면 조건절에 있는 모든 데이터를 읽지 않고 데이터 존재 여부만 확인합니다. 따라서 이를 exists로 변환하여 minus 및 distinct 대신 사용할 수 있습니다.