오라클 성능 고도화 원리와 해법 II (2010년)
1. Sort Aggregate
- 'SORT AGGREGATE'는 전체 로우를 대상으로 집계를 수행할 때 나타나는데, 실제 SORT가 이루어지지는 않음
SET autotrace traceonly;
SELECT SUM(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
14 physical reads
0 redo size
276 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
;
2. Sort Order By
- 데이터 정렬을 위해 ORDER BY 오퍼레이션을 수행할 때 나타남.
SET autotrace traceonly;
SELECT *
FROM EMP
ORDER BY SAL DESC
;
Execution Plan
----------------------------------------------------------
Plan hash value: 4060621227
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1358 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1358 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1358 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1027 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
;
3. Sort Group By
- SORT GROUP BY는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남.
SET autotrace ON;
SELECT DEPTNO,
JOB,
SUM(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
;
DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)
---------- ------------------ ---------- ---------- ----------
10 CLERK 1300 1300 1300
10 MANAGER 2450 2450 2450
10 PRESIDENT 5000 5000 5000
20 ANALYST 6000 3000 3000
20 CLERK 1900 1100 800
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
30 MANAGER 2850 2850 2850
30 SALESMAN 5600 1600 1250
9 rows selected.
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 637087546
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 6 (17)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 518 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
;
- 10gR2부터 'HASH GROUP BY' 방식이 도임되면서, ORDER BY절을 함께 명시하지 않으면 대부분 HASH GROUP BY 방식으로 되며 소트를 하지 않음.
SET autotrace ON;
SELECT DEPTNO,
JOB,
SUM(SAL),
MAX(SAL),
MIN(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
;
DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)
---------- ------------------ ---------- ---------- ----------
30 MANAGER 2850 2850 2850
30 SALESMAN 5600 1600 1250
20 CLERK 1900 1100 800
10 MANAGER 2450 2450 2450
20 MANAGER 2975 2975 2975
20 ANALYST 6000 3000 3000
10 PRESIDENT 5000 5000 5000
30 CLERK 950 950 950
10 CLERK 1300 1300 1300
9 rows selected.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
Plan hash value: 1697595674
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 6 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 518 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
580 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
;
- 일반적으로 잘 못 알려진 사실 중 하나는 GROUP BY를 사용할 경우 소트를 보장한다는 것인데, 이는 8i부터 이미 소트를 보장하지 않는다고 밝혀짐.
- 소팅 알고리즘을 사용해 SORT GROUP BY된 결과집합은 논리적인 정렬 순서를 따라 포인터로 서로 연결되 있을 뿐, 물리적인 순서와 다를 수 있고,
ORDER BY를 명시하지 않았을 때는 논리적 순서를 무시하고 물리적 순서에 따라 결과를 출력하므로, 정렬을 보장하지 않음. - 즉, 실행계획에서 'SORT GROUP BY'의 의미는 '소팅 알고리즘을 사용해 값을 집계한다'는 뜻일 분 결과의 정렬을 의미하지 않음
- 그러므로 GROUP BY 사용 시 정렬이 필요할 경우 반드시 ORDER BY를 추가해야 함.
4. Sort Unique
- 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없을 때 UNNESTING 되어 조인으로 풀릴 경우, 그리고 세미 조인이 아닐 경우 SORT UNIQUE 오퍼레이션 수행
- 만약 서브쿼리의 컬럼이 PK 또는 Unique인 경우 SORT UNIQUE 오퍼레이션은 생략
SET autotrace traceonly;
SELECT /*+ ORDERED USE_NL(DEPT) */
*
FROM DEPT
WHERE DEPTNO IN (SELECT /*+ UNNEST */
DEPTNO
FROM EMP
WHERE JOB = 'CLERK')
;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 204 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 204 | 8 (13)| 00:00:01 |
| 2 | SORT UNIQUE | | 4 | 96 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 4 | 96 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 44 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PRIMARY_KEY | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOB"='CLERK')
5 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
395 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
;
- UNION, MINUS, INTERSECT, DISTINCT 구문도 마찬가지로 SORT UNIQUE 오퍼레이션이 나타남
-- 1. UNION
SET autotrace traceonly;
SELECT JOB,
MGR
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT JOB,
MGR
FROM EMP
WHERE DEPTNO = 20
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 296 | 12 (59)| 00:00:01 |
| 1 | SORT UNIQUE | | 8 | 296 | 12 (59)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 111 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 185 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
4 - filter("DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
62 consistent gets
0 physical reads
0 redo size
340 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
;
-- 2. MINUS
SET autotrace traceonly;
SELECT JOB,
MGR
FROM EMP
WHERE DEPTNO = 10
MINUS
SELECT JOB,
MGR
FROM EMP
WHERE DEPTNO = 20
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 296 | 12 (59)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 3 | 111 | 6 (17)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 111 | 5 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 185 | 6 (17)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 185 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
5 - filter("DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
62 consistent gets
0 physical reads
0 redo size
284 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
-- 3. DISTINCT
SET autotrace traceonly;
SELECT DISTINCT
DEPTNO
FROM EMP
ORDER BY DEPTNO
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 7 (29)| 00:00:01 |
| 1 | SORT UNIQUE | | 14 | 182 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
250 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
;
5. Sort Join
- SORT JOIN 오퍼레이션은 소트 머지 조인을 수행할 때 나타남
SET autotrace traceonly;
SELECT /*+ ORDERED USE_MERGE(E) */
*
FROM DEPT D,
EMP E
WHERE D.DEPTNO = E.DEPTNO
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1974 | 12 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 1974 | 12 (17)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 176 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 176 | 5 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 1358 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 1358 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30 consistent gets
14 physical reads
0 redo size
1313 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
;
6. Window Sort
- WINDOW SORT는 분석함수를 수행할 때 나타남
SET autotrace traceonly;
SELECT EMPNO,
ENAME,
JOB,
MGR,
SAL,
AVG(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1050 | 6 (17)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 1050 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1050 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
936 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
;
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2010년 06월 05일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3239
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.