01. Nested Loops 조인

정의

중첩 루프문(Nested Loop:이중 For문)의 수행 구조

사용1

select /*+ ordered use_nl(t2) */ *
from t1, t2
where t1.c1 = t2.c1

ordered 힌트 -> from절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용
use_nl  힌트 -> NL 방식으로 조인하라고 지시할 때 사용

사용2

select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *
from A, B, C, D
where ~

A -> B -> C -> D 순으로 조인하되, B와 조인할 때 그리고 이어서 C와 조인할때는 NL 방식으로 조인하고,
D와 조인할 때는 해시 방식으로 조인하라는 뜻

사용3

select /*+ leading(C, A, D, B) use_nl(A) user_nl(d) user_hash(B) */ *
from A, B, C, D
where ~

from절에 기술된 순서와 상관없이, leading절의 인자 순서대로 조인 진행

사용4

select /*+ use_nl(A, B, C, D) */ *
from  A, B, C, D
where ~

ordered나 leading 힌트를 기술하지 않았으므로 
4개 테이블을 NL 방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡기는 것

조인별 선후 테이블 명명

           "NL 조인"         "Sort Merge 조인"    "Hash 조인"
선행테이블:  Outer(Driving)   Outer(First)         Build Input
후행테이블:  Inner(Driven)    Inner(Second)        Probe Input

NL 조인의 특징

1. Random 액세스 위주의 조인 방식
인덱스 구성이 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적임

2. 조인을 한 레코드씩 순차적으로 진행함
Random 액세스 위주의 조인으로 대용량 데이터 처리 시, 매우 치명적인 한계가 있지만
조인을 한 레코드씩 순차적으로 진행함으로, 아무리 대용량 집합이더라도 부분범위처리가 가능한 상황에서 효과적인 응답 속도를 낼 수 있음

* 인덱스를 스캔하는 양에 따라 전체 일량이 좌우됨

* 조건에 의해 필터링되는 비율이 높다면 인덱스에 컬럼을 추가하는 방안을 고려해야함

* OLTP 시스템에서 조인을 튜닝할때는 일반적으로 NL 조인부터 고려하는 것이 올바른 순서임
  우선, NL 조인 메커니즘을 따라 각 단계의 수행 일량을 분석해 과도한 Random 액세스가 발생하는 지점을 파악함
  조인 순서를 변경해 Random 액세스 발생량을 줄일 수 있는 경우가 있고,
  그렇지 못할 때는 인덱스 컬럼 구성을 변경하거나 다른 인덱스의 사용을 고려해야함

* 순차적으로 진행하는 특징 때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정됨

* 다른 조인 방식과 비교했을 때 인덱스 구성 전략이 특히 중요함

* 조인 컬럼에 대한 인덱스가 있느나 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라짐

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

* 테이블을 액세스한 후에 필터링되는 비율이 높다면, 인덱스에 테이블 필터 조건 컬럼을 추가하는 것을 고려해 볼 필요가 있음

테이블 Prefetch

/*+ nlj_prefetch */

* Prefetch 기능이 실제 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 이벤트가 나타남

* Prefetch는 디스크 I/O와 관련이 있음. 디스크 I/O를 수행하려면 비용이 많이 들기 때문에 한번 I/O Call이 필요한 시점에,
  곧이어 읽을 가능성이 큰 블록들을 캐시에 미리 적재해 두는 기능. 한번의 I/O Call로써 여려 Single Block I/O를 동시에 수행함
* NL 조인에서 항상 새 포멧의 실행계획이 나타나는 것을 아님. 기본적으로 Outer 쪽 인덱스를 Unique Scan 할 때는 작동하지 않음
  이 경우를 제외하면 언제든 새 포맷의 실해계획이 나타날 수 있음

- Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 실행계획이 항상 나타남
- Inner 쪽 Unique 인덱스를 Non-Unique 조건(모든 인덱스 구성컬럼이 '=' 조건이 아닐 때)으로 
  Range Scan할 때도 테이블 Prefetch 실행계획이 항상 나타남
- Inner 쪽 Unique 인덱스를     Unique 조건(모든 인덱스 구성컬럼이 '=' 조건         )으로 
  액세스할 때도 테이블 Prefetch 실행계획이 나타날 수 있음
  이때 인덱스는 Range Scan으로 액세스 함. 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 액세스 함

배치 I/O

/*+ nlj_batching */
/*+ no_nlj_batching */

* 오라클 11g에서 시작된 배치 I/O 메커니즘임

* Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄(batch) 처리하는 메커니즘인 것으로 추정

* 테이블 액세스를 나중에 하지만 부분범위처리는 정상적으로 작동함 
  따라서 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩(Fetch Call 단위) 나누어 처리함
 


1. 드라이빙 테이블에서 일정량을 레코드를 읽어 Inner 족 인덱스와 조인하면서 중간 결과집합(sub-resultset)을 만듬

2. 중간 결과집합이 일정량 쌓이면 Inner 쪽 테이블 레코드를 액세스 함. 이때 테이블 블록을 버퍼 캐시에서 찾으면 바로 최종 결과집합에 담고,
   못 찾으면 중간 집합에 남겨 둠

3. 2번 과정에서 남겨진 중간 집합에 대한 Inner 쪽 테이블 블록을 디스크로부터 읽음. 이때 Multiple Single Block I/O 방식을 사용함

4. 버퍼 캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음


* Outer 테이블로부터 액세스되는 Inner 쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해, 
  테이블 Prefetch에 이어 추가로 도입된 메커니즘

* Inner 쪽 테이블 블록이 모두 버퍼 캐시에서 찾아가지 않으면(버퍼 캐시 히트율 < 100%), 
  실제 배치 I/O가 작동한다면 데이터 정렬 순서가 달라질 수 있음
  모두 버퍼 캐시에서 찾을 때는(버퍼 캐시 히트율 = 100%) 이전 메커니즘과 똑같은 정렬 순서를 보임

* 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 
  디스크 I/O가 발생하든 안 하든 데이터 정렬 순서가 항상 일정함

버퍼 Pinning 효과

테이블 Prefetch와 배치 I/O 기능이 도입된 시점과 맞물려 버퍼 Pinning 기능에 변화가 생겨
9i와 11g에서 나타난 NL 조인 실행계획 변화를 버퍼 Pinning 효과로 설명도 하지만 이 둘간에 직접적인 연관성은 없음

-8i에서 버퍼 Pinning 효과
테이블 블록에 대한 버퍼 Pinning 기능이 작동하기 시작
단, 하나의 버퍼 블록만 Pinning 함. 그리고 하나의 Fetch Call을 완료하는 순간 Pin을 해제함
이 기능은 NL 조인에서 Non-Uique 조건으로 Inner 쪽 테이블을 액세스할 때도 똑같이 작용함
따라서 Inner 쪽 인덱스를 통해 액세스되는 테이블 블록이 계속 같은블록을 가리키면 논리 I/O(=CR Gets)가 추라로 발생하지 않음
하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 "다른 레코드를 읽기 위해 Outer 쪽으로 돌아오는 순가 Pin을 해제"한다는 점
Inner 쪽을 한 번 액세스할 때마다 여러 개의 테이블 레코드를 읽도록 데이터를 구성해야 함. 클러스터링 팩터가 좋아야 그 효과도 확실함(9i~같음)
NL 조인에서도 하나의 Fetch Call을 완료하면 Pin을 해제함

-9i에서 버퍼 Pinning 효과
9i부터 Inner 쪽 인덱스 "루트 블록에 대한 버퍼 Pinning" 효과가 나타나기 시작함
단, 두 번째 액세스되는 순간 Pinning 함
테이블 블록 버퍼에 대한 Pinning도 8i와 똑같이 작동함
Inner 쪽을 한 번 액세스할 때마다 Non-Unique 인덱스로 여러 개 테이블 레코드를 읽을 때라야 이 기능이 효과를 발휘함
9i부터 Inner 쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL 조인 위쪽에 올라가므로(->테이블 Prefetch 포멧) 이때는 항상 버퍼 Pinning 효과가 나타나는 셈임
Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때는 Inner 쪽에서 한 건만 읽고 바로 Outer 테이블 쪽으로 돌아감
따라서 버퍼 Pinning 효과가 나타날 수 없음
그러다 보니 테이블 액세스가 NL 조인 위쪽으로 올라가는 9i에서의 실행계획 변화를 버퍼 Pinning 효과와 연관시켜 해석하는 오류를 범하기 쉬움
Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때도 테이블 액세스가 NL 조인 위쪽으로 올라가는 경우가 있음

-10g에서 버퍼 Pinning 효과
Inner 쪽 인덱스 루트 블록과 테이블 블록을 Pinning하는 기능이 여전히 작동하면서 새로운 기능이 추가됨
Inner 쪽 테이블을 Index Range Scan을 거쳐 NL 조인 위쪽에서 액세스할 때는, 
하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 Outer 쪽으로 돌아오더라도 "테이블 블록에 대한 Pinning 상태를 유지"함
만약 Inner 쪽 테이블이 한 블록뿐일 때 10g에서의 새로운 버퍼 Pinning 기능이 작동한다면 NL 조인이 진행되는 논리적인 블록 I/O는 단 1회만 발생할 것
버퍼 Pinning 효과는 하나의 데이터베이스 Call 내에서만 유효함

-11g에서 버퍼 Pinning 효과
11g에서는 User Rowid로 테이블 액세스할 때도 버퍼 Pinning 효과가 나타남
NL 조인에서 Inner 쪽 루트 아래 인덱스 블록들도 Pinning 하기 시작함
배치 I/O 기능이 나타남과 동시에 이 기능이 추가되다 보니 11g에서의 NL 조인 실행계획 변화가 인덱스 블록 버퍼 Pinning과 관련 있다고 오해하기 쉬움
익덱스 블록 버퍼 Pinning 효과는 배치 I/O 실행계획과 상관없이 나타남
테이블 Prefetch 방식으로 액세스하더라도 인덱스 블록에 대한 버퍼 Pinning 효과는 똑같음
11g에서 실행계획 변화(->배치 I/O 포맷)가 인덱스 블록에 대한 버퍼 Pinning과 관련이 없음
테이블과 마찬가지로 인덱스 블록도 버퍼 Pinnging 효과가 나타나려면 같은 값으로 반복 액세스해야함

Prefetch vs Batch I/O

SQL_ID  adtptt05p8ahs, child number 0
-------------------------------------
select /*+ gather_plan_statistics ordered use_nl_with_index(d) 
nlj_prefetch(d) */ count(e.ename), count(d.dname) from t_emp e, t_dept 
d where d.no = e.no and d.deptno = e.deptno
 
Plan hash value: 1305463140
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      1 |00:00:00.02 |   14167 |
|   1 |  SORT AGGREGATE              |           |      1 |      1 |      1 |00:00:00.02 |   14167 |
|   2 |>>>TABLE ACCESS BY INDEX ROWID| T_DEPT    |      1 |      1 |  14000 |00:00:00.02 |   14167 |-> Prefetch
|   3 |    NESTED LOOPS              |           |      1 |  15329 |  28001 |00:00:00.01 |     167 |
|   4 |     TABLE ACCESS FULL        | T_EMP     |      1 |  15329 |  14000 |00:00:00.01 |      95 |
|*  5 |     INDEX UNIQUE SCAN        | T_DEPT_PK |  14000 |      1 |  14000 |00:00:00.01 |      72 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   5 - access("D"."NO"="E"."NO" AND "D"."DEPTNO"="E"."DEPTNO")
 

 
SQL_ID  2ku0935hr7p7c, child number 0
-------------------------------------
select /*+ gather_plan_statistics ordered use_nl_with_index(d) 
nlj_batching(d) */ count(e.ename), count(d.dname) from t_emp e, t_dept 
d where d.no = e.no and d.deptno = e.deptno
 
Plan hash value: 2968996940
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |      1 |        |      1 |00:00:00.02 |   14167 |
|   1 |  SORT AGGREGATE               |           |      1 |      1 |      1 |00:00:00.02 |   14167 |
|   2 |   NESTED LOOPS                |           |      1 |  15329 |  14000 |00:00:00.07 |   14167 |
|   3 |    NESTED LOOPS               |           |      1 |  15329 |  14000 |00:00:00.01 |     167 |
|   4 |     TABLE ACCESS FULL         | T_EMP     |      1 |  15329 |  14000 |00:00:00.01 |      95 |
|*  5 |     INDEX UNIQUE SCAN         | T_DEPT_PK |  14000 |      1 |  14000 |00:00:00.01 |      72 |
|   6 | >>>TABLE ACCESS BY INDEX ROWID| T_DEPT    |  14000 |      1 |  14000 |00:00:00.01 |   14000 |-> Batch I/O
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   5 - access("D"."NO"="E"."NO" AND "D"."DEPTNO"="E"."DEPTNO")