HighLight in your life~

목적: 소트 영역 크기를 늘리는 방법도 있지만 그전에 소트 영역을 적게 사용할 방법부터 찾는 것이 순서다.

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

- '1차 단계'는 레코드당 105(=30+30+10+20+15) 바이트(헤어 정보는 제외하고 데이터 값만)로 가공된 결과치를 소트 영역에 담는다. 반면 '2차 단계'는 가공되지 않는 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 '1차 단계'에 비해 소트 영역을 휠씬 적게 사용한다. 

1) 1차 단계

SELECT LPAD(상품번호, 30) || LPAD(상품명,30) || LPAD(고객ID, 10)

|| LPAD(고객명,20) || LPAD(주문일시, 'YYYYMMDD HH24:MI:SS')

FROM 주문상품

WHERE 주문일시 BETWEEN :start ADN :end

ORDER BY 상품번호

;

2) 2차 단계

SELECT LPAD(상품번호, 30) || LPAD(상품명,30) || LPAD(고객ID, 10)

|| LPAD(고객명,20) || LPAD(주문일시, 'YYYYMMDD HH24:MI:SS')

FROM

(

SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시

FROM 주문상품

WHERE 주문일시 BETWEEN :start ADN :end

ORDER BY 상품번호

)

;

2. Top-N 쿼리

- ROWNUM 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 빠른 수행 속도를 낼 수 있다. 실행계획에 'COUNT (STOPKEY)'가 그것을 의미한다.

- Top-N 쿼리 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻게 된다.

SELECT * FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 ='A0T100M'

AND 거래일시 >= '200120301'

ORDER BY 거래일시

 )

 WHERE ROWNUM <= 10

 ;

*Top-N 쿼리 알고리즘이 작동하지 못하는 경우

 목표: 한 페이지에 10개씩 출력한다고 가정하고, 10 페이지를 출력하는 예시다.

 1) Top-N 쿼리가 작동하는 예제

  SELECT *

  FROM (SELECT ROWNUM NO, 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 'A0T100M'

AND 거래일시 >= '20080304'

ORDER BY 거래일시

)

WHERE ROWNUM <= 100

       )

 WHERE NO BETWEEN 91 AND 100

 ;

2) Top-N 쿼리가 작동하지 않는 예제: WHERE절 하나를 줄이고도 같은 결과집합을 얻을 수 있어 더 효과적인 것처럼 보이지만 그 순간 부터 Top-N 쿼리 알고리즘은 작동하지 않는다.

SELECT *

FROM (SELECT ROWNUM NO, 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 'A0T100M'

AND 거래일시 >= '20080304'

ORDER BY 거래일시

)

-- WHERE ROWNUM <= 100 -- 주석처리

)

WHERE NO BETWEEN 91 AND 100

;

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

- 윈도우 함수를 사용할 때도 max() 함수보다 rank()나 row_number() 함수를 사용하는 것이 유리한데, 이것 역시 Top-N 쿼리 알고리즘이 작동하기 때문이다.

 (가) Top-N 쿼리 작동(X)

 SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급

     FROM (SELECT 고객ID, 변경순번

, MAX(변경순번) OVER (PARTITION BY 고객ID) 마지막변경순번

, 전화번호, 주소, 자녀수, 직업, 고객등급

          FROM 고객변경이력) A

 WHERE 변경순번 = 마지막변경순번

  ; 

 (나) Top-N 쿼리 작동(O)

 SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급

 FROM (SELECT 고객ID, 변경순번

 , RANK() OVER (PARTITION BY 고객ID ORDER BY 변경순번) RNUM

 , 전화번호, 주소, 자녀수, 직업, 고객등급

 FROM 고객변경이력) A

 WHERE RNUM

  ;

1. 소트 튜닝 방안

 1) 데이터 모델 측면에서의 검토

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

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

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

 5) 소트 영역 크기 조정

2. 메모리 소트와 디스크 소트

- SQL 수행 도중 Sort 오퍼레이션으 필요할 때마다 DBMS는 정해진 메모리 공간에 Sort Area를 할당하고 정렬을 수행한다. Oracle은 Sort Area를 PGA(Private Global Area) 영역에 할당하고, SQL Server는 버퍼 캐시에 할당한다.

- 소트 오퍼레이션 수행 중에 메모리 공간이 부족할 때는 디스크 공간을 사용한다. Oracle에서는 Temp Tablespace를 이용하고, SQL Server에서는 tempdb를 이용한다.

- 대량의 데이터를 정렬할 땐 디스크 소트가 불가피하다.

- 전체 대상 집합을 디스크에 기록했다가 다시 읽는 작업을 여러 번 반복하는 경우 SQL 수행 성능은 극도로 나빠진다.

3. 소트를 발생시키는 오퍼레이션

 1) Sort Aggregate - 전체 로우를 대상으로 집계를 수행할 때

  SQL> SELECT SUM(SAL), MAX(SAL), MIN(SAL) FROM EMP;

 2) Sort Order by - 정렬된 결과집합을 얻고자 할 때

  SQL> SELECT * FROM EMP ORDER BY SAL DESC;

 3) Sort Group by - Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때

  SQL> SELECT DEPTNO, JOB, SUM(SAL), MAX(SAL), MIN(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

;

 4) Sort Unique - 선택된 결과집합에서 중복 레코드를 제거하고자 할 때(Union 연산자, Distinct 연산자)

  SQL> SELECT DISTINCT DEPTNO

FROM EMP

ORDER BY DEPTNO

;

 5) Sort Join - Sort Merge Join을 수행할 때

  SQL> SELECT /*+ ordered use_merge(e) */

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

;

 6) Window Sort - 윈도우 함수를 수행할 때

  SQL> SELECT EMPNO, ENAME, JOB, MGR, SAL, ROW_NUMBER() OVER (ORDER BY HIREDATE)

FROM EMP

;

4. 소트 오퍼레이션은 메모리 집약적(Memory-intensive) 일뿐만 아니라 CPU 집약적(CPU-intensive)이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 크게 떨어뜨린다. 특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인이 되곤 한다. 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.