친절한 SQL 튜닝_5장.소트 튜닝_4

소트 튜닝

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

5.4.1 소트 데이터 줄이기

특정 기간에 발생한 주문상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 Sort Area를 더 적게 사용할까?

[1번]

 

SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, ‘yyyymmdd hh24:mi:ss’)
  FROM 주문상품
 WHERE 주문일시 BETWEEN :START AND :END
 ORDER BY 상품번호

 

[2번]

 

SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, ‘yyyymmdd hh24:mi:ss’)
  FROM (
         SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시 
           FROM 주문상품
          WHERE 주문일시 BETWEEN :START AND :END
         ORDER BY 상품번호
        )

1번 SQL은 레코드당 107(=30+30+10+20+17) 바이토로 가공한 결과집합을 Sort Area에 담는다. 반면, 2번 SQL은 가공하지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공한다. 따라서 2번 SQL이 Sort Area를 휠씬 적게 사용한다.

 

아래 두 SQL 중에서는 어느 쪽이 Sort Area를 더 적게 사용할까?

[1번]

 

SELECT *
  FROM 예수금원장
 ORDER BY 총예수금 DESC

Execution Plan

0                         SELECT STATEMENT Optimizer=ALL_ROWS (Cost=184K Card=2M Bytes=716M)

1            0            SORT (ORDER BY) (Cost=184K Card=2M Bytes=716M)

2            1              TABLE ACCESS (FULL) OF ‘예수금원장’ (TABLE) (Cost=24K Card=2M Bytes=716M)

 

[2번]

 

SELECT 계좌번호, 총예수금
  FROM 예수금원장
 ORDER BY 총예수금 DESC

Execution Plan

0                         SELECT STATEMENT Optimizer=ALL_ROWS (Cost=31K Card=2M Bytes=17M)

1            0            SORT (ORDER BY) (Cost=31K Card=2M Bytes=17M)

2            1              TABLE ACCESS (FULL) OF ‘예수금원장’ (TABLE) (Cost=31K Card=2M Bytes=17M)

당연히 2번 SQL이 적게 사용한다. 1번 SQL은 모든 컬럼을 Sort Area에 저장하는 반면, 2번 SQL은 계좌번호와 총예수금만 저장하기 때문이다. 실행계획에서 맨 우측열을 보면, 1번 SQL은 716MB, 2번 SQL은 17MB를 처리했다. 두 SQL 모두 테이블을 Full Scan 했으므로 읽은 데이터량은 똑같지만, 소트한 데이터량이 다르므로 성능도 다르다.

 

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

SELECT *
  FROM (
          SELECT ROWNUM NO, A.*
           FROM (
                  SELECT 거래일시, 체결건수, 체결수량, 거래대금
                    FROM 종목거래
                   WHERE 종목거래 = ‘KR123456’
                     AND 거래일시 >= ‘20180304’
                   ORDER BY 거래일시
                 ) A
           WHERE ROWNUM <= (:page * 10)
       ) 
 WHERE NO >= (:page-1) * 10 + 1

아래는 인덱스로 소트 연산을 생략할 수 없이 Table Full Scan 방식으로 처리할 때의 SQL 트레이스다.

Rows      Row Source Operation

0            STATEMENT

10          COUNT STOPKEY

10           VIEW

10             SORT ORDER BY STOPKEY (cr=690 pr=0 pw=0 time=83264 us)

49857         TABLE ACCESS FULL 종목거래

실행계획에 Sort Order By 오퍼레이션이 나타났다. Table Full Scan 대신 종목코드가 선두인 인덱스를 사용할 수도 있지만, 바로 뒤 컬럼이 거래일시가 아니면 소트 연산을 생략할 수 없으므로 지금처럼 Sort Order By 오퍼레이션이 나타난다.

여기서 Sort Order By 옆에 ‘Stopkey’라고 표시된 부분을 주목하기 바란다. 소트 연산을 피할 수 없이 Sort Order By 오퍼레이션을 수행하지만 ‘Top N 소트’알고리즘이 작동한다는 사실을 실행계획에 표시하고 있다. 이 알고리즘이 작동하면, 소트 연산(=값 비교) 횟수와 Sort Area 사용량을 최소화해 준다. 예를 들어, page 변수에 1을 입력하면 열 개 원소를 담을 배열(Array) 공간만 있으면 된다.

이 방식으로 처리하면, 대상 집합이 아무리 커도 많은 메모리 공간이 필요하지 않다. 전체 레코드를 다 정렬하지 않고도 오름차순(ASC)으로 최소값을 갖는 열 개 레코드를 정확히 찾아낼 수 있다. 이것이 ‘Top N 소트’ 알고리즘이 소트 연산 횟수와 Sort Area 사용량을 줄여주는 원리다.

 

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

SQL을 더 간결하게 표현하기 위해 다음과 같이 Order By 아래 쪽 ROWNUM 조건절을 제거하고 수행해 보자.

 

SELECT *
  FROM (
          SELECT ROWNUM NO, A.*
           FROM (
                  SELECT 거래일시, 체결건수, 체결수량, 거래대금
                    FROM 종목거래
                   WHERE 종목거래 = ‘KR123456’
                     AND 거래일시 >= ‘20180304’
                   ORDER BY 거래일시
                 ) A
       ) 
 WHERE NO BETWEEN (:page-1) * 10 + 1 AND (:page * 10)

아래는 SQL 트레이스 결과다. Sort Area는 앞에서와 똑같이 설정하고 테스트하였다.

Rows      Row Source Operation

0            STATEMENT

10           VIEW

49857         COUNT

49857          VIEW

49857           SORT ORDER BY (cr=690 pr=698 pw=698 time=756723 us)

49857            TABLE ACCESS FULL 종목거래

실행계획에서 Stopkey가 사려졌다. ‘Top N 소트’ 알고리즘이 작동하지 않았다는 뜻이다. 그 결과로 Physical Read(pr=698)와 Physical Write(pw=698)가 발생했다. 같은 양(690 블록)의 데이터를 읽고 정렬을 수행했는데, 앞에서는 ‘Top N 소트’ 알고리즘이 작동해 메모리 내에서 정렬을 완료했지만 지금은 디스크를 이용해야만 했다.

 

5.4.4 분석함수에서의 Top N 소트

윈도우 함수 중 rank나 row_number 함수는 max 함수보다 소트 부하가 적다. Top N 소트 알고리즘이 작동하기 때문이다.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음