조인 튜닝(NL, 소트머지, 해시조인)

1. NL조인

• 조인의 기본은 NL
• NL은 인덱스를 이용한 조인
• 각조인은 프로세싱과정은 비슷 , 어떤 자료구조를 사용하느냐의 차이

1.1 기본메커니즘

• NL의 기본 구조는 중첩 루프문 구조
• NL조인은 일반적으로 OUTER(DRIVING)와 INNER 양쪽 테이블 모두 인덱스를 이용한다.
• OUTER쪽 데이터가 적으면 인덱스를 이용하지 않을 수 있다. > TABLE FULL SCAN하더라도 한번에 그치기때문
• INNER쪽 테이블은 인덱스를 사용해야한다 (OUTER추출 데이터 수만큼 INNER에 액세스해야하기때문)

1.2 NL조인 실행계획 제어

• NL조인은 실행계획에서 NESTED LOOPS로 나타난다.
• USE_NL 힌트를 사용
• ORDERED는 FROM절에 기술된 순서대로 조인하는 힌트
• 여러개조인할때는

  SELECT /*+ ORDERED USE_NL(B) USE_NL(C) USE_NL(D) */
  *
  FROM A, B, C, D
  WHERE ....

1.3 NL조인 수행 과정 분석

• 한레코드씩 순차수행.
• 항상 마지막에 한레코드 더확인하는데, 스캔할 데이터가 더있는지 확인.

  SELECT /+ ORDRED USE_NL(C) INDEX(E) INDEX(C) /
  e.사원번호, e.사원명, e.입사일자
  ,c.고객번호 , c.고객명, c.전화번호, c.최종주문금액
  from 사원 e, 고객 c
  where c.관리사원번호 = e.사원번호 --1
  and e.입사일자 >= '19960101' -- 2
  and e.부서코드 = 'Z123' -- 3
  and c.최종주문금액 >= 20000 -- 4
  사원pk : 사원번호
  사원_x1 : 입사일자
  고객_pk : 고객번호
  고객_x1 : 관리사원번호
  *고객_x2 : 최종주문금액

• 위쿼리에서 조건절 비교순서는 2 - 3 - 1 - 4

1.4 NL조인 튜닝 포인트

• 단일컬럼 인덱스를 >= 조건으로 스캔했으므로 비효율없음.
• 첫째, 만약 사원테이블로 아주많은 랜덤액세스가 발생했고, 테이블에서 부서코드 = 'Z123' 조건에 의해 필터링되는 비율이 높다면, 사원_X1 인덱스에 부서코드 컬럼을 추가하는 방안
• 둘째, 고객X1 인덱스를 탐색하는 부분, 고객_X1인덱스 탐색횟수, 즉 조인액세스 횟수가많을수록 성능이 느림
  조인 액세스 횟수는 OUTER테이블인 사원을 읽고 필터링한 결과건수에 의해 결정
  ? 고객X1인덱스 탐색이라고했는데 결론은 사원테이블 결과건수를 줄이는이야기
• 세번째, 고객_X1인덱스읽고 고객 테이블액세스 부분, 최종주문금액 >= 20000조건에 필터링되는 비율이 높다면 고객_X1인덱스에 최종주문금액을 추가하는 방안 고려
• 사원_X1에서 얻은결과건수에따라 일량 좌우, 사원테이블 랜덤액세스횟수, 고객_X1인덱스 탐색횟수, 고객테이블로 랜덤액세스하는 횟수를 좌우

올바른 조인 메소드 선택

• OLTP시스템에서 튜닝시 일차적으로 NL조인부터 고려
• 성능이 느리면 위의 NL조인 튜닝포인트에 따라 각 단계의 수행일량을 분석해서 과도한 랜덤액세스가 발생하는 지점 우선 파악, 조인순서를 변경해서 랜덤액세스를 줄일 수 있는지, 다른 효과적 인덱스있는지 검토
• 필요시 인덱스 추가 또는 구성변경
• 여러 방안 검토시 NL조인이 좋은성능을 낼수없다고 판단될때 소트머지나 해시 조인을 검토.

1.5 NL조인 특징 요약

• 첫째, 랜덤 액세스 위주 조인 방식, 랜덤액세스는 설령 메모리퍼버에서 빠르게 읽어도 비효율이 존재,
  대량 데이터 조인시 NL이 불리한 이유
• 둘째, 한레코드씩 순차진행, 아무리 큰 테이블 조인이라도 부분범위 처리와함께 엄청난 속도향상을 낼수 있다. 부붐범위처리시 거의 조회하자마자 결과가 바로나올수도있다.
• 셋째, 다른조인방식과 비교해 인덱스 구성 전략이 특히 중요, 조인컬럼 인덱스 유무, 컬럼구성에 따라 효율이 크게 달라짐
• OLTP시스템에 적합한 조인방식

1.6 조인 튜닝 실습

• 266P 및 SQLP문제집 참고
• 읽은 ROW수와 FILTER후의 ROW수를 체크하여 인덱스추가
• CR(논리적 블록요청횟수), PR(디스크에서 읽은 블록수)를 참고하여 읽은 블록대비 사용하는 레코드수를 보고 인덱스 순서 변경
• 일반적으로 한블록당 500개레코드라 가정 : 실제로는 테이블 컬럼수/사이즈 파라미터에따라 레코드수가 달라짐

1.7 NL조인 확장 메커니즘

• 오라클은 NL조인 성능향상을 위해 테이블 Prefecth, 배치I/O기능을 도입
• 테이블 Prefetch : 인덱스이용 테이블액세스하다가 디스크 I/O가 필요해지면 이어서 곧 읽게될 블록까지 미리 읽어서 버퍼캐시 적재하는 기능
• 배치 I/O : 디스크 I/O CALL을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능
• 두기능모드 읽는 블록마다 건건이 I/O CALL을 발생시키는 비효율을 줄이기 위해고안
  
  
• 테이블 PREFETCH 실행계획, Inner쪽 테이블에 대한 디스크 I/O과정에 테이블 Prefetch기능이 작동할수있음을 표시, nlj_prefetch, no_nlj_prefetch힌트로 제어가능

  TABLE ACCESS BY INDEX ROWID OF 고객
     NESTED LOOPS
         TABLE ACCESS BY INDEX ROWID OF 사원
            INDEX RANGE SCAN OF 사원_X1
         INDEX RANGE SCAN OF 고객_X1
  
  

• 테이블 I/O 실행계획은 BATCHED가붙을수 있다. 확인필요.
• nlj_batching, no_nlj_batching힌트 사용
• 배치I/O실행계획나타날땐 결과집합 정렬순서가 다를수있음, 11g부터 NL조인 INNER쪽테이블에 작동하는 배치I/O기능도 결과집합 순서 보장안함.
• 정렬이 필요할때는 항시! ORDER BY를 명시하거나 배치I/O기능을 작동하지못하도록한다.
• 11g에서 바깥쪽 메인쿼리에 order by추가했어도 안쪽 order by를 함부로 제거해선안된다, 이는 TOP N쿼리구현하기위한것.

2. 소트 머지 조인

• 조인컬럼에 인덱스가 없을때
• 대량데이터 조인이어서 인덱스가 효과적이지 않을때
• 옵티마이저는 NL조신 대인 소트머지조인이나 해시조인을 선택

2.1 SGA vs PGA

• SGA(공유 메모리 영역)에 캐시된 데이터틑 여러 프로세스가 공유가능, BUT 동시 액세스는 불가
• 동시 액세스하려는 프로세스간 액세스 직렬화를 위한 Lock매커니즘으로 래치(Latch)가 존재
• 데이터 블록와 인덱스 블록을 캐싱하는 DB버퍼캐시는 SGA의 가장핵심 구성요소, 여기서 블록읽으러면 버퍼 LOCK도 얻어야 한다.
  
  
• 오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서 동시에 자신만의 고유메모리 영역을 갖는다(PGA)
• 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용
• 할당받은 PGA공간이 작아 데이터 저장이 어려우면 TEMP테이블스페이스를 이용

2.2 기본 메커니즘

• 소트머지조인의 두단계
  • 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
  • 머지 단계 : 정렬한 양쪽 집합을 서로 머지(MERGE)한다
• 1.3의 쿼리를 소트머지로 수행하면
  • 사원테이블을 사원번호순으로 정렬하여 PGA영역에 할당된 Sort Area에 저장
    (정렬한 결과값이 PGA에 담을수 없을정도로 크면, TEMP 테이블스페이스에 저장)
  • 고객데이터를 읽어 조인컬럼인 관리사원번호 순으로 정렬, 마찬가지고 PGA Sort Area저장(부족시 temp에저장)
  • PGA(또는 TEMP테이블스페이스)에 저장한 사원데이터를 스캔하면서 PGA에 저장한 고객 데이터와 조인
    (NL의 중첩루프와 같다)
• 소트머지에서는 데이터를 다찾아놓고 조인하기때문에 조인실패 레코드를 만나는순간 바로 멈출수있다.
• Sort Area에 저장된 데이터 자체가 인덱스 역할
• 조인컬럼에 인덱스가 있어도 NL조인은 대량조인에 불리하므로 소트머지조인을 사용할 수 있다.

2.3 소트머지조인이 빠른 이유

• NL조인은 대량데이터 조인시 매우 느림 > 해시조인과 소트머지조인이 개발된 이유
• 소크머지조인은 SORT AREA에 미리 정렬해둔 자료구조를 이용한다는점빼곤 프로세싱은 NL과 같다
• 대용량 NL조인이 느린 이유
  • NL조인은 '인덱스를 이용한 조인방식'
  • 조인과정에서 액세스하는 모든 블록을 랜덤 액세스 방식으로 '건건이' DB버퍼캐시를 경유해서 읽는다
  • 인덱스든 테이블이든 모든 읽는 블록에 래치 획득 및 캐시버퍼 체인 스캔과정을 거친다.
  • 버퍼캐시에 찾지못한 블록은 '건건이' 디스크에서 읽는다.
  • 인덱스 이용하기때문에 인덱스 손익 분기점 한계를 그대로 드러낸다.
• 대용량 소트머지 조인이 빠른 이유
  • 양쪽테이블로부터 조인대상 집합을 '일괄적으로'읽어 PGA(또는 TEMP TABLESPACE)에 저장 후 조인
  • PGA는 프로세스만을 위한 독립 메모리 공간이라 데이터 읽을때 래치 획득과정이 없다.
• 소트머지조인도 양쪽테이블로부터 조인대상 집합을 읽을때는 DB버퍼캐시 경유, 이때 인덱스를 이용하기도함
  이과정에서 생기는 버퍼캐시탐색 비용과 랜덤액세스 부하는 소트머지조인도 마찬가지

2.4 소트머지 조인의 주용도

• 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인(해시조인은 조인조건식이 등치(=)조건이어야만함)
• 조인 조건식이 아예 없는 조인(Cross join, 카테시안 곱)

2.5 소트머지조인 제어하기

• use_merge힌트를 이용하여 제어

2.6 소트머지조인 특징 요약

• 소트머지조인은 조인을 위해 실시간으로 인덱스 생성하는것과 같음
• 양쪽집합 정렬한다음에는 NL조인과 같은방식으로 진행하지만, PGA영역에 저장한 데이터를 이용하기때문에 빠름
• 소트부하만 감수한다면 건건이 버퍼캐시경유하는 NL조인보다 빠르다.
• 소트머지조인은 인덱스에 영향을 받지 않는다.
• 조인컬럼에 인덱스가 없는상황에서 두 테이블을 각각 읽어 조인대상 집합을 줄일 수 있을때 아주 유리

3. 해시 조인

• NL조인단점
  • 인덱스 이용한 방식으로 인덱스 구성에 따른 성능차이가 심함.
  • 인덱스를 잘 구성해도 랜덤I/O때문에 대량 데이터 처리에 불리
• 소트머지와 해시조인은 조인과정에 인덱스를 이용하지 않아 대량 데이터 조인시 NL조인보다 훨씬빠르고 일정한 성능 유지.
• 소트머지는 항상 양쪽 테이블 정렬부담이있는데 해시는 그런부담이없음.

3.1 기본 메커니즘

• Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵)을 생성한다.
• Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.
  
  
• use_hash힌트로 유도
  

  SELECT /+ ORDRED use_hash(C)/
  e.사원번호, e.사원명, e.입사일자
  ,c.고객번호 , c.고객명, c.전화번호, c.최종주문금액
  from 사원 e, 고객 c
  where c.관리사원번호 = e.사원번호
  and e.입사일자 >= '19960101'
  and e.부서코드 = 'Z123'
  and c.최종주문금액 >= 20000

• Build 단계 : 아래조건에 해당하는 사원 데이터를 읽어 헤시 테이블을 생성
  • 이때 조인 컬럼인 사원번호를 해시 테이블 키 값으로 사용
  • 사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인에 데이터를 연결.
  • 해시 테이블은 PGA영역에 할당된 HASH AREA에 저장, 해시테이블이 너무커 PGA저장못하면 TEMP 테이블스페이스에 저장
• Probe 단계 : 해시 함수가 반환한 값에 해당하는 해시 체인 스캔
  • Probe단계는 NL조인과 다르지않음.
  • Probe 테이블 scan하면서 해시맵에서 조인데이터 검색

3.2 해시 조인이 빠른 이유

• Hsh Area에 생성한 해시 테이블(=해시 맵)을 이용하는점만 다르고 프로세싱자체는 NL조인과 같다
• 해시 테이블을 PGA영역에 할당하기때문에 NL보다빠르다.
• NL조인은 Outer테이블 레코드마다 Inner테이블 레코드를 읽기위해 래치 획득 및 캐시버퍼 체인스캔 과정을 반복하지만, 해시조인은 래치획득과정없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.
• 해시조인도 Build Input과 Probe Input테이블을 읽을때는 DB버퍼캐시를 경유하며 인덱스를 이용하기도함
  이과정에서 생기는 퍼버캐시탐색비용과 랜덤엑세스는 해시조인도 발생

해시테이블에 담기는 정보

• 해시테이블은 랜덤엑세스의 단점때문에 탄생
• 조인 키값 뿐 아니라 SQL에 사용한 모든 컬럼을 저장한다(최초 액세스 이후 랜덤엑세스없음)
  
  

정리

• 소트머지조인에서 사전준비작업은 양쪽 집합을 모두 정렬해서 PGA에 담는다, PGA는 그리 큰 공간이아니므로 두집합 중 어느 하나가 중대형 이상이면 TEMP테이블 스페이스(디스크)쓰는 작업을 수반
• 해시 조인에서 사전 준비작업은 양쪽 집합 중 어느 '한쪽'을 읽어 해시맵을 만드는 작업
  둘중 작은 집합을 해시맵 Build Input으로 선택하므로 두집합 모두 Hash Area에 담을수 없을 정도로 큰 경우가 아니면 디스크 쓰기 작업은 일어나지 않는다.
• 해시조인은 랜덤액세스 부하가 없고 정렬연산 부하도 없다
• 해시테이블 생성비용이들지만 둘중 작은집합을 Build Input선택하므로 대개 부담이크지않다.
• 설령 Temp 테이블 스페이스를 쓰게되더라도 대량 데이터 조인에는 일반적으로 해시조인이 가장빠르다.

3.3 대용량 Build Input 처리

• 두테이블 모두 대용량이어서 인메모리 해시 조인이 불가능한 상황
• 이럴경우 두 단계로 나눠서 진행, 분할/정복 방식(파티션에 대한 이해필요함)

1. 파티션 단계

• 조인하는 양쪽 집합의 조인컬럼에 해시 함수를 적용하고 반환된 해시 값에 따라 동적으로 파티셔닝
• 처리할 수 있는 여러 개의 작은 서브집합으로 분할함으로써 파티션 짝을 생성하는 단계
• 양쪽 집합을 읽어 디스크 Temp공간에 저장(T1, T2)해야 하므로 인메모리 해시조인보다 성능이 많이 떨어진다

2. 조인 단계

• 각 파티션 짝에 대해 하나씩 조인을 수행, 각각파티션마다 Build Input과 Probe Input은 독립적으로 결정
• 파티션전 테이블 사이즈상관없이 파티션짝별로 작은쪽을 Build Input으로 선택하고 해시테이블 생성
• 해시테이블 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으며 해시테이블을 탐색
• 모든 파티션 짝에대한 처리마칠때까지 반복

3.4 해시 조인 실행계획 제어

• 실행계획 위쪽(HASH JOIN 바로 아래) 데이터(Build Input)으로 해시테이블을 생성한후
• 아래쪽 테이블(Probe Input)에서 읽은 조인 키값으로 해시 테이블을 탐색하면서 조인한다고 해석
• use_hash힌트만 사용하면 Build Input은 옵티마이저가 선택, 일반적으로 카디널리티(조건절결과에대한)가 작은 테이블을 선택
• swap_join_inputs힌트로 Build Input을 명시적으로 선택할 수 도 있다.

세 개 이상 테이블 해시 조인

• 테이블 수와 상관없이 조인 연결고리를 따라 leading힌트에 기술한 후,
• Build Input으로 선택하고 싶은 테이블을 swap_join_inputs힌트에 지정
• Build Input으로 선택하고 싶은 테이블이 조인된 결과 집합이어 swap_join_inputs힌트로 지정하기어렵다면 no_swap_join_inputs힌트로 반대쪽 Probe Input을 선택해주면된다

3.5 조인 메소드 선택 기준

• 일반적인 조인 메소드 선택 기준

  • 소량 데이터 조인 : NL조인
  • 대량 데이터 조인 : 해시 조인
  • 대량 데이터 조인 & 해시조인 처리불가(조건식이 등치조건 아닐때, 카테시안 곱일때) : 소트 머지 조인

• 소량과 대량의 기준 : 단순데이터량이 아님, NL조인 기준으로 최적화했는데도 랜덤액세스가 많아 만족할만한 성능을 낼수 없다면 대량 데이터 조인에 해당

• 수행 빈도가 매우 높은 쿼리에 대한 기준

  • (최적화된) NL 조인과 해시 조인 성능이 같으면, NL조인
  • 해시 조인이 약간 더 빨라도 NL조인
  • NL조인보다 해시 조인이 매우 빠른 경우, 해시 조인(대부분 대량 데이터 조인)

  조인메소드 선택시 NL조인을 가장 먼저 고려해야 하는이유

  (인덱스 설계부담에도 불구하고, 해시 조인이 더빠른데 불구하고 NL부터 고려해야하는 이유)

• NL조인에 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조

• 해시테이블은 하나의 쿼리를 위해 생성하고 조인이 끝나면 바로 소멸하는 자료구조

• 같은 쿼리를 100개 프로세스가 동시수행하면 해시테이블도 100개가 만들어짐.

• 수행시간이 짧으면서 빈도가 매우높은 쿼리(OLPT성쿼리의 특징)를 해시조인으로 처리하면 CPU와 메모리사용량이 크게 증가

• 해시맵 만드는 과정에 여러 래치경합도 발생

• 해시조인적용대상 SQL (DW, OLAP성 쿼리 특징)

  • 수행 빈도가 낮고
  • 쿼리 수행 시간이 오래 걸리는
  • 대량 데이터 조인할 때