1 NL조인 Prefetch 방식[편집]

1.1 테이블 Prefetch[편집]

인덱스 rowid에 의한 후행(inner) 테이블 엑세스가 Nested loops 위쪽에 표시 되는 것
- 플랜에서 후행(Inner) 테이블의 TABLE ACCESS BY INDEX ROWID 부분이 NESTED LOOPS 위에 나타남

- 테이블 엑세스 단계에서 Prefetch 기능이 적용되었음을 플랜에 표시
Prefetch 기능이 실제로 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 대기 이벤트가 나타남
-> 병렬로 처리 된다는 것
선행(Outer) 테이블 스캔 결과와 후행(Inner) 인덱스가 NL조인을 하고 병렬로 TABLE ACCESS BY INDEX ROWID 부분을 처리 하는 방식

1.1.1 NLJ Prefetch 가 나타나는 경우[편집]

후행(Inner) 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 항상 나타남
후행(Inner) 쪽 테이블이 Unique Scan일 경우 나타나지 않음
( 후행(Inner) 테이블 조인결과가 1건이여서 병렬로 엑세스 할것이 없기때문)
후행(Inner) 쪽 Unique 인덱스를 Non-Unique 조건으로 (모든 인덱스 구성컬럼이 '=' 조건이 아닐때) Range Scan 할 때 항상 나타남
후행(Inner) 쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 경우 테이블 prefetch 실행계획이 나타날 수 있다.
1. 이때 인덱스는 Range Scan으로 엑세스 한다.
2. 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 엑세스

1.1.2 NLJ Prefetch 효과[편집]

디스크 I/O에 의한 대기 횟수 감소
인덱스 클러스터링 팩터가 나쁠 때 특히 효과가 있음
1. CF가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문

1.2 NL조인 배치 I/O[편집]

선행 테이블 에 DISK I/O 가 발생하게 될 시 바로 DISK I/O 를 수행하지 않고 , 일정량의 I/O 작업이 모이면 , 한번의 I/O CALL 로 여러 개의 블록을 읽어 들이 는 기능
후행(Inner) 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리
인덱스와의 조인을 모두 완료하고 나서 테이블 엑세스하는 것이 아니라 일정량씩 나누어 처리하는 것
+ 플랜상 NESTED LOOPS 가 연달아 발생됨 (나눠서 처리)
nlj_batching 힌트 사용 , 반대는 no_nlj_batching 또는 nlj_prefetch 힌트 사용
후행(inner)쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지않으고 디스크 I/O 발생시 데이터 정렬 순서가 달라질 수 있다
* I/O 를 순차적으로 처리하지 않기 때문에 , Index 를 이용한 정렬은 항상 보장 받을 수 없다

* 정렬을 보장하기 위해서는 ORDER BY 절을 기술
테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 데이터 정렬 순서가 항상 일정하다.
튜닝시 주의사항
1. NLJ_BATCH 를 모니터링 하려 TRACE 를 수행하거나 , XPLAN 으로 모니터링 하기 위해 GATHER_STATISTICS 힌트를 수행하면 NLJ_BATCH 기능이 사용되지 않는다는 점
2. v$sesstat , v$session_event 뷰로 모니터링
  * V$SESSTAT 의 Batched I O (bound) vector count 지표 가 증가
  
  * 배치 I/O 가 일어날 경우 , db file parallel read 대기 이벤트가 발생하므로 V$SESSION_EVENT 를 모니터링 함

NL조인 배치 방식 플랜

SELECT *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c2 = a.c2;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  NESTED LOOPS                         |       |
|   2 |   NESTED LOOPS                        |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2    |
-------------------------------------------------------

NO_NL조인 방식로 변경 (NL PREFETCH 방식으로 )

- NO_NLJ_BATCHING 힌트로 PREFETCH NL 조인을 유도해보면 t2 테이블이 TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 액세스되는 것을 확인

SELECT /*+ NO_NLJ_BATCHING(B) */
       *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c2 = a.c2;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED  | T2    |
|   2 |   NESTED LOOPS                        |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |
-------------------------------------------------------

NL조인 PREFETCH

DB CAFE

목차

1 NL조인 Prefetch 방식[편집]

1.1 테이블 Prefetch[편집]

1.1.1 NLJ Prefetch 가 나타나는 경우[편집]

1.1.2 NLJ Prefetch 효과[편집]

1.2 NL조인 배치 I/O[편집]