NL 조인은 기본적으로 아우터의 순서로 결과를 반환하지만, NLJ_BATCHING 방식으로 동작하는 NL 조인은 물리 I/O가 발생할 경우 아우터의 순서로 결과를 반환하지 않을 수 있습니다. 아울러 NL 조인의 이너에서 테이블을 액세스하지 않아도 아우터의 순서로 결과를 반환하지 않을 수 있습니다.
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 (c1, c2) AS SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);
아래 쿼리의 NL 조인은 NLJ_BATCHING 방식으로 동작합니다. 버퍼 캐시 flush하여 물리 I/O가 발생하고 이로 인해 결과가 아우터의 순서로 반환되지 않습니다.
-- 2
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT *
FROM (SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) */
ROWNUM AS rn, a.c1, b.c2
FROM t1 a, t2 b
WHERE a.c1 > 0
AND b.c1 = a.c1)
WHERE rn != c1;
...
116 행이 선택되었습니다.
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
| 2 | COUNT | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
|* 5 | INDEX RANGE SCAN | T1_X1 |
|* 6 | INDEX RANGE SCAN | T2_X1 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 |
------------------------------------------------
Outline Data
-------------
NLJ_BATCHING(@"SEL$2" "B"@"SEL$2")
NO_NLJ_BATCHING 힌트를 추가하면 NL 조인이 NLJ_PREFETCH 방식으로 동작하여 결과가 아우터 순서대로 반환됩니다. t2 테이블은 BATCH_TABLE_ACCESS_BY_ROWID 방식으로 조회되지만 NL 조인의 이너에서 로우 단위로 액세스되므로 결과 순서에 영향을 미치지 않습니다.
-- 3
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT *
FROM (SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) NO_NLJ_BATCHING(B) */
ROWNUM AS rn, a.c1, b.c2
FROM t1 a, t2 b
WHERE a.c1 > 0
AND b.c1 = a.c1)
WHERE rn != c1;
선택된 레코드가 없습니다.
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
| 2 | COUNT | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
| 4 | NESTED LOOPS | |
|* 5 | INDEX RANGE SCAN | T1_X1 |
|* 6 | INDEX RANGE SCAN | T2_X1 |
-------------------------------------------------------
Outline Data
-------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "B"@"SEL$2")
NLJ_PREFETCH(@"SEL$2" "B"@"SEL$2")
아래 쿼리의 NL 조인은 이너에서 테이블을 액세스하지 않습니다. 이로 인해 NO_NLJ_BATCHING 힌트를 사용해도 결과가 아우터 순서대로 반환되지 않습니다.
-- 4
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT *
FROM (SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) NO_NLJ_BATCHING(B) */
ROWNUM AS rn, a.c1
FROM t1 a, t2 b
WHERE a.c1 > 0
AND b.c1 = a.c1)
WHERE rn != c1;
...
54 행이 선택되었습니다.
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
| 2 | COUNT | |
| 3 | NESTED LOOPS | |
|* 4 | INDEX RANGE SCAN| T1_X1 |
|* 5 | INDEX RANGE SCAN| T2_X1 |
-------------------------------------
아래는 앞선 쿼리의 세션 통계입니다. Batched IO block miss count 값이 정렬되지 않은 로우 수와 유사한 것을 알 수 있습니다.
+---+--------------------------------+----------+------------+
| |NAME | VALUE|CLASS |
+---+--------------------------------+----------+------------+
| 2 |Batched IO (bound) vector count | 33.00 |Cache + SQL |
| |Batched IO block miss count | 101.00 |Cache + SQL | -- 116
| |Batched IO same unit count | 67.00 |Cache + SQL |
| |Batched IO single block count | 32.00 |Cache + SQL |
| |Batched IO slow jump count | 18.00 |Cache + SQL |
| |Batched IO vector block count | 2.00 |Cache + SQL |
| |Batched IO vector read count | 1.00 |Cache + SQL |
+---+--------------------------------+----------+------------+
| 3 |Batched IO (bound) vector count | 16.00 |Cache + SQL |
| |Batched IO single block count | 16.00 |Cache + SQL |
+---+--------------------------------+----------+------------+
| 4 |Batched IO (bound) vector count | 18.00 |Cache + SQL |
| |Batched IO block miss count | 54.00 |Cache + SQL | -- 54
| |Batched IO same unit count | 36.00 |Cache + SQL |
| |Batched IO single block count | 18.00 |Cache + SQL |
+---+--------------------------------+----------+------------+
NLJ_BATCHING 방식은 아래 파라미터와 관련이 있습니다.
NAME VALUE DEFAULT_VALUE DESCRIPTION
---------------------------- ----- ------------- -------------------------------------------------------
_nlj_batching_ae_flag 2 2 FAE flag type set after restoring to IO batching buffer
_nlj_batching_enabled 1 0 enable batching of the RHS IO in NLJ
_nlj_batching_misses_enabled 1 1 enable exceptions for buffer cache misses
_nlj_batching_misses_enabled 파라미터를 0으로 설정하고 쿼리를 수행하면 결과가 아우터 순서대로 반환됩니다. 참고로 _nlj_batching_enabled 파라미터를 0으로 설정하면 결과가 아우터 순서대로 반환되지 않습니다.
-- 6
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET "_nlj_batching_misses_enabled" = 0;
SELECT *
FROM (SELECT /*+ LEADING(A) USE_NL(B) INDEX(A) INDEX(B) */
ROWNUM AS rn, a.c1
FROM t1 a, t2 b
WHERE a.c1 > 0
AND b.c1 = a.c1)
WHERE rn != c1;
선택된 레코드가 없습니다.
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
| 2 | COUNT | |
| 3 | NESTED LOOPS | |
|* 4 | INDEX RANGE SCAN| T1_X1 |
|* 5 | INDEX RANGE SCAN| T2_X1 |
-------------------------------------