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)