HighLight in your life~

 SQL Server에서는 Sort Area를 수동으로 조정하는 방법이 없다. Oracle은 있다.

 오라클 9i부터 '자동 PGA 메모리 관리(Automatic PGA Memonry Management)' 기능이 도입되었다. pga_aggregate_target 파라미터를 통해 인스턴스의 전체적으로 이용 가능한 PGA 메모리 총량을 지정하면, Oracle이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리를 할당해 준다.

 자동 PGA 메모리 관리 기능을 활성화하려면 workarea_size_policy를 auto로 설정하면 되는데, 9i부터 기본적으로 auto로 설정돼 있으며 sort_area_size 파라미터는 무시된다.

 기본적으로 자동 PGA 메모리 관리 방식이 활성화되지만 시스템 또는 세션 레벨에서 '수동 PGA 메모리 관리' 방식으로 전환할 수 있다.

 특히, 트랜잭션이 거의 없는 야간에 대량의 배치 Job을 수행할 때는 수동으로 변경하고 직접 크기를 조정하는 것이 효과적일 수 있다. 즉, Sort Area를 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸 수 없는 것이다. 결국 수 GB의 여유 메모리를 두고도 이를 충분히 활용하지 못해 작업 시간이 오래 걸릴 수 있다.

 그럴 때 아래와 같이 workarea_size_policy 파라미터를 세션 레별에서 manual로 변경하고, 필요한 만큼(최대 2,147,483,647 바이트) Sort Area 크기를 늘림으로써 성능을 향상시키고, 궁극적으로 전체 작업 시간을 크게 단축시킬 수 있다.

 SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

 SQL> ALTER SESSION SET SORT_AREA_SIZE = 10485760;

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

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

  ;