[Oracle] NL Join 확장 메커니즘

1. 전통적인 실행계획

 Rows Row Source Operation
 ---- -------------------------------------------------------
    5 NESTED LOOPS
    3   TABLE ACCESS BY INDEX ROWID OF 사원 
    5     INDEX RANGE SCAN OF 사원_X1 
    5   TABLE ACCESS BY INDEX ROWID OF 고객 
    8     INDEX RANGE SCAN OF 고객_X1

 

 

버전이 올라가면서 오라클은 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입한다. 

 

2. Table Prefetch 

인덱스를 이용해 테이블을 엑세스 하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능.

 

이는 Inner쪽 테이블에 대한 디스크 I/O 과정에 테이블 Prefetch 기능이 작동할 수 있음을 표시하기 위함이다. 

nlj_prefetch, no_nlj_prefetch 힌트를 이용해 이 실행계획이 나오게 할 수도 있고, 안 나오게 할 수도 있다. 

 

 Rows Row Source Operation
 ---- -------------------------------------------------------
   5  TABLE ACCESS BY INDEX ROWID OF 고객                        --> prefetch!!
  12    NESTED LOOPS 
   3      TABLE ACCESS BY INDEX ROWID OF 사원
   3        INDEX RANGE SCAN OF 사원_X1 
   8      INDEX RANGE SCAN OF 고객_X1

 

 

3. 배치 I/O 

디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능 

(Prefetch는 미리 가져오고, 배치I/O는 미뤘다 한꺼번에 가져온다.)

 

1. 리프 블록의 rowid로 버퍼 캐시 조회 -> 실패 시 rowid 저장 

2. 실패한 rowid 일정량 저장 시 블록 번호로 정렬하여 Multiblock I/O 수행 

 

기존 single I/O 방식에서는 인덱스 만으로 결과 집합의 정렬이 보장되었으나, Batch I/O 작동시 이러한 방식의 결과 집합을 보장하지 않게 되었다.

 

밑의 실행계획은 Inner쪽 테이블에 대한 디스크 I/O 과정에 배치 I/O 기능이 작동할 수 있음을 표시한다.

nlj_batching, no_nlj_batching 힌트를 이용해 이 실행계획이 나오게 할 수도 있고, 안 나오게 할 수도 있다.

 

 Rows Row Source Operation
 ---- -------------------------------------------------------
    5 NESTED LOOPS
    8   NESTED LOOPS
    3     TABLE ACCESS BY INDEX ROWID OF 사원
    3       INDEX RANGE SCAN OF 사원_X1
    8     INDEX RANGE SCAN OF 고객_X1
    5   TABLE ACCESS BY INDEX ROWID OF 고객                            --> batch I/O!!

 

 

두 기능 모두, 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안 되었다.

 

오라클 11g에서는 위 세가지 실행계획이 모두 나타날 수 있는데, Inner쪽 테이블 블록을 모두 버퍼캐시에서 읽는 다면 어떤 방식으로 수행하든 성능에 차이가 없다. 데이터 출력 순서도 100% 같다. 

 

다만, '일부를 디스크에서 읽게 되면' 성능에 차이가 나타날 수 있고,배치 I/O 실행계획이 나타날 때는  결과집합의 정렬 순서도 다를 수 있어 특별한 주의가 필요 하다.