"NL조인 PREFETCH"의 두 판 사이의 차이
DB CAFE
(→NL조인 배치 I/O) |
(→테이블 Prefetch) |
||
| (같은 사용자의 중간 판 35개는 보이지 않습니다) | |||
| 1번째 줄: | 1번째 줄: | ||
= NL조인 Prefetch 방식 = | = NL조인 Prefetch 방식 = | ||
== 테이블 Prefetch == | == 테이블 Prefetch == | ||
| − | # 인덱스 rowid에 의한 inner 테이블 엑세스가 Nested loops 위쪽에 표시 되는 것 | + | # 인덱스 rowid에 의한 후행(inner) 테이블 엑세스가 Nested loops 위쪽에 표시 되는 것 |
| − | # 테이블 엑세스 단계에서 Prefetch 기능이 적용되었음을 플랜에 표시 | + | #: - 플랜에서 후행(Inner) 테이블의 TABLE ACCESS BY INDEX ROWID 부분이 NESTED LOOPS 위에 나타남 |
| − | # Prefetch 기능이 실제로 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 대기 이벤트가 나타남 | + | #: - 테이블 엑세스 단계에서 Prefetch 기능이 적용되었음을 플랜에 표시 |
| + | #: {{틀:외부이미지|90%|https://misogain.files.wordpress.com/2023/06/smartselect_20230625_155352_samsung-notes.jpg}} | ||
| + | # Prefetch 기능이 실제로 작동할 때면 db file sequential read 대기 이벤트 대신 '''db file parallel reads''' 대기 이벤트가 나타남 | ||
| + | #: -> 병렬로 처리 된다는 것 | ||
| + | # 선행(Outer) 테이블 스캔 결과와 후행(Inner) 인덱스가 NL조인을 하고 병렬로 TABLE ACCESS BY INDEX ROWID 부분을 처리 하는 방식 | ||
=== NLJ Prefetch 가 나타나는 경우 === | === NLJ Prefetch 가 나타나는 경우 === | ||
| − | # Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 항상 나타남 | + | # 후행(Inner) 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 항상 나타남 |
| − | # Inner 쪽 Unique 인덱스를 Non-Unique 조건으로 (모든 인덱스 구성컬럼이 '=' 조건이 아닐때) Range Scan 할 때 항상 나타남 | + | # 후행(Inner) 쪽 테이블이 Unique Scan일 경우 나타나지 않음 |
| − | # Inner 쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 | + | #: ( 후행(Inner) 테이블 조인결과가 1건이여서 병렬로 엑세스 할것이 없기때문) |
| + | # 후행(Inner) 쪽 Unique 인덱스를 Non-Unique 조건으로 (모든 인덱스 구성컬럼이 '=' 조건이 아닐때) Range Scan 할 때 항상 나타남 | ||
| + | # 후행(Inner) 쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 경우 테이블 prefetch 실행계획이 나타날 수 있다. | ||
## 이때 인덱스는 Range Scan으로 엑세스 한다. | ## 이때 인덱스는 Range Scan으로 엑세스 한다. | ||
## 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 엑세스 | ## 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 엑세스 | ||
| 15번째 줄: | 21번째 줄: | ||
# 인덱스 클러스터링 팩터가 나쁠 때 특히 효과가 있음 | # 인덱스 클러스터링 팩터가 나쁠 때 특히 효과가 있음 | ||
## CF가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문 | ## CF가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문 | ||
| + | ---- | ||
== NL조인 배치 I/O == | == NL조인 배치 I/O == | ||
| − | # Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리 | + | # 선행 테이블 에 DISK I/O 가 발생하게 될 시 바로 DISK I/O 를 수행하지 않고 , 일정량의 I/O 작업이 모이면 , 한번의 I/O CALL 로 여러 개의 블록을 읽어 들이 는 기능 |
| + | # 후행(Inner) 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리 | ||
# 인덱스와의 조인을 모두 완료하고 나서 테이블 엑세스하는 것이 아니라 일정량씩 나누어 처리하는 것 | # 인덱스와의 조인을 모두 완료하고 나서 테이블 엑세스하는 것이 아니라 일정량씩 나누어 처리하는 것 | ||
| + | #: + 플랜상 NESTED LOOPS 가 연달아 발생됨 (나눠서 처리) | ||
| + | #: {{틀:외부이미지|90%|https://misogain.files.wordpress.com/2023/06/smartselect_20230625_155419_samsung-notes.jpg}} | ||
# nlj_batching 힌트 사용 , 반대는 no_nlj_batching 또는 nlj_prefetch 힌트 사용 | # nlj_batching 힌트 사용 , 반대는 no_nlj_batching 또는 nlj_prefetch 힌트 사용 | ||
| − | # | + | # 후행(inner)쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지않으고 디스크 I/O 발생시 데이터 정렬 순서가 달라질 수 있다 |
| + | #: * I/O 를 순차적으로 처리하지 않기 때문에 , Index 를 이용한 정렬은 항상 보장 받을 수 없다 | ||
| + | #: * 정렬을 보장하기 위해서는 ORDER BY 절을 기술 | ||
# 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 데이터 정렬 순서가 항상 일정하다. | # 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 데이터 정렬 순서가 항상 일정하다. | ||
| − | + | # 튜닝시 주의사항 | |
| + | ## NLJ_BATCH 를 모니터링 하려 TRACE 를 수행하거나 , XPLAN 으로 모니터링 하기 위해 GATHER_STATISTICS 힌트를 수행하면 NLJ_BATCH 기능이 사용되지 않는다는 점 | ||
| + | ## v$sesstat , v$session_event 뷰로 모니터링 | ||
| + | ##: * V$SESSTAT 의 Batched I O (bound) vector count 지표 가 증가 | ||
| + | ##: * 배치 I/O 가 일어날 경우 , db file parallel read 대기 이벤트가 발생하므로 V$SESSION_EVENT 를 모니터링 함 | ||
* NL조인 배치 방식 플랜 | * NL조인 배치 방식 플랜 | ||
<source lang=sql> | <source lang=sql> | ||
| 43번째 줄: | 59번째 줄: | ||
</source> | </source> | ||
| − | * NO_NL조인 방식로 변경 | + | |
| + | |||
| + | * NO_NL조인 방식로 변경 (NL PREFETCH 방식으로 ) | ||
- NO_NLJ_BATCHING 힌트로 PREFETCH NL 조인을 유도해보면 t2 테이블이 TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 액세스되는 것을 확인 | - NO_NLJ_BATCHING 힌트로 PREFETCH NL 조인을 유도해보면 t2 테이블이 TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 액세스되는 것을 확인 | ||
<source lang=sql> | <source lang=sql> | ||
| 63번째 줄: | 81번째 줄: | ||
------------------------------------------------------- | ------------------------------------------------------- | ||
</source> | </source> | ||
| + | [[category:oracle]] | ||
2023년 7월 11일 (화) 08:27 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
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 실행계획이 나타날 수 있다.
- 이때 인덱스는 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 가 연달아 발생됨 (나눠서 처리)
- nlj_batching 힌트 사용 , 반대는 no_nlj_batching 또는 nlj_prefetch 힌트 사용
- 후행(inner)쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지않으고 디스크 I/O 발생시 데이터 정렬 순서가 달라질 수 있다
- * I/O 를 순차적으로 처리하지 않기 때문에 , Index 를 이용한 정렬은 항상 보장 받을 수 없다
- * 정렬을 보장하기 위해서는 ORDER BY 절을 기술
- 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 데이터 정렬 순서가 항상 일정하다.
- 튜닝시 주의사항
- NLJ_BATCH 를 모니터링 하려 TRACE 를 수행하거나 , XPLAN 으로 모니터링 하기 위해 GATHER_STATISTICS 힌트를 수행하면 NLJ_BATCH 기능이 사용되지 않는다는 점
- 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 |
-------------------------------------------------------