🖥️ 들어가며
📌 Prefetch, Batch I/O: 모두 Disk I/O 횟수를 감소시키기 위해 사용합니다.
다만 Prefetch는 미세한 Block Read량이 증가하게 되고, Batch I/O는 Disk I/O 발생 시 정렬 순서가 상이해집니다.
✏️ 1. Nested Loop 조인
- 기본 매커니즘
- for문(Java의 중첩 루프문)과 동일한 원리입니다.
- 반복문의 외부에 있는 테이블 = 선행 테이블 = 외부 테이블 = Outer Table
- 반복문의 내부에 있는 테이블 = 후행 테이블 = 내부 테이블 = Inner Table
- 선행 테이블 조건에 만족하는 첫번째 행 추출 → 후행 테이블 읽으면서 조인 → 선행 테이블 조건을 만족하는 모든 행의 수만큼 반복수행
=> 선행 테이블 조건에 만족하는 행의 수가 많으면 그만큼 후행 테이블 조인작업을 반복수행하게 됩니다.
- 특징
- Look up 테이블의 join 컬럼에 index가 있는 것이 유리합니다.
(전체 테이블 스캔을 피할 수 있어 → 빠른 데이터 검색 가능, 조인 성능 향상시킬 수 있음) - R.A 위주의 조인 방식이므로 → 대량 데이터 조인 시 대체로 비효율적입니다.
(각 외부 테이블 행마다 내부 테이블 반복적으로 액세스 → 대용량 처리에 적합하지 않을 수 있음) - 조인을 한 레코드씩 순차적으로 진행합니다. (한 번에 한 레코드씩 처리 → 전체 데이터셋 메모리에 로드 X, 작업 수행 O)
→ 대용량 집합이라도 극적인 응답속도 낼 수 있고, 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정됩니다.
=> 선행 테이블은 처리 범위가 좁은 것의 조건으로 선택하는 것이 유리합니다. - 다른 조인 방식보다 인덱스 구성 전략이 매우 중요합니다.
- 소량의 데이터 처리하거나 부분범위 처리 가능, 성공하면 바로 사용자에게 결과를 보여주므로 OLTP 환경에 적합합니다.
- 조인 조건이 등가 조건이 아닌 경우에도 사용할 수 있습니다.
- Look up 테이블의 join 컬럼에 index가 있는 것이 유리합니다.
✏️ 2. Nested Loop 조인 - Prefetch
- Disk I/O 부하 감소를 위한 기능으로 Disk I/O가 필요할 때 곧 읽게 될 블록을 미리 버퍼 캐시에 적재하는 메커니즘입니다.
- Prefetch 종류: 1) Index Prefetch 2) Table Prefetch
- Index Prefetch: 주로 인덱스 스캔 시 사용, Index Full Scan에서 가장 효과적
- Table Prefetch: 인덱스를 통해 테이블 레코드에 접근할 때 사용, R.A 성능 향상을 위함, Index C.F가 나쁠 때 효과적
(Index C.F가 나쁜 경우 = 인덱스 순서대로 데이터를 읽을 때 테이블의 여러 블록을 건너뛰며 읽어야 하는 상황을 의미함)
- db file parallel read 대기 이벤트가 관측됩니다. (single block i/o는 db file sequential read 대기 이벤트)
- single block i/o: index를 통해서 읽을 때 → db file sequential read 대기 이벤트
- multi block i/o: index ffs, full table scan 시 → db file scrattered read 대기 이벤트
- prefetch 또는 batch i/o → db file parallel read 대기 이벤트
👉🏻 Index Prefetch
- Prefetch 미작동 시
- single block i/o 요청 수행 (db file sequential read 대기 이벤트)
- Node Read: 1, 2, 5
- Prefetch 작동 시
- 한 번의 I/O Call로 여러 single block i/o를 동시에 수행합니다. (db file parallel read 대기 이벤트)
- Node Read: 1, 2, 3, 5-10
3번 블록을 같이 읽어오기 때문에, 익스텐트가 달라도 9번 블록을 같이 읽을 수 있습니다. - 다만, 불필요한 Branch Block Read로 Prefetch 미작동 대비 Logical Read가 미세하게 더 많이 발생합니다.
(하지만 Disk I/O는 줄어듦)
👉🏻 Table Prefetch
- Table Prefetch = 테이블 Lookup Prefetch = 데이터 블록 Prefetch
- Random Access 성능 향상을 위해
- Buffer Pinning: R.A에 의한 논리적 블록 요청 횟수가 감소시킵니다.
- Table Prefetch: 디스크 I/O 대기 횟수가 감소시킵니다.
- Prefetch를 적용한 쿼리문
SELECT *
FROM emp e, dept d
WHERE d.loc = 'CHICAGO'
AND e.deptno = d.deptno;
-- 전통적인 NL 조인 시 실행계획
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT'
INDEX (RANGE SCAN) OF 'SCOTT.IX_DEPT_LOC'
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP'
INDEX (RANGE SCAN) OF 'SCOTT.IX_EMP_01'
-- Prefetch 작동 시 실행 계획
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP'
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT'
INDEX (RANGE SCAN) OF 'SCOTT.IX_DEPT_LOC'
INDEX (RANGE SCAN) OF 'SCOTT.IX_EMP_01'
- ⭐⭐⭐ Prefetch 결론
- Disk I/O 부하를 감소시키기 위한 기능입니다.
- 한 번의 Call에 여러 Single Block I/O를 동시에 수행합니다.
- (캐싱해두었다면) 미리 적재하고 사용되었다면 효율적이지만, 그렇지 않을 경우 비효율적입니다.
- CKPT 프로세스가 Prefetch된 블록 사용여부를 감시합니다.
- Multi Block I/O: 하나의 익스텐트 내의 인접한 블록을 동시에 읽는 것
Prefetch: 서로 다른 익스텐트에 위치한 블록을 배치 방식으로 미리 적재하는 것
=> 읽어야 하는 블록들이 서로 다른 디스크에 존재할 경우, 성능 향상은 더 증가합니다.
✏️ 3. Nested Loop 조인 - Batch I/O
- Batch I/O를 적용한 쿼리문
- 가장 바깥쪽 NESTED LOOPS: 최종 결과집합을 생성하기 위한 조인
- 내부 NEXTED LOOPS: 인덱스와 테이블을 조인하는 단계
=> NL 조인이 2회 이루어짐
SELECT *
FROM emp e, dept d
WHERE d.loc = 'CHICAGO'
AND e.deptno = d.deptno;
-- 전통적인 NL 조인 시 실행계획
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT'
INDEX (RANGE SCAN) OF 'SCOTT.IX_DEPT_LOC'
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP'
INDEX (RANGE SCAN) OF 'SCOTT.IX_EMP_01'
-- Prefetch 작동 시 실행 계획
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP'
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT'
INDEX (RANGE SCAN) OF 'SCOTT.IX_DEPT_LOC'
INDEX (RANGE SCAN) OF 'SCOTT.IX_EMP_01'
-- Batch I/O 작동 시 실행 계획
NESTED LOOPS -- 결과집합과 조인
NESTED LOOPS -- 인덱스와 조인
TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'SCOTT.DEPT'
INDEX (RANGE SCAN) OF 'SCOTT.IX_DEPT_LOC'
INDEX (RANGE SCAN) OF 'SCOTT.IX_EMP_01'
TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP'
- Batch I/O 수행방식
- 선행 테이블의 인덱스를 먼저 읽습니다.
- 인덱스에서 얻은 ROWID를 이용해 테이블에 랜덤 액세스합니다.
- 이때 두 가지 상황이 발생할 수 있습니다.
- Buffer Cache에서 데이터를 찾은 경우: 즉시 조인 수행
- Disk I/O가 필요한 경우
1) 해당 ROWID를 임시 저장합니다.
2) 일정량의 ROWID가 쌓일 때까지 이 과정을 반복합니다.
3) 충분한 양의 ROWID가 모이면, 한 번에 Disk I/O를 수행해 여러 블록을 읽어옵니다.
4) 읽어온 데이터로 조인을 수행합니다.
- Hint
| 구분 | 사용 O | 사용 X |
| Prefetch | nlj_prefetch | no_nlj_prefetch |
| Batch I/O | nlj_batching | no_nlj_batching |
- 조인과 무관한 Batch Access
- batch_table_access_by_rowid (대상테이블): 특정 테이블에 대해 Batch I/O 방식의 테이블 접근을 활성화합니다. 인덱스를 통한 테이블 접근 시 여러 ROWID를 모아 한 번에 처리합니다.
- no_batch_table_access_by_rowid (대상테이블): 부분범위 처리*가 필요한 경우 이 힌트를 사용해야 합니다. (Batch I/O 비활성화 필요)
-- 부분범위 처리가 필요한 경우, Batch I/O를 사용해선 안 됩니다!!!
-- 인덱스: C1 + C3(sort 연산 대체 가능) + C2
SELECT
FROM (SELECT
FROM
WHERE C1 = 'A'
AND C2 >= 'B'
ORDER BY C3
)
WHERE ROWNUM <= 30;
- Batch I/O 결과집합 정렬순서
- 기존과 동일한 경우
: 선행 테이블의 데이터를 100% Buffer Cache에서 찾는 경우 - 상이한 경우
: 한 블록이라도 Disk I/O를 하는 경우
- 기존과 동일한 경우
- Batch I/O 결론
| 구분 | Prefetch | Batch I/O |
| 장점 | Disk I/O 횟수 감소 가능성 높음 | |
| 특이사항 | 미세한 Block Read량 증가 | 선행 Table의 R.A에서 Disk I/O 발생 시 정렬 순서가 상이해짐 |
📒 정리하면
- Nested Loop 조인: 선행 테이블의 각 행에 대해 후행 테이블을 반복 접근하는 방식입니다. 소량 데이터 처리나 OLTP 환경에 적합, 인덱스 구성이 중요합니다.
- Prefetch, Batch I/O: 모두 Disk I/O 횟수 감소 가능성이 높습니다.
- Prefetch의 장단점: 곧 읽게 될 블록을 미리 버퍼 캐시에 적재하여 Disk I/O 부하를 감소시키는 기능입니다. 다만, 미세한 Block Read량이 증가합니다.
- Batch I/O의 장단점: 여러 ROWID를 모아 한 번에 Disk I/O를 수행하여 성능을 향상시킵니다. 다만, 이때 정렬 순서가 상이해집니다. (정렬 순서가 기존과 동일하려면? 선행 테이블의 데이터를 전부 Buffer Cache에서 찾아야 함)
반응형
