Oracle 21c에 _optimizer_nested_loop_join 파라미터가 추가되었습니다. 이 글에서 _optimizer_nested_loop_join 파라미터의 동작에 대해 살펴보겠습니다.
NAME TYPE VALUE DEFAULT_VALUE ISSES_MODIFIABLE DESCRIPTION
--------------------------- ---- ----- ------------- ---------------- ------------------------------
_optimizer_nested_loop_join 2 on on TRUE favor/unfavor nested loop join
NAME ORDINAL VALUE ISDEFAULT
--------------------------- ------- ----- ---------
_optimizer_nested_loop_join 1 ON FALSE --> TRUE?
_optimizer_nested_loop_join 2 OFF FALSE
_optimizer_nested_loop_join 3 FORCE FALSE
테스트를 위해 아래와 같이 테이블와 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 10');
CREATE TABLE t2 (c1, c2) AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
CREATE INDEX t2_x1 ON t2 (c1);
EXEC DBMS_STATS.GATHER_TABLE_STATS (NULL, 'T1');
EXEC DBMS_STATS.GATHER_TABLE_STATS (NULL, 'T2');
아래 쿼리는 NL 조인을 사용하는 실행 계획을 생성합니다.
-- 2
SELECT *
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 (100)| 10 | 14 |
| 1 | NESTED LOOPS | | 1 | 22 (0)| 10 | 14 |
| 2 | NESTED LOOPS | | 1 | 22 (0)| 10 | 12 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 2 (0)| 10 | 3 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 10 | 1 (0)| 10 | 9 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 2 (0)| 10 | 2 |
---------------------------------------------------------------------------------------
아래와 같이 OPT_PARAM('_optimizer_nested_loop_join' 'off') 힌트를 사용하면 NL 조인이 해시 조인으로 변경됩니다.
-- 3
SELECT /*+ OPT_PARAM('_optimizer_nested_loop_join' 'off') */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 (100)| 10 | 160 | |
|* 1 | HASH JOIN | | 1 | 32 (0)| 10 | 160 | 1313K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 2 (0)| 10 | 2 | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 30 (0)| 10000 | 158 | |
---------------------------------------------------------------------------------------
_optimizer_nested_loop_join 파라미터의 ON, OFF 설정은 힌트를 오버라이드하지 않고, FORCE 설정은 힌트를 오버라이드하는 것으로 보입니다. 아울러 동일한 실행 계획의 비용이 같다는 점에서 _optimizer_nested_loop_join 파라미터 설정이 옵티마이저의 비용 계산에 영향을 미치지 않음을 알 수 있습니다.
-- 4-1
SELECT /*+ OPT_PARAM('_optimizer_nested_loop_join' 'off') LEADING(A) USE_NL(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 (100)| 10 | 14 |
| 1 | NESTED LOOPS | | 1 | 22 (0)| 10 | 14 |
| 2 | NESTED LOOPS | | 1 | 22 (0)| 10 | 12 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 2 (0)| 10 | 3 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 10 | 1 (0)| 10 | 9 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 2 (0)| 10 | 2 |
---------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_optimizer_nested_loop_join' 'off')
1 - SEL$1
- LEADING(A)
4 - SEL$1 / "B"@"SEL$1"
- USE_NL(B)
-- 4-2
SELECT /*+ OPT_PARAM('_optimizer_nested_loop_join' 'on') LEADING(A) USE_HASH(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 (100)| 10 | 160 | |
|* 1 | HASH JOIN | | 1 | 32 (0)| 10 | 160 | 1211K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 2 (0)| 10 | 2 | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 30 (0)| 10000 | 158 | |
---------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_optimizer_nested_loop_join' 'on')
1 - SEL$1
- LEADING(A)
3 - SEL$1 / "B"@"SEL$1"
- USE_HASH(B)
-- 4-3
SELECT /*+ OPT_PARAM('_optimizer_nested_loop_join' 'force') LEADING(A) USE_HASH(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 (100)| 10 | 14 |
| 1 | NESTED LOOPS | | 1 | 22 (0)| 10 | 14 |
| 2 | NESTED LOOPS | | 1 | 22 (0)| 10 | 12 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 2 (0)| 10 | 3 |
|* 4 | INDEX RANGE SCAN | T2_X1 | 10 | 1 (0)| 10 | 9 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 2 (0)| 10 | 2 |
---------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_optimizer_nested_loop_join' 'force')
1 - SEL$1
- LEADING(A)
4 - SEL$1 / "B"@"SEL$1"
U - USE_HASH(B)
참고로 소트 머지 조인과 해시 조인은 아래 파라미터로 동작을 제어할 수 있습니다.
NAME TYPE VALUE DEFAULT_VALUE ISSES_MODIFIABLE DESCRIPTION
--------------------------------- ---- ----- ------------- ---------------- -------------------------------------
_optimizer_sortmerge_join_enabled 1 TRUE TRUE TRUE enable/disable sort-merge join method
_hash_join_enabled 1 TRUE TRUE TRUE enable/disable hash join
_optimizer_nested_loop_join 파라미터의 ON, OFF 설정처럼 두 파라미터 모두 힌트를 오버라이드하지 않습니다.
-- 5-1
SELECT /*+ OPT_PARAM('_optimizer_sortmerge_join_enabled' 'false') LEADING(A) USE_MERGE(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 159 | |
| 1 | MERGE JOIN | | 1 | 10 | 159 | |
| 2 | SORT JOIN | | 1 | 10 | 2 | 2048 (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 10 | 2 | |
|* 4 | SORT JOIN | | 10 | 10 | 157 | 1243K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 10000 | 157 | |
---------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_optimizer_sortmerge_join_enabled' 'false')
1 - SEL$1
- LEADING(A)
5 - SEL$1 / "B"@"SEL$1"
- USE_MERGE(B)
-- 5-2
SELECT /*+ OPT_PARAM('_hash_join_enabled' 'false') LEADING(A) USE_HASH(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 160 | |
|* 1 | HASH JOIN | | 1 | 10 | 160 | 1190K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 10 | 2 | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 158 | |
--------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_hash_join_enabled' 'false')
1 - SEL$1
- LEADING(A)
3 - SEL$1 / "B"@"SEL$1"
- USE_HASH(B)