NL조인 PREFETCH
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 NL조인 Prefetch 방식[편집]
1.1 테이블 Prefetch[편집]
- 인덱스 rowid에 의한 inner 테이블 엑세스가 Nested loops 위쪽에 표시 되는 것
- 테이블 엑세스 단계에서 Prefetch 기능이 적용되었음을 플랜에 표시
- Prefetch 기능이 실제로 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 대기 이벤트가 나타남
1.1.1 NLJ Prefetch 가 나타나는 경우[편집]
- Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 항상 나타남
- Inner 쪽 Unique 인덱스를 Non-Unique 조건으로 (모든 인덱스 구성컬럼이 '=' 조건이 아닐때) Range Scan 할 때 항상 나타남
- Inner 쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 때도 테이블 prefetch 실행계획이 나타날 수 있다.
- 이때 인덱스는 Range Scan으로 엑세스 한다.
- 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 엑세스
1.1.2 NLJ Prefetch 효과[편집]
- 디스크 I/O에 의한 대기 횟수 감소
- 인덱스 클러스터링 팩터가 나쁠 때 특히 효과가 있음
- CF가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문
1.2 NL조인 배치 I/O[편집]
- 선행 테이블 에 DISK I/O 가 발생하게 될 시 바로 DISK I/O 를 수행하지 않고 , 일정량의 I/O 작업이 모이면 , 한번의 I/O CALL 로 여러 개의 블록을 읽어 들이 는 기능
- Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리
- 인덱스와의 조인을 모두 완료하고 나서 테이블 엑세스하는 것이 아니라 일정량씩 나누어 처리하는 것
- + 플랜상 NESTED LOOPS 가 연달아 발생됨 (나눠서 처리)
| 1 | NESTED LOOPS| 2 | NESTED LOOPS
- nlj_batching 힌트 사용 , 반대는 no_nlj_batching 또는 nlj_prefetch 힌트 사용
- inner쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지않으고 디스크 I/O 발생시 데이터 정렬 순서가 달라질 수 있다
- * I/O 를 순차적으로 처리하지 않기 때문에 , Index 를 이용한 정렬은 항상 보장 받을 수 없다
- * 정렬을 보장하기 위해서는 ORDER BY 절을 기술
- 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 데이터 정렬 순서가 항상 일정하다.
- 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조인 방식로 변경
- 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 |
-------------------------------------------------------