성능고도화 2-1. Nested Loops 조인

힌트를 이용해 NL 조인을 제어하는 방법 

 - use_nl() 

 - 10g 부터 leading 힌트에 2개 이상 테이블을 기술할 수 있도록 기능이 개선 

NL 조인 수행 과정 분석 

 - NL 조인은 각 단계를 완료하고 나서 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 

   진행한다는 사실이다. 

   ( 단, order by는 전체 집합을 대상으로 정렬해야 하므로 작업을 모두 완료한 후에 

     다음 오퍼레이션을 진행한다. )

 - NL 조인은 outer 테이블과 inner 테이블과 인덱스 사용으로 인한 random access가 

   1차 적이 부하 지점이 된다. 

 - OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서다. 

NL 조인의 특징 

 - Random 엑세스 위주의 조인 

 - 조인을 한 레코드씩 순차적으로 진행 

 - 인덱스 구성 전략이 특히 중요하다. 

 - 소량의 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 환경에 적합한 

   조인 방식 

테이블 Prefetch 

 - 인덱스 rowid에 의한 inner 테이블 엑세스가 Nested loops 위쪽에 표시 되는 것은 

   테이블 엑세스 단계에서 Prefetch 기능이 적용되었음을 표현하기 위함 

 - Prefetch 기능이 실제로 작동할 때면 db file sequential read 대기 이벤트 대신 

   db file parallel reads 대기 이벤트가 나타난다. 

 - 새 포켓의 실행계획이 나타날 수 있는 경우 

     ● Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 항상 나타남 

     ● Inner 쪽 Unique 인덱스를 Non-Unique 조건으로

         (모든 인덱스 구성컬럼이 '=' 조건이 아닐때) Range Scan 할 때 항상 나타남 

     ● Inner 쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 때도 테이블 prefetch 실행계획이 

         나타날 수 있다. 이때 인덱스는 Range Scan으로 엑세스 한다. 

         테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 엑세스한다. 

 - 테이블 Prefetch는 디스크 I/O에 의한 대기 횟수를 감소시킨다. 

 - 이 기능은 인덱스 클러스터링 팩터가 나쁠 때 특히 효과를 발휘한다. 

   ( 클러스터링 팩터가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문 )

 * 이 부분은 나의 생각 

   - 결국 테이블 Prefetch인덱스를 통한 중간 결과 값을 만들고 rowid 순으로 정렬하여서 

     필요한 block들을 여러 개 빠르게 읽어 들이는 것인가? 

배치 I/O 

 - Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리하는 

   매커니즘인 것으로 추정된다. 

 - 인덱스와의 조인을 모두 완료하고 나서 테이블 엑세스하는 것이 아니라 일정량씩 나누어 

   처리하는 것 

 - 이 메커니즘이 작동하도록 유도하려면 nlj_batching 힌트를 사용하면 된다. 

   이 방식을 원치 않을 때는 no_nlj_batching 또는 nlj_prefetch 힌트를 사용하면된다. 

    (그럼 Prefetch 방식으로 전환된다.) 

 - 이 방식을 사용할 때 inner쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지않으면 

   데이터 정렬 순서가 달라질 수 있다는 사실이다. 

 - 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 

   데이터 정렬 순서가 항상 일정하다. 

  * 내 생각 

    - 결국 테이블 Prefetch 나 배치 I/O 둘 중 하나만 사용해야 하는 것인가? 

버퍼 Pinning 효과 

 - 8i에서 나타난 버퍼 Pinning 효과 

    ● 단, 하나의 버퍼 블록만 Pinning 그리고 하나의 Fetch Call을 완료하는 순간 Pin을 해제 

     (따라서, Inner 쪽 인덱스를 통해 엑세스 되는 테이블 블록이 계속 같은 블록을 가리키면 

      논리 I/O가 추가로 발생하지 않는다.  )

     ● 중요한 사실은 inner 쪽과의 조인을 마치고 다른 레코드를 읽기 위해 

         outer 쪽으로 돌아오는 순간 Pin을 해제 

         (그래서 클러스터링 팩터가 좋아야 그 효과가 확실) 

     ● NL 조인에서도 하나의 Fetch Call을 완료하면 Pin을 해체 

 

 - 9i에서 버퍼 Pinning 효과 

     ● inner 쪽 인덱스 루트 블록에 대한 버퍼 Pinning 효과가나타나기 시작 

         (단, 두번째 엑세스 되는 순간 Pinning) 

     ● 9i 부터 Inner 쪽이 Non-Unique 인덱스일 때는 테이블 엑세스가 항상 NL 조인 위쪽으로 

         올라가므로 이때는 항상 버퍼 Pinning 효과가 나타나는 셈 

         ( 테이블 엑세스가 NL조인 위쪽으로 올라가지 않을 때는 

           버퍼 Pinning 효과가 나타날 수 없음 )

 - 10g에서 버퍼 Pinning 효과 

     ● Inner 쪽 테이블을 Index Range Scan을 거쳐 NL조인 위쪽에서 엑세스 할 때는, 

         하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 Outer 쪽으로 돌아오더라도 

         테이블 블록에 대한 Pinning 상태를 유지한다. 

     ● 버퍼 Pinning 효과는 하나의 데이터베이스 Call 내에세만 유료하다. 

         (여러번의 Fetch Call이 발생 시 Pin을 해제 했다가 다시 Pin을 걸어야 하기 때문에 

          약간의 블록 I/O가 추가로 발생한다.

     ● Non-Unique 인덱스로 조인한다면, 테이블 엑세스가 NL 위쪽에 있던 아래쪽에 있든 

         8i 이후 모든 버전에서 모두 버퍼 Pinning 효과가 나타난다. 

         (물론 10에서 I/O가 좀 더 줄 가능성이 있다. )

 - 11g에서 나타난 버퍼 Pinning 효과 

     ● User Rowid로 테이블 엑세스 할 때( 일반적인 filter로 )도 버퍼 Pinning 효과

     ● NL조인에서 Inner 쪽 루트 아래 인덱스 블록도 Pinning 

     ● 인덱스 블록 버퍼 Pinning 효과는 배치 I/O 실행계획과 상관없이 나타난다.  

     ● Use_nl_with_index() 힌트는 nl 조인시 인덱스와 조인하라는 힌트 

     ● 집계 함수를 쓰지 않고 결과집합을 출력하는 쿼리라면, 드라이빙 집합을 

          Inner쪽 인덱스 컬럼 순으로 정렬하고 나서 NL 조인함으로써 블록 I/O를 줄임