2.소트 튜닝 (by xsoft) [2013.11.13]
목차
1. 소트와 성능
2. 데이터 모댈 측면에서의 검토
3. 소트가 발생하지 않도록 SQL 작성
4. 인텍스를 이용한 소트 연산 대체
5. 소트 영역을 적게 사용하도록 SQL 작성
6. 소트 영역 크기 조정
1. 소트와 성능
- 소트(Sort) 오퍼레이션은 정해진 메모리 공간에 소트 영역(8ort Area)을 할당하고 정렬을 수행함.
- Oracle : 소트영역을 PGA영역에 할당
- SQL Server : 소트영역을 버퍼 캐시에 할당
- 소트 오퍼리에션 수행 시 메모리 공간이 부족할 때 디스크 공간을 사용함.
- Oracle : Temp Tablespace 사용
- SQL Server : tempdb 사용
- 가급적 소트 영역 내에서 정렬이 이루어 지는것이 좋으며, 디스크 공간 사용 시 SQL 수행 성능이 나빠짐
가) 메모리 소트와 디스크 소트
| 구분 | 설명 |
|---|---|
| 메모리(In-Memory) 소트 | 전체 데이터의 정렬 작업을 할당받은 소트 영역 내에 서 완료하는 것을 말하며, 'Internal Sort' 또는 'Optimal Sort'라고 함. |
| 디스크(To-Disk) 소트 | 할당받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, 'External Sort' 라고 함. 디스크에 임시 저장했다가 다시 읽는 작업을 반복한 횟수에 따라 디스크 소트를 다음 두 가지로 구분함. * Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록 * Multipass Sort : 정렬 대상 집합을 디스크에 여러 번 기록 |
나) 소트를 발생시키는 오퍼레이션
1) Sort Aggregate
- 전체 로우를 대상으로 집계할 때 나타나며, 'Sort'가 나오긴 하지만 실제 소트가 발생되진 않음
- SQL Server에서는 'Stream Aggregate'로 표현됨
SELECT SUM(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
;
-- Oracle
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
-- SQL Server
StmtText
-----------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Exprl014]=(O) 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
;
-- Oracle
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 532 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
-- SQL Server
StmtText
--------------------------------------------------------------------------------
|--Sort(ORDER BY: ([SQLPRO].[dbo].[emp].[sal] DESC))
|--Table Scan(OBJECT:([SQLPRO].[dbo].[emp]))
3) Sort Group By
- Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남
- Oracle 10g R2 이전 버전까지 사용되며, R2 이후부터는 'Hash Group By' 사용됨
- 'Sort Group By'는 소트를 통해 GROUP BY 연산을 하다보니 ORDER BY 구문이 없어도 자동으로 소트가 되나, 'Hash Group By'는 SORT로 처리되지 않으므로 정렬이 필요할 경우 ORDER BY를 넣어 주어야 함.
- R2 이후 SORT GROUP BY 하려면?
- Optimizer Mode를 Rule일 경우 발생 안함
- OPTIMIZER_FEATURES_ENABLE를 10.1로 함
- '_gby_hash_aggregation_enabled' = FALSE 로 변경함
-- Check Oracle Version
SELECT *
FROM V$VERSION
;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
;
-- Check Hidden parameter
NAME VALUE DEF_YN DESCRIPTION
------------------------------ ------ ------- ---------------------------------------------------
_gby_hash_aggregation_enabled TRUE TRUE ENABLE group-by and aggregation using hash scheme
;
-- Sort Group by
SELECT /*+ OPT_PARAM('_GBY_HASH_AGGREGATION_ENABLED', 'FALSE') */
DEPTNO,
JOB,
SUM(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
GROUP BY DEPTNO,
JOB
;
-- Oracle
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 6 (17)| 00:00:01 |
| 1 | SORT GROUP BY | | 11 | 165 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
-- SQL Server
StmtText
-----------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1014]=(O) THEN NULL ELSE [Exprl0l5] END))
|--Stream Aggregate(GROUP BY: )
|--Sort(ORDER BY:([SQLPRO].[dbo].[emp].[deptno] ASC, [SQLPRO].[dbo].[emp].[job] ASC))
|--Table Scan(OBJECT: ([SQLPRO].[dbo].[emp]))
- HASH GROUP BY 하려면?
- /*\+ USE_HASH_AGGREGATION \*/ 힌트 사용
SELECT /*+ USE_HASH_AGGREGATION */
DEPTNO,
JOB,
SUM(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
GROUP BY DEPTNO,
JOB
;
-- Oracle
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 6 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 165 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
4) Sort Unique
- 선택된 결과집합에서 중복 레코드를 제거하고자 할 때 나타남.
SELECT DISTINCT DEPTNO
FROM EMP
ORDER BY DEPTNO
;
-- Oracle
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 7 (29)| 00:00:01 |
| 1 | SORT UNIQUE | | 3 | 9 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
-- SQL Server
StmtText
-----------------------------------------------------------------------------------
|--Sort(DISTINCT ORDER BY: ([SQLPRO].[dbo].[emp].[deptno] ASC))
|--Table Scan(OBJECT: ([SQLPRO].[dbo].[emp]))
5) Sort Join
- Sort Merge Join을 수행할 때 나타남
-- Oracle
SELECT /*+ ORDERED USE_MERGE(E D) */
*
FROM EMP E,
DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 8 (13)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_N1 | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 80 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 80 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
;
-- SQL Server
SELECT *
FROM EMP E,
DEPT D
WHERE E.DEPTNO = D.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
;
-- Oracle
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 476 | 6 (17)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 476 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 476 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
-- SQL Server
StmtText
---------------------------------------------------------------------------
| --Sequence Project(DEFINE: ([Exprl004] =row number))
|-- Compute Scalar(DEFINE: ([Expr1006] = (1)))
|--Segment
|--Sort(ORDER BY: ([SQLPRO].[dbo].[emp].[hiredate] ASC))
|--Table Scan(OBJECT: ([SQLPRO].[dbo].[emp]))
다) 소트튜닝 요약
- 데이터 모댈 측면에서의 검토
- 소트가 발생하지 않도록 SQL 작성
- 인텍스를 이용한 소트 연산 대체
- 소트 영역을 적게 사용하도록 SQL 작성
- 소트 영역 크기 조정
2. 데이터 모댈 측면에서의 검토
- 자주 사용되는 액세스 패턴을 분석하지 않거나, M:M 관계를 해소하지 않아 소트 오퍼레이션을 수행하는 경우 많음
- 아래 <그림5-5>의 정규화 모델에서 '가입상품' 엔터티의 속성이 적다는 이유로 <그림5-6> 처럼 통합을 했는데, 이 회사가 고객별 가입상품 레벨의 데이터 조회가 빈번할 경우 문제가 됨
- 만약 <그림5-6>의 모델링에서 고객별 가입상품 레벨을 조회할 경우 '고객별상품라인'과 '과금'을 조인해야 하는데 M:M 관계이므로 1:M으로 만들어야 해서 항상 GROUP BY를 사용해야 함
- 이 때 불필요한 소트 연산이 발생하여 성능에 불리함.
SELECT 과금.고객ID,
과금.상품ID,
과금.과금액,
가입상품.가입일시
FROM 과금,
(SELECT 고객ID,
상품ID,
MIN(가입일시) 가입일시
FROM 고객별상품라인
GROUP BY 고객 ID,
상품ID
) 가입상품
WHERE 과금.고객 ID(+) = 가입상품.고객ID
AND 과금.상품ID(+) = 가입상품.상품ID
AND 과금.과금연월(+) = :YYYYMM
- 이를 <그림5-5>의 정규화에서 구할 경우 1:M 관계이므로 GROUP BY가 필요 없으며, 소트 연산도 불필요함.
SELECT 과금.고객ID,
과금.상품ID,
과금.과금액,
가입상품.가입일시
FROM 과금,
가입상품
WHERE 과금.고객ID(+) = 가입상품.고객ID
AND 괴금.상품ID(+) = 가입상품.상품ID
AND 과금.과금연월(+) = :YYYYMM
3. 소트가 발생하지 않도록 SQL 작성
가) Union을 Union All로 대체
- 만약 2개의 SQL 집합의 DISTINCT 연산이 필요 없을 경우 UNION으로 연결하지 말고 UNION ALL로 연결하여 소트 연산을 제거해야 함
- UNION 실행계획
SELECT EMPNO,
JOB,
MGR
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO,
JOB,
MGR
FROM EMP
WHERE DEPTNO = 20
;
-- Oracle
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 190 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 10 | 190 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_N1 | 5 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_N1 | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
;
-- SQL Server
StmtText
-----------------------------------------------------------------------------------------
|--Sort(mSTINCT ORDER BY:([Unionl008] ASC, [Union1009] ASC, [Unionl010] ASC))
|--Concatenation
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| |--Index Seek(OBJECT:([SQLPRO].[dbo].[emp].[emp_deptjdx]), SEEK:([deptno]=(10.)))
| |--RID Lookup(OBJECT:([SQLPRO].[dbo].[emp]), SEEK:([Bmk1000]=[BmklOOO]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmkl004]))
|--Index Seek(OBJECT:([SQLPROJ.[dbo].[emp].[emp_deptjdx]), SEEK:( deptno]=(20.)))
| -- RID Lookup( OBJECT: ([SQLPRO].[dbo].[emp]), SEEK:([Bmk1004] [Bmkl004]))
- UNION ALL 실행계획
SELECT EMPNO,
JOB,
MGR
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT EMPNO,
JOB,
MGR
FROM EMP
WHERE DEPTNO = 20
;
-- Oracle
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 190 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_N1 | 5 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_N1 | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
;
-- SQL Server
StmtText
------------------------------------------------------------------------------------------
|--Concatenation
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000)))
| |--Index Seek(OBJECT:([SQLPRO].[dbo].[emp].[emp_depUdx]), 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_depUdx]), SEEK: ([deptno]=(20.)))
|--RID Lookup(OBjECT:([SQLPRO].[dbo].[emp]), SEEK:([Bmk1004]=[Bmk1004)))
- 참고로 Select-List에 EMPNO 컬럼이 없을 경우 데이터가 다르게 나오므로, 충분히 데이터를 검토하여 변경해야 함
나) Distinct를 Exists 서브쿼리로 대체
- '연월테이블:과금' 관계가 1:M인 상태에서 특정 지역의 과금이 발생했던 연월을 조회하려고 함.
- 이 때 과금이 발생했던 데이터를 구하기 위해 M 집합인 과금 테이블에서 DISTINCT 처리로 계산할 경우 성능저하 및 과도한 소트 발생됨
SELECT DISTINCT 과금연월
FROM 과금
WHERE 과금연월 <= :YYYYMM
AND 지역 LIKE :REG 11 '%'
;
call count cpu elapsed 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
Rows Row Source Operation
------- -----------------------------------------------------------------------
35 HASH UNIQUE (cr=1586208 pr=32648 pw=O time=98704640 us)
9845517 PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=1586208 pr=32648
9845517 TABLE ACCESS FULL 과금 (cr=1586208 pr=32648 pw=O time=70155864 us)
- 이를 1 집합인 '연월테이블'에서 데이터를 구하고 '과금' 테이블은 Validation 체크만 하면 되므로 EXISTS로 처리할 경우 성능 개선 가능함
SELECT 연월
FROM 연월테이블 A
WHERE 연월 <= :YYYYMM
AND EXISTS (SELECT 'x'
FROM 과금
WHERE 과금연월 A. 연월
AND 지 역 LIKE :REG 11 '%')
call count cpu elapsed 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
Rows Row Source Operation
---- ------------------------------------------------------------------------------
35 NESTED LOOPS SEMI (cr=82 pr=O pw=O time=19568 us)
36 TABLE ACCESS FULL 연월테이블 (cr=6 pr=O pw=O t ime=557 us)
35 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=76 pr=O pw=O time=853 us)
35 INDEX RANGE SCAN 과금_N1 (cr=76 pr=O pw=O time=683 us)
다) 불필요한 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 elapsed 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
Rows Row Source Operation
---- ------------------------------------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=26112 pr=4742 pw=O time=17561372 us)
29184 TABLE ACCESS BY INDEX ROWID MEMBER (cr=26112 pr=4742 pw=O time=30885229 us)
33952 INDEX RANGE SCAN MEMBER_IDX01 (cr=105 pr=105 pw=O time=2042777 us)
- 이를 1건만 체크하도록 변경할 경우 성능 개선 가능
-- Oracle
DECLARE
l_cnt NUMBER;
BEGIN
SELECT 1
INTO l_cnt
FROM MEMBER
WHERE MEMB_CLS = '1'
AND BIRTH_YYYY <= '1950'
AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('not exists');
END;
call count cpu elapsed 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
Rows Row Source Operation
---- ------------------------------------------------------------------------------
0 STATEMENT
1 COUNT STOPKEY (cr=3 pr=O pw=O time=54 us)
1 TABLE ACCESS BY INDEX ROWID MEMBER (cr=3 pr=O pw=O time=46 us)
1 INDEX RANGE SCAN MEMBER_IDX01 (cr=2 pr=O pw=O time=26 us)
;
-- SQL Server
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 대처
- 조건절과 ORDER BY 절의 컬럼이 인덱스로 구성될 경우 소트 연산 대체 가능
-- ORDER BY 절의 컬럼이 인덱스에 없을 경우
SELECT *
FROM EMP
WHERE DEPTNO = 10
ORDER BY JOB
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 190 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_N1 | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
;
-- ORDER BY 절 컬럼이 인덱스에 있을 경우
CREATE INDEX APPS.EMP_N3 ON APPS.EMP (DEPTNO, JOB);
SELECT *
FROM EMP
WHERE DEPTNO = 10
ORDER BY JOB
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_N3 | 5 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
나) Sort Group By 대처
- 결합이나 단일 컬럼 인덱스에 포함되는 선두컬럼을 ORDER BY에 사용할 경우 GROUP BY 시 소트 연산 대처 가능
- SORT GROUP BY를 유도하기 위해 /*\+ NO_USE_HASH_AGGREGATION \*/ 힌트 사용
-- 일반 컬럼 사용 시
SELECT /*+ NO_USE_HASH_AGGREGATION */
JOB,
SUM(SAL),
COUNT(*)
FROM EMP
GROUP BY JOB
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 60 | 6 (17)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 60 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 168 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
;
-- 인덱스 컬럼 사용 시
SELECT /*+ NO_USE_HASH_AGGREGATION */
EMPNO,
SUM(SAL),
COUNT(*)
FROM EMP E
GROUP BY EMPNO
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 112 | 2 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 14 | 112 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 112 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_U1 | 14 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
다) 인텍스를 활용한 Min, Max 구하기
- 인덱스가 조건절 및 GROUP 함수의 컬럼으로 결합되어 있을 때, MIN/MAX를 사용할 경우 소트 연산 없이 빠르게 수행 가능함
SELECT MAX(JOB)
FROM EMP E
WHERE DEPTNO = :1
;
-- Oracle
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | FIRST ROW | | 1 | 11 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| EMP_N3 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
-- SQL Server
StmtText
------------------------------------------------------------------------------------------------
|--Stream Aggregate(DEFINE: ([Expr1004) =MAX([SQLPRO).[dbo].[emp].[empno))))
|--Top(TOP EXPRESSION:((l)))
|-- Index Seek(OBJECT: ([SQLPRO].[dbo].[주문].[주문_PK)), SEEK:( .... .. ) ORDERED BACKWARD)
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 상품번호
;
-- SQL_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 상품번호
)
;
- SQL_1 : 레코드당 105(30 + 30 + 10 + 20 + 15) 바이트로 가공된 결과치를 소트 결과에 담음
- SQL_2 : 가공되지 않은 상태로 소트를 하고 가공하므로, SQL_1보다 소트 영역을 적게 사용함
나) Top-N 쿼리
-- SQL_1(SQL Server)
SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
FROM 시간별종목거 래
WHERE 종목코드 = 'KR123456'
AND 거래일시 = '20080304'
;
-- SQL_2(DB2)
SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 시간별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 = '20080304'
ORDER BY 거래일시
FETCH FIRST 10 ROWS ONLY
;
-- SQL_3(Oracle)
SELECT *
FROM (SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 시간별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 = '20080304'
ORDER BY 거래일시
WHERE ROWNUM <= 10
;
Execution Plan
--------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
o COUNT (STOPKEY)
1 VIEW
2 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)
3 INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE))
- 인덱스가 <종목코드, 거래일시> 순으로 되어 있으면 소트 연산 대체 가능
1) Top-N 쿼리의 소트 부하 경감 원리(잘 이해 안됨. 배열을 만들어 사용하란 이야기인가? 그럼 PL/SQL?)
- <종목코드>만을 선두로 갖는 인덱스만 있을 경우 정렬 작업이 불가피하지만, Top\- N 쿼리 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻을 수 있음
- 예를 들어 Top 10 (rownum <= 10) 이면, <그림 III-5-8> 처럼 우선 10개의 레코드를 담을 배열을 할당하고 처음 읽은 107B 레코드를 정렬된 상태로 담음
(위에서 예시한 쿼리는 거래일시 순으로 정렬하고 있지만, 설명을 단순화하려고 숫자로 표현함) - 이후 읽는 레코드에 대해서는 맨 우측에 있는 값(=가장 큰 값)과 비교해서 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시토함.
(물론 맨 우측에 있던 값은 버림) - 이 방식으로 처리하면 전체 레코드를 정렬하지 않고도 오름차순(ASC)으로 최소값을 갖는 107H 레코드를 정확히 찾아낼 수 있음
2) Top-N 쿼리 알고리즘이 작동하지 못하는 정우
- TOP-N 쿼리 작동하는 경우
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 쿼리 작동하지 않는 경우
SELECT *
FROM (SELECT ROWNUM NO,
거래일시,
체결건수,
체결수량,
거래대금
FROM (SELECT 거래일시,
체결건수,
체결수량,
거래대금
FROM 시간별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304'
ORDER BY 거래일시
)
)
WHERE NO BETWEEN 91 AND 100
3) 윈도우 합수에서의 Top-N 쿼리
- TOP-N 쿼리 작동하지 않는 경우
SELECT 고객ID,
변경순번,
전화번호,
주소,
자녀수,
직업,
고객등급
FROM (SELECT 고객ID 변경순번,
MAX(변정순번) OVER(PARTITION BY 고객ID) 마지막변경순번,
전화번호,
주소,
자녀수,
직업,
고객등급
FROM 고객변경이력)
WHERE 변갱순번 = 마지막변청순번
- TOP-N 쿼리 작동하는 경우
SELECT 고객ID,
변경순번,
전화번호,
주소,
자녀수,
직업,
고객등급
FROM (SELECT 고객ID 변경순번,
RANK() OVER(PARTITION BY 고객ID ORDER BY 변경순번) RNUM
전화번호,
주소,
자녀수,
직업,
고객등급
FROM 고객변경이력)
WHERE RNUM = 1
6. 소트 영역 크기 조정
- SQL-Server는 소트 영역을 수동으로 조정하는 방법 제공하지 않음
가) 오라클 관리 방법
1) oracle-8i
- 데이터 정렬을 위해 사용하는 메모리 공간을 sort area_size 파라미터를 통해 조정함.
- 기본 값은 관리자가 지정하고 프로그램의 작업 내용에 따라 세션 레벨에서 아래와 같이 값을 조정하는 식
alter session set sort area size 1048576;
2) oracle-9i
- 9i부터는 '자동 PGA 메모리 관리 (Automatic PGA Memory Management)' 기능이 도입되어 사용자가 수동으로 크기를 조정하지 않아도 됨.
- DB 관리자가 pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량을 지정하면, Oracle이 시스템 부하 정도에 따라 자동으로 각 세션에 메모리를 할당함.
- 자동 PGA 메모리 관리 기능을 활성화하려면 workarea_size_policy를 auto로 설정하면 되는데, 9i부터 기본적으로 auto로 설정되어 있으며 sort area size 파라미터는 무시됨
- 수동 변경 방법
alter session set workarea_size_policy manual;
alter session set sort area size 10485760;
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2013년 11월 30일
- 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*