조인 튜닝 - NL 조인
1. NL 조인의 기본 메커니즘
NL조인은 Nested Loop 방식을 사용한다. 중첩 루프문으로 수행 된다는 뜻이다.
NL 조인은 중첩 루프문과 같은 수행 구조를 사용한다.
일반적으로 NL 조인은 Outer와 Inner 양쪽 테이블 모두의 인덱스를 사용한다. Outer쪽 테이블 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다. Table Full Scan 하더라도 한 번에 그치기 때문이다. 반면 Inner쪽 테이블은 인덱스를 사용해야 한다.
- 사원 X1 인덱스에서 입사일자 >= '19960101'인 첫번째 레코드를 찾는다.
- 인덱스에서 읽은 ROWID로 사원 테이블 레코드를 찾아간다.
- 사원 테이블에서 읽은 사원번호 0006으로 고객 X1 인덱스를 탐색한다.
- 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아간다.
소트 머지 조인과 해시 조인도 각각 Sort Area와 Hash area에 가공해 둔 데이터를 이용한다는 점만 다를 뿐, 기본적인 조인 프로세싱은 다르지 않다.
2. NL 조인 실행계획 제어
아래는 NL 조인 실행계획이다. 위쪽 사원 테이블 기준으로 아래쪽 고객 테이블과 NL 조인 한다.
NL 조인을 제어할 때는 use_nl 힌트를 사용한다.
힌트를 사용할 때는
/*+ ordered use_nl(B) use_nl(c) use_hash(d) */A->B->C 로 NL 조인하고, D는 해쉬 조인하라는 뜻이다.
3. NL 조인 수행 과정 분석
select /*+ ordered use_nl(c) index(e) index(c) */
e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.고객사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'z123'
and c.최종주문금액 >= 20000
두 테이블에 index를 명시했는데, 어떤 인덱스를 사용할지 지정하지 않았다. (옵티마이저가 선택)
- 입사일자를 만족하는 레코드를 찾기 위해 사원_X1인덱스를 Range Scan한다 (실행계획3)
- X1인덱스를 통해 테이블에 엑세스해서 부서코드가 Z123 필터조건 확인한다 (실행계획2)
- 사원 테이블에서 읽은 사원번호 값으로 조인 조건 만족하는 고객 레코드를 위해 고객_X1인덱스를 Range Scan한다(실행계획 5)
- 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 엑세스 해서 최종주문금액 >=20000을 필터링한다.
각 단계를 모두 완료하고 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다!
4. NL 조인 튜닝 포인트
1. 선행 테이블의 인덱스
사원_X1인덱스를 읽고 사원 테이블을 엑세스하는 부분에서 비효율이 많다면, 인덱스에 부서코드를 추가한다.
2. 선행 테이블의 필터링 결과 건수
후행 테이블 인덱스를 탐색하는 과정 즉, 조인 엑세스 횟수가 많을수록 성능이 느려진다.
조인 엑세스 횟수는 Outer 테이블 필터링한 결과 건수에 의해 결정된다.
3. 후행 테이블의 테이블 엑세스
후행 테이블의 조건이 필터링 되는 부분에서 테이블 엑세스가 많다면, 인덱스 추가를 고려하자
4. 선행 테이블의 크기가 전체 일량을 좌우한다.
선행 테이블 혹은 선행 테이블의 첫 인덱스 스캔 결과가 전체 일량을 결정한다.
5. NL 조인 특징 요약
- 랜덤 엑세스 위주의 조인 방식이다.
- 한 레코드씩 순차적으로 진행한다. (부분범위 처리가 가능한 상황에서 좋은 특징임)
- 인덱스 구성 전략이 매우 중요하다
NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 OLTP 환경에 적합한 조인 방식이다.
6. NL조인 튜닝 실습
select /*+ ordered use_nl(c) index(e) index(c) */
e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.고객사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'z123'
and c.최종주문금액 >= 20000
실행계획이 다음과 같다면 어떻게 해야할까? 잘 살펴보면 사원번호로 스캔한 결과가 2780인데, 테이블에서 필터링한 결과는 3이다. 이는 불필요한 테이블 엑세스 과정이 많았음을 의미한다.
-> 테이블 엑세스 후 필터링 비율이 높다면, 인덱스에 필터 조건 컬럼을 추가할 고려할 필요가 있다.
Rows 열에 표시된 숫자를 보면 비효율적인 엑세스는 거의 사라졌지만, 테이블을 엑세스하기 전 인덱스 스캔 단계에서의 일량은 확인하지 않았으므로 튜닝이 끝났다고 볼 수 없다.
실제 스캔량을 한번 출력해보자
각 처리 단계별 논리적인 블록 요청 횟수(cr)과 디스크에서 읽은 블록 수(pr) 디스크에 쓴 블록 수 (pw) 등을 표시한다.
사원_X1 인덱스로부터 읽은 블록이 102개이고, 한 블록에 평균 500개 레코드가 저장되어 있으면, 인덱스에서 세 건을 얻기 위해 50,000여개 레코드를 읽은셈이다.
기존 인덱스는 [입사일자 + 부서코드] 순인데, 입사일자가 BETWEEN이기 때문에 이와 같은 결과가 발생한 것이다.
이를 해결하기 위해 [부서코드+입사일자] 순으로 인덱스를 변경하면, 두 조건 다 인덱스 엑세스 조건이 된다.
사원 테이블을 읽는 비효율은 사라졌지만, 사원 테이블을 읽고나서 고객과 조인하는 곳에서 비효율이 발생했다.
조인은 2780번 시도하지만, 최종 결과집합은 5건 뿐이다.
이럴 때는 조인 순서 변경을 고려할 필요가 있다.
(만약 최종주문금액 조건절에 부합하는 레코드가 별로 없다면 튜닝에 성공할 가능성이 있다)
조인 순서를 바꿔도 별 소득이 없다면, 소트 머지 조인과 해시 조인을 검토해보자
7. NL조인 확장 메커니즘
NL조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O기능을 도입했다.
테이블 Prefetch는 인덱스를 이용해 테이블을 엑세스하다가 디스크 I/O가 필요해지면, 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다.
배치I/O는 디스크I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다.
- 전통적인 NL 실행계획
- 테이블 Prefetch 실행계획
- 배치 I/O 실행계획
이는 Inner쪽 테이블에 대한 디스크I/O 과정에서 배치 I/O 기능이 작동할 수 있음을 표시하기 위함이다.
참고자료
https://www.yes24.com/Product/Goods/61254539
친절한 SQL 튜닝 - 예스24
책 제목은 필자가 애청하는 라디오 프로그램 ‘손에 잡히는 경제’ 중 ‘친절한 경제’라는 코너에서 착안했다. 어려운 경제 이슈를 일반인 눈높이에 맞게 풀어서 설명해 주는 진행자를 보면서
www.yes24.com