오라클 성능 고도화 원리와 해법 II (2010년)
I. Outer NL 조인
1. Outer NL 조인은 단방향 액세스 패턴을 가짐
- Outer NL 조인은 특성상 기준 테이블에서 Outer 대상 테이블로만 가는 단방향 액세스패턴을 가질 수 밖에 없음
- 단방향 액세스밖에 안되는 이유
-- 1. EMP, DEPT 데이터 구조
SELECT D.DEPTNO,
COUNT(E.DEPTNO) EMP_CNT,
COUNT(D.DEPTNO) DEPT_CNT
FROM EMP E,
DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
GROUP BY D.DEPTNO
ORDER BY D.DEPTNO
;
DEPTNO EMP_CNT DEPT_CNT
---------- ---------- ----------
10 9 9
20 90 90
30 900 900
40 9000 9000
50 90000 90000
60 1 1
70 0 1
80 0 1
90 0 1
;
-- 2. Outer 조인 결과
SELECT *
FROM EMP E,
DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
AND D.DEPTNO IN (60, 70, 80, 90)
;
EMPNO EMPNO_VARCHAR JOB HIREDATE DEPTNO DEPTNO DNAME
---------- ------------- ------ ------------ ---------- ---------- --------
100000 100000 A 18-MAY-28 60 60 SALES_6
80 SALES_8
90 SALES_9
70 SALES_7
- 조인결과를 보면 EMP 테이블에 3건의 NULL 레코드가 보인다.
이 때 Outer NL 조인을 하려면 드라이빙 테이블에 데이터가 있어야 Outer 여부를 확인할 수 있는데
EMP 테이블에는 이미 NULL 값 밖에 없기 때문에 이 NULL 값을 가지고 DEPT 테이블의 데이터와
비교를 할 수 없으므로 항상 데이터가 존재하는 DEPT 테이블만 드라이빙이 될 수 밖에 없다.
- 실행계획 확인
-- 0. 오라클 버전
SELECT * FROM v$version;
BANNER
---------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
-- 1. Outer NL 조인
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4003K| 702 (1)| 00:00:09 |
| 1 | NESTED LOOPS OUTER | | 100K| 4003K| 702 (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL | DEPT | 9 | 162 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 11111 | 249K| 134 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | EMP_N1 | 25000 | | 33 (4)| 00:00:01 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
-- 2. LEADING 힌트로 E -> D 유도
-- E -> D로 실행계획 안됨
EXPLAIN PLAN FOR
SELECT /*+ LEADING(E D) USE_NL(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4003K| 702 (1)| 00:00:09 |
| 1 | NESTED LOOPS OUTER | | 100K| 4003K| 702 (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL | DEPT | 9 | 162 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 11111 | 249K| 134 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | EMP_N1 | 25000 | | 33 (4)| 00:00:01 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
-- 3. LEADING 힌트보다 강력한 ORDERED 힌트로 E -> D 유도
-- E -> D로 실행계획 안됨
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_NL(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4003K| 702 (1)| 00:00:09 |
| 1 | NESTED LOOPS OUTER | | 100K| 4003K| 702 (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL | DEPT | 9 | 162 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 11111 | 249K| 134 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | EMP_N1 | 25000 | | 33 (4)| 00:00:01 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
2. 데이터 형변환 조인도 액세스 단방향 패턴
- Outer NL 조인 뿐만 아니라 데이터 형변환을 통해 조인이 된 경우에도 단방향 액세스 패턴과 같다고 볼 수 있음
-- 1. 이 경우는 형변환 안한 테이블에 조건이 들어와 형변환 한곳으로 넘어가야 하는데 인덱스가 없으므로 FULL SCAN함
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(E_1 E_2) */
*
FROM EMP E_1,
EMP E_2
WHERE E_1.EMPNO = TO_NUMBER(E_2.EMPNO_VARCHAR)
AND E_1.HIREDATE BETWEEN :1 AND :2
--AND E_2.HIREDATE BETWEEN :1 AND :2
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 11500 | 28606 (3)| 00:05:44 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 250 | 11500 | 28606 (3)| 00:05:44 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 250 | 5750 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_N2 | 450 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 23 | 114 (2)| 00:00:02 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:1)<=TO_DATE(:2))
4 - access("E_1"."HIREDATE">=:1 AND "E_1"."HIREDATE"<=:2)
5 - filter("E_1"."EMPNO"=TO_NUMBER("E_2"."EMPNO_VARCHAR"))
-- 2. 이 경우는 형변환 한 테이블에 조건이 들어와 형변환 안한곳으로 넘어가는데 인덱스가 있으므로 INDEX SCAN함
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(E_1 E_2) */
*
FROM EMP E_1,
EMP E_2
WHERE E_1.EMPNO = TO_NUMBER(E_2.EMPNO_VARCHAR)
--AND E_1.HIREDATE BETWEEN :1 AND :2
AND E_2.HIREDATE BETWEEN :1 AND :2
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 11500 | 255 (0)| 00:00:04 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 250 | 11500 | 255 (0)| 00:00:04 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 250 | 5750 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_N2 | 450 | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 23 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:1)<=TO_DATE(:2))
4 - access("E_2"."HIREDATE">=:1 AND "E_2"."HIREDATE"<=:2)
6 - access("E_1"."EMPNO"=TO_NUMBER("E_2"."EMPNO_VARCHAR"))
3. 데이터 형변환 조인을 양방향 액세스 패턴으로 변경하는 방법
- 위의 내용처럼 형변환을 통해 조인한 경우 단방향 액세스 패턴과 같은 현상을 보이는데
이를 극복하기 위해 양쪽에 모두 형변환을 해주어 양방향 패턴이 가능하도록 해주면 해결 가능 - 단 양쪽 조인컬럼 모두 인덱스가 있을 경우임!!!
EXPLAIN PLAN FOR
SELECT /*+ USE_NL(E_1 E_2) */
*
FROM EMP E_1,
EMP E_2
WHERE E_1.EMPNO = TO_NUMBER(E_2.EMPNO_VARCHAR)
AND TO_CHAR(E_1.EMPNO) = E_2.EMPNO_VARCHAR -- 조건 추가
AND E_1.HIREDATE BETWEEN :1 AND :2
--AND E_2.HIREDATE BETWEEN :1 AND :2
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 11500 | 505 (0)| 00:00:07 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 23 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 250 | 11500 | 505 (0)| 00:00:07 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 250 | 5750 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_N2 | 450 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_N3 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:1)<=TO_DATE(:2))
5 - access("E_1"."HIREDATE">=:1 AND "E_1"."HIREDATE"<=:2)
6 - access("E_2"."EMPNO_VARCHAR"=TO_CHAR("E_1"."EMPNO"))
filter("E_1"."EMPNO"=TO_NUMBER("E_2"."EMPNO_VARCHAR"))
II. Outer 소트 머지 조인
Outer 소트 머지 조인도 Outer NL 조인과 마찬가지로 단방향 액세스
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_MERGE(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4003K| | 811 (2)| 00:00:10 |
| 1 | MERGE JOIN OUTER | | 100K| 4003K| | 811 (2)| 00:00:10 |
| 2 | SORT JOIN | | 9 | 162 | | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 9 | 162 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 100K| 2246K| 7864K| 807 (2)| 00:00:10 |
| 5 | TABLE ACCESS FULL| EMP | 100K| 2246K| | 117 (3)| 00:00:02 |
------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
filter("D"."DEPTNO"="E"."DEPTNO"(+))
III. Outer 해시 조인
1. 소개
- Outer 해시 조인도 9i까지는 단방향 액세스 패턴이었으나, 10g에서 성능상의 이유로 변경됨
-- 1. 오라클 버전
SELECT * FROM v$version;
BANNER
------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
-- 2. 세션 레벨에서 오라클 버전 9i로 변경
ALTER SESSION SET optimizer_features_enable = '9.2.0';
-- 3. 실행계획 확인
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4003K| 67 |
|* 1 | HASH JOIN OUTER | | 100K| 4003K| 67 |
| 2 | TABLE ACCESS FULL| DEPT | 9 | 162 | 2 |
| 3 | TABLE ACCESS FULL| EMP | 100K| 2246K| 64 |
-----------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('9.2.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
Note
-----
- cpu costing is off (consider enabling it)
;
-- 4. 10g로 변경
ALTER SESSION SET optimizer_features_enable = '10.2.0.3';
-- 5. 다시 확인
-- 책의 내용이 일부 잘못된 것으로 보임.
-- SQL 내용 변경 없이 SWAP_JOIN_INPUTS(D) -> SWAP_JOIN_INPUTS(E)로 변경해야 함
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 14 | 798 | 11 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
SWAP_JOIN_INPUTS(@"SEL$1" "E"@"SEL$1")
USE_HASH(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
- 9i에의 해시 조인 알고리즘
- Outer 집합인 DEPT 테이블을 해시 테이블로 빌드(Build)함
- Inner 집합인 EMP 테이블을 읽으면서 해시 테이블을 탐색(Probe)
- 조인에 성공한 레코드는 곧바로 결과집합에 삽입하고, 조인에 성공했음을 해시 엔트리에 표시(그림_1)
- Probe 단계가 끝나면 Inner 조인과 동일한 결과집합이 만들어진 상태.
이제 조인에 실패했던 레코드를 결과집합에 포함시켜야 하므로 해시 테이블을 스캔하면서
체크가 없는 DEPT 엔트리를 결과집합에 삽입한다(그림_2)
- 그림_1
- 그림_2
- 하지만 일반적으로 해시 조인을 할 때 집합이 작은 대상이 Outer가 되어야 성능에 좋음에도 불구하고
집합이 큰 대상이 Outer가 되는 경우가 종종 있어 성능에 치명적인 영향을 주게 되었다. - 이에 오라클은 10g에서 Outer가 아닌 Inner 또한 빌드가 가능하도록 추가한 것이 Right Outer 해시 조인이다.
2. 의문사항(논의 사항)
- 즉, Right Outer 해시 조인 탄생 배경은 집합이 작은 대상을 빌드로 만드는 것이 목적인데 실제 Trace를 보면 Block을 더 읽는다.
_
왜 그럴까?
-- 1. Normal Outer Hash Join
/*
USE_HASH(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
*/
SELECT /*+ USE_HASH(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
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 10002 0.000 0.440 0 10363 0 100003
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10004 0.000 0.440 0 10363 0 100003
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100003 HASH JOIN OUTER (cr=10363 pr=0 pw=0 time=201178 us)
9 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=130 us)
100000 TABLE ACCESS FULL EMP (cr=10360 pr=0 pw=0 time=69 us)
-- 2. Right Outer Hash Join
/*
SWAP_JOIN_INPUTS(@"SEL$1" "E"@"SEL$1")
USE_HASH(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
*/
SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10002 0.170 0.440 0 413 0 100003
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10004 0.170 0.443 0 413 0 100003
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100003 HASH JOIN RIGHT OUTER (cr=413 pr=0 pw=0 time=265771 us)
100000 TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=112 us)
9 TABLE ACCESS FULL DEPT (cr=9 pr=0 pw=0 time=139 us)
- 보는 것처럼 여기서 'Right Outer Hash Join'은 'SWAP_JOIN_INPUTS(E)'으로 인해 EMP를
빌드하고 DEPT를 Probe 하였는데 I/O는 10,363 -> 413로 줄어들었다.(1/25 감소) - 여기서 의문사항 2가지
- 'Right Outer Hash Join'의 진정한 의미는 작은 집합이 아닌 큰 집합을 빌드하는 것이 효과적인 것인가?
- 트레이스 실행계획은 'E->D'로 풀렸는데 Outline 힌트에 보면 'LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")'이다.
이는 무엇으로 설명을 해야 하나?
- 이에 대해 비투엔에서 답변한 내용
- 질문 : http://www.b2en.com/comm/performance/view.asp?sfield=&svalue=&Page=&pageSize=&no=84
제목 : bulid input을 큰 집합으로 할때 성능이 더 좋은 case가 있어 추가 질문 드립니다.
안녕하세요? 조시형 선생님.
역시나, 작은 쪽 테이블을 bulid input으로 선택하기 위해 그렇게 표현을 한 것이군요.
답변 감사드립니다.
그런데 제가 한번 emp테이블에 데이터를 많이 넣고 테스트를 해보았는데 emp테이블을 bulid input으로
한 결과가 더 좋게 나온게 있어 이에 대해 문의를 한번 더 드립니다.
아래의 스크립트에서 dept는 9건, emp는 10만건을 넣어 본 뒤 dept와 emp를 각각 bulid input하여 테스트를
해 보았습니다.
-- 1. oracle version
SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
-- 2. create table
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP AS
SELECT -- 1-1) EMPNO(Unique 컬럼)
LEVEL EMPNO,
-- 1-2) EMPNO(VARCHAR로 변형)
TO_CHAR(LEVEL) EMPNO_VARCHAR,
-- 1-3) JOB(10만건 데이터를 20개 그룹으로 분류)
CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
-- 1-4) HIREDATE(10개씩 증가하여 날짜 데이터 만듬)
TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
-- 1-5) DEPTNO(자리수 길이를 한 그룹으로 하여 데이터 생성, 분포도 차이나도록)
LENGTH(LEVEL) * 10 DEPTNO
FROM DUAL
CONNECT BY LEVEL <= 100000
;
CREATE TABLE DEPT AS
SELECT LEVEL * 10 DEPTNO,
'SALES_' || LEVEL DNAME,
'ZONE_' || LEVEL LOC
FROM DUAL
CONNECT BY LEVEL <= 9
;
-- 3. create index
CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_U1 NOPARALLEL;
CREATE INDEX EMP_N1 ON EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N1 NOPARALLEL;
CREATE INDEX EMP_N2 ON EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N2 NOPARALLEL;
CREATE INDEX EMP_N3 ON EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N3 NOPARALLEL;
CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO);
-- 4. gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);
-- 5. dept bulid input
SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(D) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10002 0.010 0.681 0 10363 0 100003
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10004 0.010 0.685 0 10363 0 100003
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100003 HASH JOIN OUTER (cr=10363 pr=0 pw=0 time=212801 us)
9 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=11638 us)
100000 TABLE ACCESS FULL EMP (cr=10360 pr=0 pw=0 time=97 us)
-- 6. emp bulid input
SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10002 0.160 0.559 0 413 0 100003
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10004 0.160 0.562 0 413 0 100003
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100003 HASH JOIN RIGHT OUTER (cr=413 pr=0 pw=0 time=267378 us)
100000 TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=112 us)
9 TABLE ACCESS FULL DEPT (cr=9 pr=0 pw=0 time=150 us)
;
보시는 것처럼 dept를 bulid input할 때 emp를 10,360블록 액세스한 반면, emp를 bulid input할 때는 emp를
404블록만 액세스한것이 보입니다.
이렇게 데이터가 많은 emp를 bulid input한 결과가 더 좋게 나온 이유는 왜 그런것일까요?
책에서 소개해 주신것처럼 bulid input한 테이블은 가급적 집합이 작은 테이블을 만드는것이 유리하다고
하셨는데 말이죠. 제 추측으로는 집합이 큰 테이블이라 하더라도 조인키의 NDV 값이 작다면 bulid input할 때
해시 버킷을 적절히 나눌 수 있어 좋을것 같다고 예측만 될 뿐인데...
조시형 선생님께서는 어떻게 생각하시는지 궁금합니다.
답변이 늦어져 죄송합니다. 회사 사정상 늘 프로젝트를 병행하다 보니
프로젝트 일정이 바쁠 때는 빨리 답변하기 곤란할 때가 잦습니다. 양해 바랍니다.
올려주신 스크립트를 간단히 살펴봤는데, 원인은 해시 조인 메커니즘(작은 테이블을 Build Input으로 삼아야
빠르다)에 있지 않고, 다량의 데이터베이스 Call 때문에 블록 읽기가 증가한 데에 있습니다. dept를
Build Input으로 삼았을 때는 블록 I/O가 10,363이고, emp를 Build Input으로 삼았을 때는 413입니다.
왜 이런 차이가 발생할까요?
이 원리를 이해하시려면 오라클 성능 고도화 1권 5장에 설명한 ′데이터베이스 Call 최소화 원리′를 먼저
이해하셔야 합니다. 그중에서도, 1권 5장 5절 (3)항(381~385 페이지)을 참조하시기 바랍니다.
해당 내용을 읽기 전에 먼저 아래 테스트를 수행해 보시죠.
1. select-list를 ′*′로 하지 마시고 아래와 같이 바꿔 보세요.
select count(dept_nullable_column), count(emp_nullable_column) from ...
그러면 데이터베이스 Call이 한 번만 발생하면서 그 Call 내에서 모든 조인 처리를 완료하겠죠.
그러면 데이터베이스 Call 횟수에 영향을 받지 않고 순수하게 해시 조인 메커니즘에 의한 성능 차이를
비교할 수 있습니다.
2. Array Size를 5,000쯤으로 증가시켜 테스트해 보는 것도 방법입니다. 현재 Fetch Call이 10,004번
발생했는데, Array Size를 5,000으로 증가시키면 불과 2~3번의 Fetch Call이 발생할 것입니다.
그러면 Fetch Call에 의한 블록 I/O 증가량은 아주 미미할 테니, 순수하게 해시 조인 메커니즘 성능
차이를 비교할 수 있습니다.
3. 또는 양쪽 쿼리모두 emp 쪽 테이블을 아래와 같이 order by가 발생하도록 인라인 뷰로 씌웁니다.
from (select rownum, emp.* from EMP order by empno) e, dept d
그러면 Fetch Call이 다량 발생하더라도 Sort Area에서 액세스하므로 추가적인 CR 읽기가 발생하지 않습니다.
위 테스트를 모두 수행해 보고 1권 5장 5절에 설명한 원리를 충분히 이해하신다면, 테스트 결과를 어렵지
않게 해석할 수 있습니다. 그리고 해시 조인할 때 작은 집합을 Build Input으로 삼아야 유리하다는 원리에도
변함이 없음을 이해하실 것입니다.
참고로 이와 관련된 내용을 이미 설명해 놓은 부분이 있는데, 2권 p131 상단 첫 번째 문단을 참조하시기 바랍니다.
질문하신 예제에선, 책의 예제처럼 Fetch Call마다 반복적인 클러스터 스캔이 발생하지 않는다는 점만 다를 뿐,
작은 쪽 테이블을 Build Input으로 삼았는데 오히려 더 느려지는 현상은 동일하므로 이해하시는 데 도움이 되실 겁니다.
답변이 되셨기를 바랍니다.
조시형 드림
- 테스트 결과
-- 1. 테스트_1
SELECT /*+ USE_HASH(D E) */
count(e.hiredate), count(d.dname)
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.150 0.846 402 407 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.160 0.849 402 407 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=407 pr=402 pw=0 time=845766 us)
100003 HASH JOIN OUTER (cr=407 pr=402 pw=0 time=243128 us)
9 TABLE ACCESS FULL DEPT (cr=3 pr=2 pw=0 time=12719 us)
100000 TABLE ACCESS FULL EMP (cr=404 pr=400 pw=0 time=29205 us)
-- 2. 테스트_2
set arraysize 5000
SELECT /*+ USE_HASH(D E) */
*
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
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 22 0.23 0.93 402 427 0 100003
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.23 0.93 402 427 0 100003
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
100003 HASH JOIN OUTER (cr=427 pr=402 pw=0 time=236644 us)
9 TABLE ACCESS FULL DEPT (cr=3 pr=2 pw=0 time=6392 us)
100000 TABLE ACCESS FULL EMP (cr=424 pr=400 pw=0 time=28159 us)
-- 3. 테스트_3
SELECT /*+ USE_HASH(D E) */
*
FROM (select rownum, emp.* from EMP order by empno) e,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10002 0.190 0.752 400 407 0 100003
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10004 0.190 0.757 400 407 0 100003
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100003 HASH JOIN OUTER (cr=407 pr=400 pw=0 time=623806 us)
9 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=105 us)
100000 VIEW (cr=404 pr=400 pw=0 time=422547 us)
100000 SORT ORDER BY (cr=404 pr=400 pw=0 time=322541 us)
100000 COUNT (cr=404 pr=400 pw=0 time=102073 us)
100000 TABLE ACCESS FULL EMP (cr=404 pr=400 pw=0 time=2072 us)
IV. Full Outer 조인
1. 'Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)' 이용
-- 1. 테이블 생성
DROP TABLE DEPOSIT PURGE;
DROP TABLE PAYMENT PURGE;
EXEC DBMS_RANDOM.SEED(150);
CREATE TABLE DEPOSIT
AS
SELECT ROWNUM CNT
, ROUND(DBMS_RANDOM.VALUE(1, 20)) CUSTOMER_ID
, ROUND(DBMS_RANDOM.VALUE(1000, 100000),-2) DEPOSIT_AMOUNT
FROM DUAL CONNECT BY LEVEL <= 10;
CREATE TABLE PAYMENT
AS
SELECT ROWNUM CNT
, ROUND(DBMS_RANDOM.VALUE(1, 20)) CUSTOMER_ID
, ROUND(DBMS_RANDOM.VALUE(1000, 10000),-2) PAYMENT_AMOUNT
FROM DUAL CONNECT BY LEVEL <= 10;
-- 2. 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPOSIT');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PAYMENT');
-- 3. Full Outer 데이터 출력
SELECT A.CUSTOMER_ID,
A.DEPOSIT_AMOUNT,
B.PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
FROM DEPOSIT
GROUP BY CUSTOMER_ID) A,
(SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) B
WHERE B.CUSTOMER_ID(+) = A.CUSTOMER_ID
UNION ALL
SELECT CUSTOMER_ID,
NULL,
PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) A
WHERE NOT EXISTS (SELECT 1
FROM DEPOSIT
WHERE CUSTOMER_ID = A.CUSTOMER_ID)
;
CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT
----------- -------------- --------------
2 23900 6200
3 26600 2300
13 6800 14500
19 40400 6900
1 23100
6 71000
4 121900
8 95700
18 34300
7 3900
17 9200
16 7500
9 1300
13 rows selected.
-- 4. 실행계획
EXPLAIN PLAN FOR
SELECT A.CUSTOMER_ID,
A.DEPOSIT_AMOUNT,
B.PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
FROM DEPOSIT
GROUP BY CUSTOMER_ID) A,
(SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) B
WHERE B.CUSTOMER_ID(+) = A.CUSTOMER_ID
UNION ALL
SELECT CUSTOMER_ID,
NULL,
PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) A
WHERE NOT EXISTS (SELECT 1
FROM DEPOSIT
WHERE CUSTOMER_ID = A.CUSTOMER_ID)
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 477 | 16 (63)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN OUTER | | 9 | 468 | 9 (34)| 00:00:01 |
| 3 | VIEW | | 9 | 234 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 9 | 63 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPOSIT | 10 | 70 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 8 | 208 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 8 | 48 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| PAYMENT | 10 | 60 | 3 (0)| 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 9 | 8 (25)| 00:00:01 |
|* 10 | HASH JOIN ANTI | | 1 | 9 | 7 (15)| 00:00:01 |
| 11 | TABLE ACCESS FULL | PAYMENT | 10 | 60 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | DEPOSIT | 10 | 30 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."CUSTOMER_ID"(+)="A"."CUSTOMER_ID")
10 - access("CUSTOMER_ID"="CUSTOMER_ID")
-- 5. Trace 내용
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.010 0.010 0 12 0 13
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.010 0.012 0 12 0 13
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
13 UNION-ALL (cr=12 pr=0 pw=0 time=9851 us)
9 HASH JOIN OUTER (cr=6 pr=0 pw=0 time=9843 us)
9 VIEW (cr=3 pr=0 pw=0 time=3492 us)
9 HASH GROUP BY (cr=3 pr=0 pw=0 time=3481 us)
10 TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=66 us)
8 VIEW (cr=3 pr=0 pw=0 time=350 us)
8 HASH GROUP BY (cr=3 pr=0 pw=0 time=342 us)
10 TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=46 us)
4 HASH GROUP BY (cr=6 pr=0 pw=0 time=292 us)
5 HASH JOIN ANTI (cr=6 pr=0 pw=0 time=150 us)
10 TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=20 us)
10 TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=27 us)
2. ANSI Full Outer 조인
- ANSI 구문으로 인해 사용법은 더 편해졌지만 1번 내용과 동일한 실행계획 보임
-- 1. ANSI 구문을 활용한 출력 결과
SELECT NVL(A.CUSTOMER_ID, B.CUSTOMER_ID) CUSTOMER_ID,
A.DEPOSIT_AMOUNT,
B.PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
FROM DEPOSIT
GROUP BY CUSTOMER_ID) A
FULL OUTER JOIN
(SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
;
CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT
----------- -------------- --------------
2 23900 6200
3 26600 2300
13 6800 14500
19 40400 6900
1 23100
6 71000
4 121900
8 95700
18 34300
7 3900
17 9200
16 7500
9 1300
13 rows selected.
;
-- 2. 실행계획
EXPLAIN PLAN FOR
SELECT NVL(A.CUSTOMER_ID, B.CUSTOMER_ID) CUSTOMER_ID,
A.DEPOSIT_AMOUNT,
B.PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
FROM DEPOSIT
GROUP BY CUSTOMER_ID) A
FULL OUTER JOIN
(SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 884 | 28 (15)| 00:00:01 |
| 1 | VIEW | | 17 | 884 | 28 (15)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 9 | 468 | 9 (34)| 00:00:01 |
| 4 | VIEW | | 9 | 234 | 4 (25)| 00:00:01 |
| 5 | HASH GROUP BY | | 9 | 63 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | DEPOSIT | 10 | 70 | 3 (0)| 00:00:01 |
| 7 | VIEW | | 8 | 208 | 4 (25)| 00:00:01 |
| 8 | HASH GROUP BY | | 8 | 48 | 4 (25)| 00:00:01 |
| 9 | TABLE ACCESS FULL | PAYMENT | 10 | 60 | 3 (0)| 00:00:01 |
| 10 | HASH GROUP BY | | 8 | 48 | 19 (6)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | TABLE ACCESS FULL | PAYMENT | 10 | 60 | 3 (0)| 00:00:01 |
| 13 | SORT GROUP BY NOSORT| | 1 | 3 | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | DEPOSIT | 1 | 3 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID"(+))
11 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "DEPOSIT" "DEPOSIT"
WHERE "CUSTOMER_ID"=:B1 GROUP BY "CUSTOMER_ID"))
14 - filter("CUSTOMER_ID"=:B1)
;
-- 3. Trace 내용(I/O도 더 증가함)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.010 0.010 0 33 0 13
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.010 0.015 0 33 0 13
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
13 VIEW (cr=33 pr=0 pw=0 time=10088 us)
13 UNION-ALL (cr=33 pr=0 pw=0 time=10086 us)
9 HASH JOIN OUTER (cr=6 pr=0 pw=0 time=10079 us)
9 VIEW (cr=3 pr=0 pw=0 time=9189 us)
9 HASH GROUP BY (cr=3 pr=0 pw=0 time=9180 us)
10 TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=112 us)
8 VIEW (cr=3 pr=0 pw=0 time=448 us)
8 HASH GROUP BY (cr=3 pr=0 pw=0 time=447 us)
10 TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=44 us)
4 HASH GROUP BY (cr=27 pr=0 pw=0 time=359 us)
5 FILTER (cr=27 pr=0 pw=0 time=335 us)
10 TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=44 us)
4 SORT GROUP BY NOSORT (cr=24 pr=0 pw=0 time=153 us)
4 TABLE ACCESS FULL DEPOSIT (cr=24 pr=0 pw=0 time=127 us)
3. Native Hash Full Outer 조인
- 위처럼 ANSI Full Outer 조인을 사용할 때 I/O가 나빠지는 것을 극복하기 위해
오라클은 11g에서 'Native Hash Full Outer 조인'을 선보였고 10.2.0.4에서도
OPT_PARAM 힌트(_optimizer_native_full_outer_join)를 사용하여 가능하도록 지원한다.
-- 1. ANSI 구문을 활용한 출력 결과
SELECT /*+ OPT_PARAM('_optimizer_native_full_outer_join', 'force') */
NVL(A.CUSTOMER_ID, B.CUSTOMER_ID) CUSTOMER_ID,
A.DEPOSIT_AMOUNT,
B.PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
FROM DEPOSIT
GROUP BY CUSTOMER_ID) A
FULL OUTER JOIN
(SELECT CUSTOMER_ID,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM PAYMENT
GROUP BY CUSTOMER_ID) B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
;
CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT
----------- -------------- --------------
6 71000
7 3900
17 9200
8 95700
16 7500
3 26600 2300
1 23100
2 23900 6200
18 34300
19 40400 6900
13 6800 14500
4 121900
9 1300
13 rows selected.
;
-- 2. 실행계획과 Trace는 버전이 낮은 관계로 테스트 못함 -_-;
4. Union All을 이용한 Full Outer 조인
- 하지만 'Native Hash Full Outer 조인'과 동일한 I/O 효과를 볼 수 있는 방법이 있는데
그것은 Outer 대상의 양쪽 키를 NULL을 포함하여 UNION ALL로 묶은 뒤 GROUP BY로 압축하게 되면
동일한 결과를 보여주면서 성능향상을 높일 수 있다.
-- 1. 추출 결과
SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
DEPOSIT_AMOUNT,
TO_NUMBER(NULL) PAYMENT_AMOUNT
FROM DEPOSIT
UNION ALL
SELECT CUSTOMER_ID,
TO_NUMBER(NULL) DEPOSIT_AMOUNT,
PAYMENT_AMOUNT
FROM PAYMENT)
GROUP BY CUSTOMER_ID
;
CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT
----------- -------------- --------------
6 71000
7 3900
17 9200
8 95700
16 7500
3 26600 2300
1 23100
2 23900 6200
18 34300
19 40400 6900
13 6800 14500
4 121900
9 1300
13 rows selected.
;
-- 2. 실행계획
EXPLAIN PLAN FOR
SELECT CUSTOMER_ID,
SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT,
SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
FROM (SELECT CUSTOMER_ID,
DEPOSIT_AMOUNT,
TO_NUMBER(NULL) PAYMENT_AMOUNT
FROM DEPOSIT
UNION ALL
SELECT CUSTOMER_ID,
TO_NUMBER(NULL) DEPOSIT_AMOUNT,
PAYMENT_AMOUNT
FROM PAYMENT)
GROUP BY CUSTOMER_ID
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 3. Trace 내용(I/O는 가장 좋음)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 3 0.000 0.003 0 6 0 13
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 5 0.000 0.005 0 6 0 13
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
13 HASH GROUP BY (cr=6 pr=0 pw=0 time=3492 us)
20 VIEW (cr=6 pr=0 pw=0 time=98 us)
20 UNION-ALL (cr=6 pr=0 pw=0 time=96 us)
10 TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=62 us)
10 TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=31 us)
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2010년 04월 03일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3211
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.