[SQLP] 소트 튜닝 - 3

인덱스를 이용한 소트 연산 대체

가. Sort Order By 대체

• 아래의 쿼리를 읽을 때 [region + custid] 순으로 구성된 인덱스 사용한다면 sort order by 연산을 대체할 수 있다.
• 인덱스 미리 정렬이 되어있기 때문이라 생각하면 된다.
• 물론 소트해야 할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 때 유용하다고 함
  • 인덱스를 스캔하면서 결과 집합을 끝까지 Fetch 한다면 오히려 I/O 및 리소스 사용측면서 손해임

select custid, name, resno, status, tel1  
from customer  
where region = 'A'  
order by custid

--------------------------------------------------------------------  
|Id| Operation | Name | Rows|Bytes|Cost(%CPU)|  
--------------------------------------------------------------------  
| 0|SELECT STATEMENT | |40000|3515K| 1372 (1)|  
| 1|TABLE ACCESS BY INDEX ROWID|CUSTOMER |40000|3515K| 1372 (1)|  
| 2|INDEX RANGE SCAN |CUSTOMER_X02|40000| | 258 (1)|  
--------------------------------------------------------------------

나. Sort Group By 대체

• Sort Order By와 유사하게 Sort Group By 연산도 인덱스를 활용할 수 있다.
• 아래의 실행계획을 보면 SORT GROUP BY NOSORT 가 명시되어있다.

select region, avg(age), count(*)  
from customer  
group by region

---------------------------------------------------------------------------  
| Id | Operation | Name |Rows |Bytes |Cost (%CPU)|  
---------------------------------------------------------------------------  
| 0 |SELECT STATEMENT | | 25 | 725 |30142 (1)|  
| 1 |SORT GROUP BY NOSORT | | 25 | 725 |30142 (1)|  
| 2 |TABLE ACCESS BY INDEX ROWID|CUSTOMER |1000K| 27M |30142 (1)|  
| 3 |INDEX FULL SCAN |CUSTOMER_X01|1000K| |2337 (2)|  
---------------------------------------------------------------------------

다. 인덱스를 활용한 Min, Max 구하기

• 인덱스의 정렬 특성 때문에 min, max 값을 빠르게 추출할 수 있다.
• 아래의 실행계획을 보면 FIRST ROW 와 MIN/MAX 오퍼레이션이 나타난다.
• 이 기능을 First Row StopKey 라고 한다.

select nvl(max(주문번호), 0) + 1  
from 주문  
where 주문일자 = :주문일자

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=ALL_ROWS  
1 0 SORT (AGGREGATE)  
2 1 FIRST ROW  
3 2 INDEX (RANGE SCAN (MIN/MAX)) OF '주문_PK' (INDEX (UNIQUE))

• 참고로 아래와 같이 min, max 함수 내에서 가공이 일어난다면 인덱스를 사용하지 못한다.

select nvl(max(주문번호 + 1), 1)  
from 주문  
where 주문일자 = :주문일자

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=ALL_ROWS  
1 0 SORT (AGGREGATE)  
2 1 INDEX (RANGE SCAN) OF '주문_PK' (INDEX (UNIQUE))

소트영역을 적게 사용하도록 SQL 작성

가. 소트 완료 후 데이터 가공

• 특정 기간의 주문 상품 목록을 파일로 내리고자 할 때 아래 두 쿼리 중 2번 쿼리가 소트 영역을 적게 사용한다.
• 이유는 1번 쿼리는 선 가공 후 정렬을 하고 2번 쿼리는 정렬한 결과를 가공하기 때문이다.
  • 굳이 사용하지 않을 결과 집합을 가공할 필요가 없다.

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 상품번호

2번 쿼리

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 상품번호  
)

나. Top N 쿼리

• TOP N 쿼리 형태로 작성하면 소트 연산 횟수와 소트 영역 사용량을 최소화할 수 있다.
  • SQL Server는 TOP N 으로 간단하게 쓸 수 있지만 Oracle은 인라인 뷰로 한 번 감싸야 한다.

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

• 위 쿼리를 수행할 때 인덱스가 [종목 코드 + 거래일시] 로 잡혀있다고 가정해보자
• 아래 실행계획을 보면 SORT ORDER BY 오퍼레이션이 나타나지 않은 것을 알 수 있다.
• 이 기능을 TOP N StopKey 알고리즘이라 부르기 하자.
  • COUNT (STOPKEY) 로 오퍼레이션이 나타난다.

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=ALL_ROWS  
1 0 COUNT (STOPKEY)  
2 1 VIEW  
3 2 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)  
4 3 INDEX (RANGE SCAN) OF ' 시간별종목거래_PK' (INDEX (UNIQUE))

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

• 만약 인덱스가 없다면 어떻게 될까 ?
• 이런 경우 Full Table Scan 방식으로 처리할 텐데, 이 때도 Top N Sort 알고리즘이 작동해 소트 영역을 최소화한 얻는 효과를 가질 수 있다.
• 위와 같이 TOP 10 인 상황으로 다시 가정해보자
• 10개를 우선 순서대로 담아 놓았고 이 후 지속적인 Sort를 할 때, 맨 우측 값만 비교하여, 우측 값보다 값이 작을 때만 다시 배열 내에서 정렬을 시도한다.
  • 물론 이런 경우 맨 우측 값은 버리게 된다.

[8, 16, 98, 136, 189, 190, 233, 264, 287, 337]

TOP N Sort 알고리즘이 작동하지 못하는 경우

• 아래 쿼리는 페이징 처리의 예시이다.
• 아래의 쿼리에서 rownum이 원래대로 적혀있었다면 인덱스가 없어도 어느정도 최적화할 순 있지만, 쓰지 않을 경우 최적화하지 못하는 당연한 결과다.

select *  
from (
    select rownum no, 거래일시, 체결건수, 체결수량, 거래대금  
    from (
        select 거래일시, 체결건수, 체결수량, 거래대금  
        from 시간별종목거래  
        where 종목코드 = 'KR123456'  
            and 거래일시 >= '20080304'  
        order by 거래일시  
    )  
    -- where rownum < = 100  
)  
where no between 91 and 100

윈도우 함수에서의 Top N 쿼리

• 윈도우 함수를 이용해 마지막 이력 레코드를 찾는 경우 max() 함수를 사용하는 쿼리다.

select 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급  
from (
    select 고객ID, 변경순번  
    , max(변경순번) over (partition by 고객ID) 마지막변경순번  
    , 전화번호, 주소, 자녀수, 직업, 고객등급  
    from 고객변경이력
)  
where 변경순번 = 마지막변경순번

• 윈도우 함수를 사용할 때도 max() 함수보다 다음과 같이 rank() 나 row_number() 함수를 사용하는 것이 유리하다.
• 이것 역시 Top N Sort 알고리즘이 작동한다.

select 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급  
from (
    select 고객ID, 변경순번  
    , rank() over (partition by 고객ID order by 변경순번) rnum  
    , 전화번호, 주소, 자녀수, 직업, 고객등급  
    from 고객변경이력
)  
where rnum = 1

다. Top N 쿼리를 이용한 효과적인 이력 조회

• 이력 데이터를 조회할 때 결과적으로 First StopKey 또는 Top N StopKey 알고리즘이 작동할 수 있도록 인덱스를 설계하고 구현해야한다.
• 예를들어 인덱스 컬럼을 구성되있더라도 인덱스 컬럼을 가공했으니 First Row Stop Key가 작동하지 않는다.

소트 영역 크기 조정

SQL Server는 소트 영역을 수동으로 조정하는 방법을 제공하지 않음

• 소트가 불가피하다면 메모리 내에서 작업을 완료할 수 있어야 최적이다.
• 디스크 소트가 불가피할 땐, 임시 공간에 기록했다가 다시 읽는 횟수를 최소화할 수 있어야 최적이다.
• Oracle 8i 까지는 데이터 정렬을 위해 기본 값은 관리자가 지정하고 작업에 따라 세션 레벨에서 다음과 같이 조정하는 방식이다.

alter session set sort_area_size = 1048576;

• 9i 부터는 자동 PGA 메모리 관리 기능이 도입됐기 때문에, DBA가 pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 총량을 지정하면 시스템 부하 정도에 따라 각 세션에 메모리를 할당한다.
  • 해당 기능은 workarea_size_policy 를 auto 로 설정하면 되는데 9i 부터 기본으로 설정되있기 때문에 sort_area_szie 파라미터가 무시 된다고 한다.
• 수동 PGA 메모리 관리 방식도 효율적일 때가 있는데 트랜잭션이 없는 야간에 대량의 배치 Job을 수행할 때는 직접 크기를 조정하는게 효과적일 수 있다고 한다.
  • 자동으로 설정하게 되면 다른 프로세스가 사용하지 않더라도 자동 PGA 메모리 관리로 인해 모든 공간을 다 쓸 수 없기 때문이다.
• 아래와 같이 설정할 수 있다.

alter session set workarea_size_policy = manual;  

alter session set sort_area_size = 10485760;