SQL튜닝) 고급SQL튜닝 - 소트튜닝

메모리소트 & 디스크소트

 

SQL 수행중 SORT OPERATION이 필요할때마다, DBMS는 정해진 메모리 공간에 소트 영역을 할당하고 정렬을 수행합니다. ORACLE은 소트영역을 PGA에 할당하고, SQL SERVER는 버퍼캐시에 할당합니다.

 

소트에 필요한 메모리 공간이 부족하면 디스크영역을 사용합니다. 이 영역을 ORACLE은 TEMP TABLESPACE, SQL SERVER는 TEMP DB라고 합니다. 디스크 공간을 사용하는 경우 수행성능이 저하됩니다.

 

- 메모리 소트 : 할당받은 소트 영역 내에서 정렬작업을 완료
- 디스크 소트 : 할당받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간에서 정렬작업 완료

 

디스크에 임시저장한 반복 횟수에 따라 디스크 소트는 두 가지로 구분됩니다.

 

- ONEPASS SORT : 정렬대상집합을 디스크에 한 번만 기록
- MULTIPASS SORT : 정렬대상집합을 디스크에 여러번 기록

 

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

 

SORT AGGREGATE

 

전체 로우를 대상으로 집계를 수행할 때 나타납니다. 실행계획에는 나타나지만 실제 소트연산은 발생하지 않습니다.

 

SELECT SUM(SAL), MAX(SAL), MIN(SAL) FROM SCOTT.EMP;

-- EXECTION PLAN
-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0  SORT (AGGREGATE)
-- 2  1     TABE ACCESS (FULL) OF 'EMP' TABLE

 

SORT ORDER BY

 

ORDER BY 절을 사용해 결과집합을 정렬할때 나타납니다.

 

SELECT * FROM EMP ORDER BY SAL DESC

-- EXECTION PLAN
-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0   SORT (ORDER BY)
-- 2  1   TABE ACCESS (FULL) OF 'EMP' TABLE

 

SORT GROUP BY

 

SORTING 알고리즘을 사용하는 그룹별 집계를 수행할때 나타납니다.

 

SELECT DEPTNO, JOB, SUM(SAL), MAX(SAL), MIN(SAL)
FROM EMP
GROUP BY DEPTNO, JOB

-- EXECTION PLAN
-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0   SORT (GROUP BY)
-- 2  1     TABE ACCESS (FULL) OF 'EMP' TABLE

-- ORACLE은 HASHING 알고리즘으로 그룹별 집계를 수행하기도 하는데 그때 실행계획에 다듬과 같이 표현된다
-- EXECTION PLAN
-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0   HASH (GROUP BY)
-- 2  1     TABE ACCESS (FULL) OF 'EMP' TABLE 

 

SORT UNIQUE

 

결과집합에서 중복 레코드를 제거하는 연산을 실행할 때 나타납니다. UNION 연산자나 DISTINCT 연산자가 대표적입니다.

 

SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO

-- EXECTION PLAN
-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0   SORT (UNIQUE)
-- 2  1     TABE ACCESS (FULL) OF 'EMP' TABLE

 

SORT JOIN

 

소트머지조인을 수행할 때 나타납니다.

 

SELECT /*+ORDERED USE_MERGE(e)*/ *
FROM EMP.E, DEPT.D
WHERE D.DEPTNO = E.DEPTNO

-- EXECTION PLAN
-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0   MERGE JOIN
-- 2  1     SORT (JOIN)
-- 3  2       TABLE ACCESS (FULL) OF 'EMP'
-- 4  1     SORT (JOIN)
-- 5  4       TABLE ACCESS (FULL) OF 'DEPT'

 

WINDOW SORT

 

윈도우 함수를 수행할 때 나타납니다.

 

SELECT EMPNO, ENMAE, JOB, MGR, SAL, ROW_NUMBER() OVER (ORDER BY HIREDATE)
FROM EMP

-- 0    SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- 1  0   WINDOW(SORT)
-- 2  1     TABLE ACCESS (FULL) OF 'EMP'

 

소트 오퍼레이션 부하

 

 

소트 오퍼레이션은 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인이 됩니다. 될수 있으면 소트가 발생하지 않도록 하고 소트를 피할 수 없다면 메모리에서 수행을 완료할 수 있도록 해야합니다.

 

소트오퍼레이션이 발생하지않는 SQL 작성법

 

UNION을 UNION ALL로 대체

 

UNION은 중복제거를 위해 SORT UNIQUE 연산을 수행하는 반면, UNION ALL은 중보을 허용하므로 소트연산이 불필요합니다.

 

DISTINCT를 EXIST 서브쿼리로 대체

 

중복 레코드를 제거하는 DISTINT는 SORT UNIQUE 연산을 수행합니다. 대부분 EXISTS 서브쿼리로 대체할 수 있습니다.

 

SELECT 연월
FROM 연월테이블 a
WHERE 연월 <= :yyyymm
and exists (
  SELECT 'X'
  FROM 과금
  WHERE 과금연월 = a.연월
  AND 지역 like :reg || '%'
)

 

EXISTS 서브쿼리의 가장 큰 특징은 조건을 만족하는 첫 번째 레코드를 만나는 순간 ture를 반환하고 서브쿼리 수행을 마친다는 점입니다.
따라서 서브쿼리의 인덱스가 최적으로 구성되면 최소한의 블록엑세스로 작업을 수행할 수 있습니다.

 

불필요한 COUNT 연산 제거

 

아래의 쿼리는 데이터 존재 여부만 확인하면 되는데도 불필요하게 전체 건수를 COUNT 하는 경우입니다.

 

DECLARE
1_CNT NUMBER;
BEGIN
SELECT COUNT(*) INTO 1_CNT
FROM SCOTT.MEMBER
WHERE MEMB_CLS = '1'
AND BIRTH_YYYY <= '1950';

IF 1_CNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT EXISTS');
END IF;
END;

 

쿼리를 다음과 같이 바꾸면 블록 I/O를 단 3개로 줄일 수 있어 디스크 I/O 발생여부와 상관 없이 빠른 성능을 항상 보장합니다.

 

DECLARE
1_CNT NUMBER;
BEGIN
SELECT 1 INTO 1_CNT
FROM SCOTT.MEMBER
WHERE MEMB_CLS = '1'
AND BIRTH_YYYY <= '1950';
and rownum <= 1

DBMS_OUTPUT.PUT_LINE('EXISTS');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NOT EXISTS');
END;

 

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

 

인덱스는 항상 구성 칼럼 순으로 정렬딘 상태를 유지하므로 인덱스를 이용해 소트 오퍼레이션을 생략할 수 있습니다.

 

SORT ORDER BY 대체

 

INDEX [REGION + CUSTID]
SELECT CUSTID, NAME, RESNO, STATUS, TEL1
FROM CUSTOMER
WHERE REGION = 'A'
ORDER BY CUSTID

--- EXECUTION PLAN
-- SELECT STATEMENT
--  TABLE ACCESS BY INDEXROWID
--    INDEX RANGE SCAN

 

위 쿼리는 ORDER BY를 사용했음에도 실행계획에 SORT ORDER BY 오퍼레이션이 나타나지 않습니다. 이 방식은 REGION 비교절을 만족하는 전체 로우를 읽지 않고도 CUSTID로 정렬된 결과집합을 얻을 수 있어 OLTP 환경에서 극적인 성능개선을 유도합니다.

 

소트해야할 전체 대상 레코드가 무수히 많고 그중 일부만 읽고 멈출때, 성능개선이 극대화됩니다. 만약 인덱스를 스캔하면서 결과집합 전체를 FETCH 해야한다면 오히려 성능상 손해입니다.

 

대상 레코드가 소량일 때는 소트가 발생하더라도 부하가 크지 않으므로 성능개선효과도 미미합니다.

 

SORT GROUP BY 대체

 

집계 기준 칼럼이 인덱스의 선두칼럼이라면 집계시 추가적인 정렬이 불필요합니다. 이 경우 실행계획에 SORT GROUP BY NOSORT 라고 표시됩니다.

 

INDEX [REGION]
SELECT REGION, AVG(AGE), CONT(*)
FROM CUSTOMER
GROUP BY REGION

-- EXECUTION PLAN
-- SELECT STATEMENT
--  SORT GROUP BY NOSORT
--    TABLE ACCESS BY INDEX ROWID   CUSTOMER
--      INDEX FULL SCAN             CUSTMOER_X01

 

인데스를 활용한 MIN, MAX 구하기

 

인덱스는 항상 정렬되있으므로 대상 레코드 전체를 읽지 않고도 최대,최소값을 추출할 수 있습니다. 아래 쿼리는 인덱스에 의해 정렬 없이 집계함수가 실행됐습니다. 실행계획에 FIRST ROW와 MIN/MAX 오퍼레이션이 나타난 것을 확인할 수 있습니다.

 

-- index : [주문일자 + 주문번호]
SELECT NVL(MAX(주문번호), 0) + 1
FROM 주문
WHERE 주문일자 = :주문일자

-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMAZER=ALL_ROWS
--  SORT(AGGREGATE)
--    FIRST FOW
--      INDEX (RANGE SCAN (MIN/MAX)) OF '주문_pk' (index(unique))

 

주의할 점은, 집계함수 내에서 인덱스 칼럼을 가공하면 인덱스를 사용하지 못하게 됩니다. FISRT ROW 와 STOPKEY 알고리즘이 사용되지 않습니다.

 

SELECT NVL(MAX(주문번호 + 1), 1)
FROM 주문
WHERE 주문일자 = :주문일자

-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMAZER=ALL_ROWS
--  SORT(AGGREGATE)
--      INDEX (RANGE SCAN (MIN/MAX)) OF '주문_pk' (index(unique))

 

소트영역을 적게 사용하는 SQL

 

소트연산 대체가 불가하다면 메모리 내에서 처리하도록 노력해야합니다.

 

소트완료 후 데이터 가공

 

데이터의 가공이 필요한 쿼리의 경우 가공하기 전에 정렬을 우선 완료하고 가공하는 것이 효율적입니다.

 

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 쿼리

 

필요한 상위 N건을 우선 조회하는 TOP N 쿼리를 자성하면 소트 연산 횟수와 소트 영역의 사용량을 최소화 할 수 있습니다. 특히 적절한 구성의 인덱스가 존재하면 소트연산을 생략함으로써 최상의 효율을 낼 수 있습니다. 실행계획을 보면 SORT ORDER BY 오퍼레이션이 나타나지 않은 것을 확인할 수 있습니다.

 

-- INDEX : [종목코드 + 거래일시]
SELECT * FROM
(
SELECT 거래일시, 체결건수, 체결수당, 거래대금
FROM 시간별종목거래
WHERE 종목코드 = 'KR12456'
AND 거래일시 > '20080304'
ORDER BY 거래일시
) WHERE ROWNUM <= 10

-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMIZER=ALL_ROWS
--  COUNT (STOPKEY)
--    VIEW
--      TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)
--        INDEX (RANGE SCAN) OF '시간별종목거래_pk' (INDEX (UNIQUE))

 

ROWNUM 조건절을 통해 N건에서 조회를 멈추게 했으므로 조건절에 부합하는 레코드가 많아도 빠른 속도를 보장할 수 있습니다. 실행계획의 COUNT(STOPKEY)가 그것을 의미합니다.

 

적절한 인덱스가 없어 소트연산을 대체하지 못하더라도 TOP N 쿼리를 사용하면 N 개의 배열을 할당해 최초 10개 래코드를 정렬상태로 담고 다음에 읽어올 값들과 비교해 전체 정렬 없이 N개의 정렬된 결과집합을 얻을 수 있습니다.

 

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

 

쿼리를 아래처럼 작성하면 결과는 같지만 TOP N STOPKEY, TOP N SORT 알고리즘이 모두 작동하지 않습니다.

 

SELECT *
FROM(
  SELECT ROWNUM no, T_DATE, T_COUNT, T_AMOUNT, T_PRICE
  FROM (
    SELECT T_DATE, T_COUNT, T_AMOUNT, T_PRICE
    FROM 시간별종목거래
    WHERE 종목코드 = :종목코드
    AND 거래일시 = :거래일시
    ORDER BY 거래일시
  )
)
WHERE NO BETWEEN 91 AND 100

 

WINDOW 함수에서 TOP N 쿼리

 

RANK(), ROW_NUM() 함수를 사용하면 TOP N STOP KEY 알고리즘이 동작합니다.

 

SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급
FROM (
  SELECT 고객ID, 변경순번
  , rank() over (partition by 고객id order by 변경순번)
  , 전화번호, 주소, 자녀수, 직업, 고객등급
  FROM 고객변경이력
)
WHERE rownum = 1

 

TOP N 쿼리를 이용한 효과적인 이력조회

 

이력 데이터를 조회할 때 'FIRST ROW STOPKEY' 또는 'TOP N STOPKEY' 알고리즘이 작동할 수 있도록 인덱스와 SQL을 구현해야 합니다. 아래 SQL과 같이 인덱스 칼럼을 가공한다면 인덱스 구성이 완전하더라도 FIRST ROW STOPKEY 알고리즘이 작동하지 않습니다.

 

-- 상태변경이력 테이블 PK_IDX : 장비번호 + 변경일자 + 변경순번

SELECT 장비번호, 장비명, 상태코드
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
FROM
(
  SELECT 장비번호, 장비명, 상태코드
  ,(
    SELECT MAX(H.변경일자 || LPAD(H.변경순번, 4))
    FROM 상태변경이력 H
    WHERE 장비번호 = P.장비번호
  ) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT AGGREGATE
--    INDEX RANGE SCAN              상태변경이력_PK
--   TABLE ACCESS BY INDEX ROWID    장비
--    INDEX RANGE SCAN              장비_N1

 

아래의 SQL처럼 인덱스를 역순으로 읽도록 INDEX_DESC 힌트사용하고 첫 번째 레코드에서 멈추ㅗㄹㄱ  ROWNUM <= 1 조건절을 사용한 쿼리는 인덱스를 통한 정렬이 돼있어야 원하는 결과를 얻을 수 있습니다.

 

-- 상태변경이력 테이블 PK_IDX : 장비번호 + 변경일자 + 변경순번

SELECT 장비번호, 장비명, 상태코드
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM
(
  SELECT 장비번호, 장비명, 상태코드
  ,(
    SELECT /*+INDEX_DESC(X 상태변경이력_pk)*/ 
    변경일자 || LPAD(변경순번, 4) || 상태코드
    FROM 상태변경이력 X
    WHERE 장비번호 = P.장비번호
    AND ROWNUM <= 1) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

-- EXECUTION PLAN
-- SELECT STATEMENT
-- COUNT STOPKEY
--  TABLE ACCESS BY INDEX ROWID -- 상태변경이력
--    INDEX RANGE SCAN DESENDING -- 상태변경이력
--  TABLE ACCESS BY INDEX ROWID == 장비
--    INDEX RANGE SCAN == 장비_N1

 

위 쿼리는 메인 쿼리의 칼럼을 서브쿼리 내의 인라인뷰가 참조할 수 없기때문에 비효율을 감안하고 작성한 쿼리입니다. ORACLE 12G 버전 이상 부터는 메인 쿼리의 칼럼을 서브쿼리 내의 인라인 뷰에서 참조할 수 있기 때문에 아래와 같은 쿼리가 잘 동작합니다.

 

-- 상태변경이력 테이블 PK_IDX : 장비번호 + 변경일자 + 변경순번

SELECT 장비번호, 장비명, 상태코드
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM
(
  SELECT 장비번호, 장비명, 상태코드
  ,(
    SELECT  변경일자 || LPAD(변경순번, 4) || 상태코드
    FROM (
           SELECT 변경일자, 변경순번, 상태코드
        FROM 상태변경이력
        WHERE 장비번호 = P.장비번호
        ORDER BY 변경일자 DESC, 변경순번 DESC
    ) 최종이력
   WHERE ROWNUM <= 1
   ) 
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

-- EXECUTION PLAN
-- SELECT STATEMENT
-- COUNT STOPKEY
--    VIEW
--      TABLE ACCESS BY INDEX ROWID -- 상태변경이력
--            INDEX RANGE SCAN DESENDING -- 상태변경이력
--      TABLE ACCESS BY INDEX ROWID OF 장비
--            INDEX RANGE SCAN OF 장비_N1

 

소트영역크기 조정

 

소트연산이 불가피하다면 메모리 내에서 작업을 완료해야합니다. 디스크 소트가 불가피할때, 임시 공간에 기록했다가 다시 읽는 횟수를 최적화해야합니다. 이를 위해 관리자가 시스템 레벨 또는 사용자가 세션 레벨에서 직접 소트 영역의 크기를 조정하는 작업이 필요합니다.

 

DB 관리자가 PGA_AGGREGATE_TARGET 파라미터를 통해 인스턴스가 전체적으로 이용가능한 PGA 메모리 총량을 지정하면, ORACLE이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리를 할당합니다.

 

자동 PGA 메모리 관리 기능을 활성화하려면 WORKAREA_SIZE_POLICY 를 AUTO로 설정하면 됩니다. 기본적으로 AUTO PGA 메모리 관리 방식이 활성화되고, 시스템과 세션 레벨의 수종 PGA 메모리 관리 방식으로 전환도 가능합니다.

 

트랜잭션이 거의 없는 야간에 대량 비치를 수행할 때는 수동 방식으로 변경하여 직접 크기를 조정하는 것이 효과적일 수 있습니다. 자동할당 시, 소트 영역을 사용중인 다른 프로세스가 없더라도 특정 프로세스가 그 공간을 사용할 수 없기 때문입니다. 즉, 메모리의 여유공간을 낭비하게 됩니다.