[sql 튜닝 ]chapter 4.1 NL 조인, 4.2 소트 머지 조인

4.1. NL 조인
4.1.1. 기본 메커니즘

4.1. NL 조인

• NL 조인은 인덱스를 활용하는 방식이고 어떤 자료형을 쓰느냐의 차이로 NL조인, 소트머지조인, 해시 조인이 나뉘게 되므로 한번 이해하게 되면 나머지 조인 방식도 이해하기 쉬워진다.

4.1.1. 기본 메커니즘
outer loop, inner loop 에서 모두 인덱스를 활용한다. outer 는 아무리 많아도 table full scan 한번만 하는 효과가 되기 때문에 인덱스를 활용하지 않아도 될 때도 있지만, inner 는 인덱스를 활용해야 한다.

4.1.2.NL 조인 실행 계획 제어

• 힌트 사용
  • ordered use_nl(table)
  • ordered : from 절에 기술한 순서대로 조인할 것.
    • <-> leading(A,B,C,D) : from 절에 기술한 것과 상관없이 지정한 대로 조인 순서를 정함.
  • use_nl : nl 조인 방식을 사용할 것.

4.1.3. NL 조인 수행 과정 분석
4.1.4. NL 조인 튜닝 포인트

1. outer table을 조건에 맞게 인덱스로 테이블 엑세스하는 부분
   1. 입사 일자는 인덱스로 찾아서 들어가고, 부서 조건에 맞는 레코드만 찾는 과정.
2. outer table 과의 조건에 맞는 innter table 의 인덱스를 조회하는 부분 (수직적 탐색)
   1. outer table 을 읽고 필터링 된 결과 건수.
3. inner table 에 맞는 인덱스를 활용해서 테이블 엑세스를 하는 부분.

• 올바른 조인 메소드 선택
  온라인 트랜잭션 시스템에서는 기본적으로 NL 조인 방식을 먼저 고려한다. 성능을 올리기 위해 조인 순서, 인덱스 구성 변경 등을 고려한 후, 성능 개선이 어렵다고 판단할 때 소트 머지, 해시 조인 방식을 고민하게 된다.

4.1.5. NL 조인 특징 요약

• 랜덤 엑세스 방식에 기반한다.
  • 인덱스 구성이 완벽하더라도 최선의 선택이 아닐 수 있는 이유. (대량 데이터 조인의 경우)
  • 메모리 버퍼에서 읽는다고 하더라도 비효율이 존재한다. (실제 블록에 접근하려면 랜덤IO발생)
• 한 레코드씩 읽는다.
  • 대량 데이터 처리 시 한계가 명확.
  • 그러나 부분 범위 처리가 가능할 때는 오히려 속도가 매우 빠르다.
• 다른 조인 방식과 비교했을 때 인덱스의 구성이 특히 중요하다.
• 주로 온라인 트랜잭션 시스템에서 적합한 방식이다.

4.1.6 NL 조인튜닝실습

• 7 버전에서는 각 단계의 처리 건수를 보여주다가 8i 에서는 스캔 후 출력 건수를 보여주는 것으로 바뀜.

• 9iR2 부터 아래와 같이 각 처리 단계 별 논리적인 블록 요청 횟수(cr) 와 디스크에서 읽은 블록 수(pr) 그리고 디스크에 쓴 블록 수(Pw) 등을 표시

• 트레이스 결과가 다음과 같았을 때

  • 조건에 맞는 사원 레코드를 찾기 위해 인덱스를 통해 테이블 엑세스하는 횟수가 매우 많음.
  • 인덱스 칼럼 추가 (부서 코드 )
    

• 

  • 이후에 개선된 트레이스.
  • 이게 끝은 아니고, 테이블 엑세스 전에 인덱스 스캔 단계에서의 전체 일량도 체크해야한다.
  • 

4.1.7 NL 조인확장메커니즘

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

  • Prefetch : 디스크 IO 가 발생해야하면, 다음에 읽을 블록까지 미리 읽는 것.

    • 힌트 : nlj_prefetch, no_nlj_prefetch
    • 

  • 배치 I/O : 디스크 I/O 가 발생해야하면, 여러 요청이 모일 때까지 기다렸다가 모이면 실행하는 것.

    • 배치 I/O 를 실행하면 실행 결과에 순서가 보장되지 않기 때문에 주의해야한다.

  • 
    *NL 조인 자가 진단

4.2. 소트 머지 조인

• 조인 컬럼에 인덱스가 없을 때, 대량 데이터라서 인덱스가 효과가 없을 때, 소트 머지 조인 혹은 해시 조인을 선택하게 된다.
• 소트 머지 조인과 해시 조인을 이해하기 위해서는 PGA, SGA(System Global Area, Shared Global Area) 에 대한 설명이 선행되어야 한다.

4.2.1. SGA VS PGA
SGA, System Global Area에 존재하는 메모리는 프로세스 간 공유가 가능하다. 단, 동시 엑세스는 불가하기 때문에 엑세스를 직렬화하기 위한 Latch 가 존재한다. 특히 DB 버퍼 (데이터 블록, 인덱스 블록 캐시) 에 접근할 때는 버퍼 Lock 도 획득해야한다.
SQL 서버 프로세스는 SGA 에서 공유 메모리를 읽고 쓰면서, 동시에 자신만의 메모리 영역도 가지고 있다.
서버 프로세스가 독립적으로 갖고 있는 메모리영역을 PGA 라고 부른다. (Process/Program/Private Global Area)

서버 프로세스가 사용하는 공간이 부족할 때는 Temp 테이블 스페이스를 활용한다.

• SGA 보다 PGA 의 경우, Latch 메커니즘이 불필요하므로 SGA 에서 버퍼캐시를 읽을 때보다 데이터를 읽는 속도가 매우 빠르다.

4.2.2. 기본 메커니즘
두 단계로 진행한다.

1. 양쪽 집합을 조인 칼럼 기준으로 정렬한다.
2. 정렬된 양쪽 집합을 머지한다.

• 힌트 : use_merge

  select /*+ ordered use_merge(c) */
    e.사원번호， e.사원명， e.입사일자, c.고객변호，c.고객명， c.전화번호， c.최종주문금액
  from 사원 e, 고객 c
  where c.관리사원변호 = e.사원번호
  and e. 입사일자 >= ’19960101 ’
  and e. 부서코드 = 'Z123'
  and c. 최종주문금액 >= 20000

1. 사원 테이블에서 입사일자, 부서코드에 맞는 레코드를 읽어서, 사원 번호 순으로 정렬한다.

   1. 이때 정렬한 데이터는 PGA 영역의 Sort Area 에 저장한다. (데이터가 너무 크면 Temp 에 저장한다. )

2. 고객 테이블에서 최종주문금액 >= 20000 인 레코드에 대해, 관리사원번호를 기준으로 정렬한다.

   1. 정렬한 데이터는 역시 PGA 영역의 Sort Area 에 저장하되, 데이터가 너무 크면 Temp 영역에 저장한다.

3. 1,2 단계에서 정렬된 데이터를 가지고 머지를 진행한다.

   1. NL 조인처럼, 1데이터 레코드 한 행을 가지고 2데이터에서 조건에 맞는 데이터를 찾아서 조인하는 식이다.

• 특징은, 하나의 사원 테이블 레코드에 대해 고객 테이블을 매번 Full Scan 하지 않는다는 점이다.
  • 시작점과 끝점을 쉽게 찾을 수 있다.
• 인덱스를 사용하지 않기 때문에 오히려 인덱스를 사용해서 느릴 수 있는 대량 데이터의 조인의 경우 사용한다.

4.2.3. 소트 머지 조인이 빠른 이유

• 애초에 NL 조인 방식이 대량 데이터의 조인에서 너무 느리기 때문에 개발된 방식임.
• 기본적으로 정렬하는 걸 빼면 NL 조인처럼 outer 데이터당 inner데이터 하나씩 조인하는 건 똑같음. 그럼에도 불구하고 빠른 이유는 랜덤 I/O 의 존재여부에 있음.
• NL 조인은 인덱스를 활용하는 방식임. 따라서 데이터에 접근할 때 DB 버퍼 캐시 혹은 디스크 IO 에 접근하게 되고 모든 과정에서 버퍼 래치 획득, 캐시 버퍼 체인 스캔 과정을 거친다.
• 1,2, 과정에서 머지하기 전에 정렬을 할 때는 DB 버퍼 캐시를 활용한다. 오히려 인덱스를 활용하기도 한다. 그런데 그렇게 정렬해놓고나면, PGA 영역만 읽으면 되기 때문에 초반에 정렬해서 저장해둔 것이 성능상 이점을 갖게 되는 것이다.

-> 정리하자면, 방식의 차이보다도, 미리 정렬해서 저장해두는 '공간'의 위치가 프로세스 독립적이며 랜덤 I/O 방식을 생략할 수 있기 때문에 성능 상의 이점이 생기는 것이다.

4.2.4. 소트 머지 조인의 주용도

• NL 조인의 단점, 대용량 데이터에 대해서 느리다는 점을 극복하기 위해서 사용한다. 단, 해시 조인이 등장한 후로는 해시 조인이 대부분 더 빠르기 때문에 사용되지 않는다. 그럼에도 불구하고 사용되는 시점은 해시 조인이 '=' 등치 조건이 아닐 때 사용할 수 없기 때문에, 대용량 데이터를 다룸 + 등치'=' 조건이 아님 조건에서 사용하게 된다.

4.2.5. 소트 머지 조인 제어하기

• ordered use_merge 힌트 사용
• 
• SORT 를 위해서 테이블에 엑세스할 때는 인덱스를 사용하는 것이 보인다. (table full scan 도 가능. )

4.2.6. 소트 머지 조인 특징 요약

1. 소트에 드는 부하만 감수한다면, PGA 영역에서 데이터 엑세스를 하기 때문에 속도가 빠르다. (소트가 곧 인덱스를 생성하는 과정이나 다름없다. )
2. 각각의 테이블을 각각 읽고 머지를 수행하기 때문에, 필터링 되는 데이터가 많을 수록 이득이다.
3. NL 조인은 인덱스의 영향을 많이 받는 반면, 소트 머지는 그렇지 않다. 하지만 인덱스를 사용하지 않는 것은 아니고 sort 할 떄 사용할 수 있다. 조인 시에는 인덱스를 사용하지 않는다는 것!