🌠3-6-1. 소트 튜닝

과목3. SQL 고급 활용 및 튜닝

제6장 고급 SQL 튜닝

제1절 소트 튜닝

1. 소트와 성능

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

SQL 수행 도중 소트(Sort) 오퍼레이션이 필요할 때마다 DBMS는 정해진 메모리 공간에 소트 영역(Sort Area)을 할당하고 정렬을 수행한다. Oracle은 소트 영역을 PGA(Private Global Area) 영역에 할당하고, SQL Server는 버퍼 캐시에 할당한다고 1장에서 설명하였다. 컴퓨터에서 이루어지는 모든 작업이 그렇듯, 소트 오퍼레이션도 메모리 공간이 부족할 땐 디스크 공간을 사용한다. Oracle에선 Temp Tablespace를 이용하고, SQL Server에선 tempdb를 이용한다. 가급적 소트 영역 내에서 데이터 정렬 작업을 완료하는 것이 최적이지만, 대량의 데이터를 정렬할 땐 디스크 소트가 불가피하다. 특히, 전체 대상 집합을 디스크에 기록했다가 다시 읽는 작업을 여러 번 반복하는 경우 SQL 수행 성능은 극도로 나빠진다.

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

소트 튜닝 방안을 본격적으로 설명하기에 앞서, 어떨 때 소트가 발생하는지부터 살펴보자. Oracle 실행계획에 나타나는 오퍼레이션 형태를 기준으로 설명하며, 같은 오퍼레이션이 SQL Server 실행계획에선 어떻게 표시되는지도 함께 제시한다.

1) Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타나며, 아래와 같이 Oracle 실행계획에 'sort'라는 표현이 사용됐지만 실제 소트가 발생하진 않는다. SQL Server 실행계획엔 'Stream Aggregate'라고 표시된다.

SELECT SUM(SAL)
    , MAX(SAL)
    , MIN(SAL)
 FROM EMP 

		Execution Plan
0		SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)
1	0	SORT (AGGREGATE) (Card=1 Bytes=4)
2	1	TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=56)

StmtText
| --Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1014]=(0)THEN NULL ELSE [Expr1015] END))>
| --Stream Aggregate(DEFINE: )>
| --Table Scan(OBJECT:([SQLPRO].[dbo].[emp]))>

2) Sort ORDER BY : 정렬된 결과집합을 얻고자 할 때 나타난다.

SELECT *
 FROM EMP
ORDER BY SAL DESC 

		Execution Plan
0		SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=518)
1	0	SORT (ORDER BY) (Cost=4 Card=14 Bytes=518)
2	1	TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)

StmtText
| --Sort(ORDER BY:([SQLPRO].[dbo].[emp].[sal] DESC))
| --Table Scan(OBJECT:([SQLPRO].[dbo].[emp]))

3) Sort GROUP BY : Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

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

		Execution Plan
0		SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 4 Card = 11 Bytes = 165)
1	0	SORT ( GROUP BY ) (Cost = 4 Card = 11 Bytes = 165)
2	1	TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost = 3 Card = 14 Bytes = 210)

StmtText
--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1014] =(0) THEN NULL ELSE [Expr1015] END))
| --Stream Aggregate(GROUP BY: )
| --Sort(ORDER BY:([SQLPRO].[dbo].[emp].[deptno] ASC,[SQLPRO].[dbo].[emp].[job] ASC))
| --Table Scan(OBJECT:([SQLPRO].[dbo].[emp]))

Oracle은 Hashing 알고리즘으로 그룹별 집계를 수행하기도 하는데, 그때는 실행계획에 아래와 같이 표시된다.

		Execution Plan
0		SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165)
1	0	HASH (GROUP BY) (Cost=4 Card=11 Bytes=165)
2	1	TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210)

4) Sort Unique : 선택된 결과집합에서 중복 레코드를 제거하고자 할 때 나타난다. UNION 연산자나 아래와 같이 DISTINCT 연산자를 사용할 때가 대표적이다.

SELECT DISTINCT DEPTNO
 FROM EMP
ORDER BY DEPTNO 

		Execution Plan
0		SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=6)
1	0	SORT (UNIQUE) (Cost=4 Card=3 Bytes=6)
2	1	TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=26)

StmtText
--Sort(DISTINCT ORDER BY:([SQLPRO].[dbo].[emp].[deptno] ASC))
--Table Scan(OBJECT:([SQLPRO].[dbo].[emp]))

5) Sort Join : Sort Merge Join을 수행할 때 나타난다.

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

		Execution Plan
0		SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=3K Bytes=177K)
1	0	MERGE JOIN (Cost=11 Card=3K Bytes=177K)
2	1	SORT (JOIN) (Cost=4 Card=13 Bytes=442)
3	2	TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=442)
4	1	SORT (JOIN) (Cost=7 Card=654 Bytes=19K)
5	4	TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=654 Bytes=19K)

SELECT *
 FROM EMP E
    , DEPT D
WHERE D.DEPTNO = E.DEPTNO
OPTION (FORCE ORDER, MERGE JOIN) 

StmtText
| --Merge Join(Inner Join, MANY-TO-MANY MERGE: )
| --Sort(ORDER BY:([e].[deptno] ASC))
| | --Table Scan(OBJECT:([SQLPRO].[dbo].[emp] AS [e]))
| --Sort(ORDER BY:([d].[deptno] ASC))
| --Table Scan(OBJECT:([SQLPRO].[dbo].[dept] AS [d]))

6) Window Sort : 윈도우 함수를 수행할 때 나타난다.

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

		Execution Plan
0		SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=364)
1	0	WINDOW (SORT) (Cost=4 Card=13 Bytes=364)
2	1	TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=364)

StmtText
--Sequence Project(DEFINE:([Expr1004]=row_number))
--Compute Scalar(DEFINE:([Expr1006]=(1)))
--Segment
--Sort(ORDER BY:([SQLPRO].[dbo].[emp].[hiredate] ASC))
--Table Scan(OBJECT:([SQLPRO].[dbo].[emp]))

다. 소트 튜닝 요약

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

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

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

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

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

• 소트 영역 크기 조정

2. 데이터 모델 측면에서의 검토

자주 사용되는 데이터 액세스 패턴을 고려하지 않은 채 물리 설계를 진행하거나, M:M 관계의 테이블을 해소하지 않아 핵심 프로그램이 항상 소트 오퍼레이션을 수반하고 그로 인해 시스템 성능이 좋지 못한 경우를 흔히 접할 수 있다. 예를 들어 보자. 정상적인 데이터 모델은 [그림 Ⅲ-5-5]와 같다.

PK 외에 관리할 속성이 아예 없거나 [그림 Ⅲ-5-5]의 '가입상품'처럼 소수(여기서는 가입일시 하나뿐임)일 때, 테이블 개수를 줄인다는 이유로 자식 테이블에 통합시키는 경우를 종종 볼 수 있다. '가입상품' 테이블을 없애고 [그림 Ⅲ-5-6]처럼 '고객별상품라인'에 통합하는 식이다.

정보 누락이 없고, 가입일시는 최초 입력 후 변경되지 않는 속성이므로 정합성에도 문제가 안 생기겠지만 이 회사는 고객별 가입상품 레벨의 데이터 조회가 매우 빈번하게 발생한다. 그때마다 아래 처럼 '고객별상품라인' 테이블을 GROUP BY 해야 한다면 성능이 좋을 리 없다.

SELECT 과금.고객ID
    , 과금.상품ID
    , 과금.과금액
    , 가입상품.가입일시
 FROM 과금
    , (SELECT 고객ID
                , 상품ID
                , MIN(가입일시) 가입일시
           FROM 고객별상품라인
          GROUP BY 고객ID
                   , 상품ID) 가입상품
WHERE 과금.고 = 가입상품.상품ID
  AND 과금.과금연월(+) = :yyyymm 

만약 [그림 Ⅲ-5-5]처럼 잘 정규화된 데이터 모델을 사용했다면 쿼리도 아래 처럼 간단해지고 시스템 전반의 성능 향상에도 도움이 된다.

SELECT 과금.고객ID
    , 과금.상품ID
    , 과금.과금액
    , 가입상품.가입일시
 FROM 과금
    , 가입상품
WHERE 과금.고객ID(+) = 가입상품.고객ID
  AND 과금.상품ID(+) = 가입상품.상품ID
  AND 과금.과금연월(+) = :yyyymm 

데이터 모델 때문에 소트 부하를 일으키는 사례는 무궁무진하다. GROUP BY, UNION, DISTINCT 같은 연산자가 심하게 많이 사용되는 패턴을 보인다면 대개 데이터 모델이 잘 정규화되지 않았음을 암시한다. 데이터 모델 이상(異常)으로 발생한 데이터 중복을 제거하려다 보니 소트 오퍼레이션을 수행하는 것이다.

3. 소트가 발생하지 않도록 SQL 작성

가. UNION을 UNION ALL로 대체

데이터 모델 측면에선 이상이 없는데, 불필요한 소트가 발생하도록 SQL을 작성하는 경우가 있다. 예를 들어, 아래 처럼 UNION을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique 연산을 수행한다. 반면, UNION ALL은 중복을 허용하며 두 집합을 단순히 결합하므로 소트 연산이 불필요하다.

SELECT EMPNO
    , JOB, MGR
 FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO
    , JOB, MGR
 FROM EMP
WHERE DEPTNO = 20; 

		Execution Plan
0		SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=8 Bytes=120)
1	0	SORT (UNIQUE) (Cost=8 Card=8 Bytes=120)
2	1	UNION-ALL
3	2	TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)
4	3	INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)
5	2	TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)
6	5	INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)

SQL Server

StmtText
--Sort(DISTINCT ORDER BY:([UNION1008] ASC, [UNION1009] ASC, [UNION1010] ASC))
--Concatenation
--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
--Index Seek(OBJECT:([SQLPRO].[dbo].[emp].[emp_dept_idx]), SEEK:([deptno]=(10.)))
--RID Lookup(OBJECT:([SQLPRO].[dbo].[emp]), SEEK:([Bmk1000]=[Bmk1000]))
--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))
--Index Seek(OBJECT:([SQLPRO].[dbo].[emp].[emp_dept_idx]), SEEK:( deptno]=(20.)))
--RID Lookup(OBJECT:([SQLPRO].[dbo].[emp]), SEEK:([Bmk1004]=[Bmk1004]))

위 쿼리에선 PK 칼럼인 empno를 SELECT-list에 포함하므로 두 집합간에는 중복 가능성이 전혀 없다. UNION을 사용하든 UNION ALL을 사용하든 결과집합이 같으므로 UNION ALL을 사용하는 것이 마땅하다. 아래는 UNION 대신 UNION ALL을 사용했을 때의 실행계획이다.

		Execution Plan
0		SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=8 Bytes=120)
1	0	UNION-ALL
2	1	TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)
3	2	INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)
4	1	TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)
5	4	INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)

SQL Server

StmtText
--Concatenation
--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
--Index Seek(OBJECT:([SQLPRO].[dbo].[emp].[emp_dept_idx]), SEEK:([deptno]=(10.)))
--RID Lookup(OBJECT:([SQLPRO].[dbo].[emp]), SEEK:([Bmk1000]=[Bmk1000]))
--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))
--Index Seek(OBJECT:([SQLPRO].[dbo].[emp].[emp_dept_idx]), SEEK:([deptno]=(20.)))
--RID Lookup(OBJECT:([SQLPRO].[dbo].[emp]), SEEK:([Bmk1004]=[Bmk1004]))

참고로, SELECT-list에 empno가 없다면 10번과 20번 부서에 job, mgr이 같은 사원이 있을 수 있으므로 함부로 UNION ALL로 바꿔선 안 된다.

나. DISTINCT를 EXISTS 서브쿼리로 대체

중복 레코드를 제거하려고 DISTINCT를 사용하는 경우도 대표적인데, 대부분 EXISTS 서브쿼리로 대체함으로써 소트 연산을 제거할 수 있다. 예를 들어, 아래는 특정 지역(:reg)에서 특정월(:yyyymm) 이전에 과금이 발생했던 연월을 조회하는 쿼리다.

SELECT DISTINCT 과금연월
 FROM 과금
WHERE 과금연월< = :yyyymm
  AND 지역like :reg || '%'

Call	Count	CPU Time	Elapsed Time	Disk	Query	Current	Rows
Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	4	27.65	98.38	32648	1586208	0	35
-----	-----	-----	-------	------	------	------	-----
total	6	27.65	98.38	32648	1586208	0	35

5 개의 행이 선택되었습니다.

Rows	Row Source Operation
35	HASH UNIQUE (cr=1586208 pr=32648 pw=0 time=98704640 us)
9845517	PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=1586208 pr=32648 )
9845517	TABLE ACCESS FULL 과금 (cr=1586208 pr=32648 pw=0 time=70155864 us

입력한 과금연월(yyyymm) 이전에 발생한 과금 데이터를 모두 스캔하는 동안 1,586,208개 블록을 읽었고, 무려 1,000만 건에 가까운 레코드에서 중복 값을 제거하고 고작 35건을 출력했다. 매우 비효율적인 방식으로 수행되었고, 쿼리 소요시간은 1분 38초다. 각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 되므로 쿼리를 아래 처럼 바꿀 수 있다.

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

Call	Count	CPU Time	Elapsed Time	Disk	Query	Current	Rows
Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	4	0.00	0.01	0	82	0	35
-----	-----	------	--------	------	------	------	-----
total	6	0.00	0.01	0	82	0	35

5 개의 행이 선택되었습니다.

Rows	Row Source Operation
35	NESTED LOOPS SEMI (cr=82 pr=0 pw=0 time=19568 us)
36	TABLE ACCESS FULL 연월테이블 (cr=6 pr=0 pw=0 time=557 us)
35	PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=76 pr=0 pw=0 time=853 us)
35	INDEX RANGE SCAN 과금_N1 (cr=76 pr=0 pw=0 time=683 us)

연월테이블을 먼저 드라이빙해 과금 테이블을 EXISTS 서브쿼리로 필터링하는 방식이다. EXISTS 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행을 마친다는 점이다. 따라서 과금 테이블에 [과금연월 + 지역] 순으로 인덱스를 구성해 주기만 하면 가장 최적으로 수행될 수 있다. 그 결과, 소트가 발생하지 않았으며 82개 블록만 읽고 0.01초 만에 작업이 끝났다.

다. 불필요한 Count 연산 제거

아래는 데이터 존재 여부만 확인하면 되는데 불필요하게 전체 건수를 Count하는 경우다.

DECLARE
   l_cnt NUMBER;
BEGIN
   SELECT COUNT(*)
     INTO l_cnt
     FROM MEMBER
    WHERE MEMB_CLS = '1'
          AND BIRTH_YYYY <= '1950';
   IF L_CNT > 0 THEN
     dbms_output.PUT_LINE('EXISTS');
   ELSE
     dbms_output.PUT_LINE('not EXISTS');
   END IF;
END; 

Call	Count	CPU Time	Elapsed Time	Disk	Query	Current	Rows
Parse	1	0.000	0.000	0	0	0	0
Execute	1	0.000	0.000	0	0	0	0
Fetch	2	0.172	17.561	4742	26112	0	1
----	----	---------	----------	----	----	-----	----
Total	4	0.172	17.561	4742	26112	0	1

5 개의 행이 선택되었습니다.

Rows	Row Source Operation
0	STATEMENT
1	SORT AGGREGATE (cr=26112 pr=4742 pw=0 time=17561372 us)
29184	TABLE ACCESS BY INDEX ROWID MEMBER (cr=26112 pr=4742 pw=0 time=30885229 us)
33952	INDEX RANGE SCAN MEMBER_IDX01 (cr=105 pr=105 pw=0 time=2042777 us)

위 쿼리는 26,112개 블록 I/O가 발생하면서 17.56초나 소요되었다. 총 26,112개 중 디스크 I/O가 4,742개나 되는 것이 성능을 저하시킨 주요인이다. 쿼리를 아래와 같이 바꾸고 나면 블록 I/O가 단 3개뿐이므로 디스크 I/O 발생 여부와 상관없이 항상 빠른 성능을 보장한다

DECLARE
   L_CNT NUMBER;
BEGIN
   SELECT 1
     INTO L_CNT
     FROM 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; 

Call	Count	CPU Time	Elapsed Time	Disk	Query	Current	Rows
Parse	1	0.000	0.000	0	0	0	0
Execute	1	0.000	0.000	0	0	0	0
Fetch	2	0.000	0.000	0	3	0	1
----	-----	---------	----------	-----	-----	------	-----
Total	4	0.000	0.000	0	3	0	1

5 개의 행이 선택되었습니다.

Rows	Row Source Operation
0	STATEMENT
1	COUNT STOPKEY (cr=3 pr=0 pw=0 time=54 us)
1	TABLE ACCESS BY INDEX ROWID MEMBER (cr=3 pr=0 pw=0 time=46 us)
1	INDEX RANGE SCAN MEMBER_IDX01 (cr=2 pr=0 pw=0 time=26 us)

QL Server에선 rownum 대신 Top N 구문을 사용하면 되고, 아래와 같이 EXISTS 절을 사용하는 방법도 있다.

DECLARE @cnt INT

SELECT @cnt = COUNT(*)
WHERE EXISTS (SELECT 'x'
                FROM MEMBER
               WHERE MEMB_CLS = '1'
                     AND BIRTH_YYYY <= '1950')

IF @cnt > 0
 PRINT 'EXISTS'
ELSE
 PRINT 'not EXISTS' 

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

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

가. Sort ORDER BY 대체

아래 쿼리를 수행할 때 [region + custid] 순으로 구성된 인덱스를 사용한다면 sort ORDER BY 연산을 대체할 수 있다.

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)

ORDER BY 절을 사용했음에도 불구하고 실행계획에 sort ORDER BY 오퍼레이션이 나타나지 않았다. 이 방식으로 수행되면 region = 'A' 조건을 만족하는 전체 로우를 읽지 않고도 정렬된 결과집합을 얻을 수 있어 OLTP 환경에서 극적인 성능 개선 효과를 가져다 준다. 물론, 소트해야 할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있을 때만 유용하다. 만약 인덱스를 스캔하면서 결과집합을 끝까지 Fetch 한다면 오히려 I/O 및 리소스 사용 측면에서 손해다. 대상 레코드가 소량일 때는 소트가 발생하더라도 부하가 크지 않아 개선 효과도 미미하다.

나. Sort GROUP BY 대체

방금 본 customer 테이블 예시에서 region이 선두 칼럼인 결합 인덱스나 단일 칼럼 인덱스를 사용하면 아래 쿼리에 필요한 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 값을 빠르게 추출할 수 있다. 예를 들어, 주문 테이블에서 일자별 주문번호를 관리한다고 하자. 그러면 PK 인덱스를 [주문일자 + 주문번호] 순으로 구성해 주는 것만으로 아주 빠르게 마지막 주문번호를 찾을 수 있다. 아래 실행계획에서 FIRST ROW와 MIN/MAX 오퍼레이션이 나타난 것을 확인하기 바란다.

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))

아래는 SQL Server에서의 실행계획이다.

StmtText
| --Stream Aggregate(DEFINE:([Expr1004]=MAX([SQLPRO].[dbo].[emp].[empno])))
| --Top(TOP EXPRESSION:((1)))
| --Index Seek(OBJECT:([SQLPRO].[dbo].[주문].[주문_PK]),SEEK:(……)ORDERED BACKWARD)

주의할 점은, 아래와 같이 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))

사실 max 함수 내에서 인덱스 칼럼에 상수 값을 더할 때는 결과가 틀려질 가능성이 없다. 그럼에도 Oracle 옵티마이저는 인덱스 사용을 거부하지만 SQL Server는 인덱스를 정상적으로 사용한다.

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

소트 연산이 불가피하다면 메모리 내에서 처리되게 하려고 노력해야 한다. 소트 영역 크기를 늘리는 방법도 있지만 그전에 소트 영역을 적게 사용할 방법부터 찾는 것이 순서다.

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

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

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

1번 SQL은 레코드당 105(=30+30+10+20+15) 바이트(헤더 정보는 제외하고 데이터 값만)로 가공된 결과치를 소트 영역에 담는다. 반면 2번 SQL은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 1번 SQL에 비해 소트 영역을 훨씬 적게 사용한다. 실제 테스트해 보면 소트 영역 사용량에 큰 차이가 나는 것을 관찰할 수 있다.

나. Top-N 쿼리

Top-N 쿼리 형태로 작성하면 소트 연산(=값 비교) 횟수와 소트 영역 사용량을 최소화할 수 있다. 우선 Top-N 쿼리 작성법부터 살펴보자. SQL Server나 Sybase는 Top-N 쿼리를 아래와 같이 손쉽게 작성할 수 있다.

SELECT TOP 10 거래일시
             , 체결건수
             , 체결수량
             , 거래대금
 FROM 시간별종목거래
WHERE 종목코드 = 'KR123456'
  AND 거래일시 >= '20080304' 

IBM DB2에서도 아래와 같이 쉽게 작성할 수 있다.

SELECT 거래일시
    , 체결건수
    , 체결수량
    , 거래대금
 FROM 시간별종목거래
WHERE 종목코드 = 'KR123456'
  AND 거래일시 >= '20080304'
ORDER BY 거래일시
FETCH FIRST 10 ROWS ONLY 

Oracle에서는 아래 처럼 인라인 뷰로 한번 감싸야 하는 불편함이 있다.

SELECT *
 FROM (SELECT 거래일시
              , 체결건수
              , 체결수량
              , 거래대금
         FROM 시간별종목거래
        WHERE 종목코드 = 'KR123456'
              AND 거래일시 >= '20080304'
        ORDER BY 거래일시)
WHERE ROWNUM = 10 

위 쿼리를 수행하는 시점에 [종목코드 + 거래일시] 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를 이용함으로써 ORDER BY 연산을 대체할 수 있다. 아래 실행계획에서 'SORT ORDER BY' 오퍼레이션이 나타나지 않은 것을 확인하기 바란다.

		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))

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

• Top-N 쿼리의 소트 부하 경감 원리 [종목코드 + 거래일시] 순으로 구성된 인덱스가 없을 때는 어떤가? 종목코드만을 선두로 갖는 다른 인덱스를 사용하거나 Full Table Scan 방식으로 처리할 텐데, 이때는 정렬 작업이 불가피하다. 하지만 Top-N 쿼리 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻게 된다. 예를 들어 Top 10 (rownum < = 10)이면, [그림 Ⅲ-5-8]처럼 우선 10개 레코드를 담을 배열을 할당하고 처음 읽은 10개 레코드를 정렬된 상태로 담는다. (위에서 예시한 쿼리는 거래일시 순으로 정렬하고 있지만, 설명을 단순화하려고 숫자로 표현하였다.)

이후 읽는 레코드에 대해서는 맨 우측에 있는 값(=가장 큰 값)과 비교해서 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도한다. 물론 맨 우측에 있던 값은 버린다. 이 방식으로 처리하면 전체 레코드를 정렬하지 않고도 오름차순(ASC)으로 최소값을 갖는 10개 레코드를 정확히 찾아낼 수 있다. 이것이 Top-N 쿼리가 소트 연산 횟수와 소트 영역 사용량을 줄여주는 원리다.

• Top-N 쿼리 알고리즘이 작동하지 못하는 경우 1절에서, 앞쪽 일부 페이지만 주로 조회할 때의 가장 표준적인 페이징 처리 구현 방식은 아래와 같다고 설명하였다. 한 페이지에 10개씩 출력한다고 가정하고, 10 페이지를 출력하는 예시다. (설명의 편의를 위해 바인드 변수 대신 상수를 사용하였다.)

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 쿼리 알고리즘이 작동해 소트 부하만큼은 최소화할 수 있다고 설명하였다. 쿼리를 아래와 같이 작성하면 WHERE절 하나를 줄이고도 같은 결과집합을 얻을 수 있어 더 효과적인 것처럼 보인다. 하지만 그 순간부터 Top-N 쿼리 알고리즘은 작동하지 않는다.

SELECT *
 FROM (SELECT ROWNUM no
              , 거래일시
              , 체결건수
              , 체결수량
              , 거래대금
         FROM (SELECT 거래일시
                      , 체결건수
                      , 체결수량
                      , 거래대금
                 FROM 시간별종목거래
                WHERE 종목코드 = 'KR123456'
                      AND 거래일시 >= '20080304'
                ORDER BY 거래일시))
WHERE NO BETWEEN 91 AND 100 

• 윈도우 함수에서의 Top-N 쿼리 윈도우 함수를 이용해 마지막 이력 레코드를 찾는 경우를 보자. 아래는 max() 함수를 사용하는 SQL이다.

SELECT 고객ID
    , 변경순번
    , 전화번호
    , 주소
    , 자녀수
    , 직업
    , 고객등급
 FROM (SELECT 고객ID
              , 변경순번
              , MAX(변경순번)
                  OVER (
                    PARTITION BY 고객ID) 마지막변경순번
              , 전화번호
              , 주소
              , 자녀수
              , 직업
              , 고객등급
         FROM 고객변경이력)
WHERE 변경순번 = 마지막변경순번 

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

SELECT 고객ID
    , 변경순번
    , 전화번호
    , 주소
    , 자녀수
    , 직업
    , 고객등급
 FROM (SELECT 고객ID
              , 변경순번
              , RANK()
                  OVER (
                    PARTITION BY 고객ID
                    ORDER BY 변경순번) rnum
              , 전화번호
              , 주소
              , 자녀수
              , 직업
              , 고객등급
         FROM 고객변경이력)
WHERE RNUM = 1 

6. 소트 영역 크기 조정

SQL Server에서는 소트 영역을 수동으로 조정하는 방법을 제공하지 않으므로 여기서는 Oracle 중심으로 설명하기로 하자. 소트가 불가피하다면, 메모리 내에서 작업을 완료할 수 있어야 최적이다. 디스크 소트가 불가피할 땐, 임시 공간에 기록했다가 다시 읽는 횟수를 최소화할 수 있어야 최적이다. 이를 위해 관리자가 시스템 레벨에서, 또는 사용자가 세션 레벨에서 직접 소트 영역 크기를 조정하는 작업이 필요할 수 있다. Oracle 8i까지는 데이터 정렬을 위해 사용하는 메모리 공간을 sort_area_size 파라미터를 통해 조정했었다. 기본 값은 관리자가 지정하고, 프로그램의 작업 내용에 따라 세션 레벨에서 아래와 같이 값을 조정하는 식이다.

ALTER SESSION SET SORT_AREA_SIZE = 1048576; 

9i부터는 '자동 PGA 메모리 관리(Automatic PGA Memory Management)' 기능이 도입되었기 때문에 사용자가 일일이 그 크기를 조정하지 않아도 된다. DB 관리자가 pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량을 지정하면, Oracle이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리를 할당해 준다. 자동 PGA 메모리 관리 기능을 활성화하려면 workarea_size_policy를 auto로 설정하면 되는데, 9i부터 기본적으로 auto로 설정돼 있으며 sort_area_size 파라미터는 무시된다. 기본적으로 자동 PGA 메모리 관리 방식이 활성화되지만 시스템 또는 세션 레벨에서 '수동 PGA 메모리 관리' 방식으로 전환할 수 있다. 특히, 트랜잭션이 거의 없는 야간에 대량의 배치 Job을 수행할 때는 수동 방식으로 변경하고 직접 크기를 조정하는 것이 효과적일 수 있다. 왜냐하면, 자동 PGA 메모리 관리 방식 하에서는 프로세스당 사용할 수 있는 최대 크기가 제한되기 때문이다. 즉 소트 영역을 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸 수 없는 것이다. 결국 수 GB의 여유 메모리를 두고도 이를 충분히 활용하지 못해 작업 시간이 오래 걸릴 수 있다. 그럴 때 아래와 같이 workarea_size_policy 파라미터를 세션 레벨에서 manual로 변경하고, 필요한 만큼(최대 2,147,483,647 바이트) 소트 영역 크기를 늘림으로써 성능을 향상시키고, 궁극적으로 전체 작업 시간을 크게 단축시킬 수 있다.

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

ALTER SESSION SET SORT_AREA_SIZE = 10485760; 

출처 : 데이터온에어 – 한국데이터산업진흥원(https://dataonair.or.kr)