조인 튜닝 (1)

NL 조인

• 조인의 기본은 NL 조인이다.
• NL은 Nested Loops 조인의 약어다. 즉 중첩 루프문과 같은 수행 구조를 사용한다.
• 일반적으로 NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 사용한다.
• Outer 쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다. Table Full Scan 하더라도 그것은 한 번에 그치기 때문이다.
• Inner 쪽 테이블은 인덱스를 사용해야 한다.
• Inner 루프에서 외래키로 데이터를 검색할 때 인덱스를 이용하지 않으면, Outer 루프에서 읽은 건수만큼 Table Full Scan을 반복하기 때문이다.
• NL 조인은 '인덱스를 이용한 조인 방식'이라고 할 수 있다.

튜닝 포인트

• Outer Table 인덱스를 읽고 나서 Outer Table을 액세스하는 부분이다.
  • Outer Table에서 랜덤 액세스가 너무 많이 발생하면 결합 인덱스를 고려해야 한다.
• Inner Table 인덱스를 탐색하는 부분이다.
  • 인덱스를 탐색하는 횟수, 즉 조인 액세스 횟수가 많을수록 성능이 느려진다.
  • 조인 액세스 횟수는 Outer 테이블을 읽고 필터링한 결과 건수에 의해 결정된다.
• Inner Table 인덱스를 읽고 나서 고객 테이블을 액세스하는 부분이다.
  • 마찬가지로 결합 인덱스를 고려해야 한다.
• 맨 처음 액세스하는 Outer Table 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다는 점도 기억하다.

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

성능이 느리다면, 앞에서 언급한 튜닝 포인트에 따라 각 단계의 수행 일량을 분석해서 과도한 랜덤 액세스가 발생하는 지점을 파악한다.

조인 순서를 변경해서 랜덤 액세스 발생량을 줄일 수 있는지, 더 효과적인 다른 인덱스가 있는지 등을 검토한다.

필요하다면 인덱스 추가 또는 구성 변경도 고려해본다.

 

NL 조인으로 결코 좋은 성능을 내기 어렵다고 판단될 때, 소트 머지조인이나 해시 조인을 검토한다.

특징

• 랜덤 액세스 위주의 조인 방식이다.
• 조인을 한 레코드씩 순차적으로 진행한다.
• 인덱스 구성 전략이 매우 중요하다.
• NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 OLTP 시스템에 적합한 조인 방식이다.

소트 머지 조인

SGA vs PSA

• SGA는 System Global Area의 약어로 공유 메모리 영역이다. SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있다.
• PGA는 Process Global Area라고 부르며, 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.

PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간이므로 래치 메커니즘이 불필요하다.

따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

기본 메커니즘

소트 머지 조인은 이름이 의미하는 것처럼 아래 두 단계로 진행한다.

• 소트 단계: 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
• 머지 단계: 정렬한 양쪽 집합을 서로 머지한다.

Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다.

조인 컬럼에 인덱스가 있어도 NL 조인은 대량 데이터 조인할 때 불리하므로 소트 머지 조인을 사용할 수 있다.

소트 머지 조인이 빠른 이유

• NL 조인의 치명적인 단점은대량 데이터 조인할 때 성능이 매우 느리다는 데 있다. 소트 머지 조인과 해시 조인이 개발된 이유다.
• NL 조인은 단적으로 말해 '인덱스를 이용한 조인 방식'이다. 
• 조인 과정에서 액세스하는 모든 블록을 랜덤 액세스 방식으로 '건건이' DB 버퍼 캐시를 경유해서 읽는다.
• 즉, 인덱스든 테이블이든, 읽는 모든 블랙에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다.
• 반면 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 '일괄적으로' 읽어 PGA에 저장한 후 조인한다.
• PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 소트 머지 조인이 대량 데이터 조인에 유리한 이유다.

소트 머지 조인의 주용도

• 해시 조인의 등장으로 이제 소트 머지 조인의 쓰임새는 예전만 못하다. 
• 대부분 해시 조인이 더 빠르기 때문이다.
• 그래도 아래와 같은 상황에 주로 사용된다.
  • 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
  • 조인 조건식이 아예 없는 조인(카테시안 곱)

특징

• 소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.
• 양쪽 집합을 정렬한 다음에는 NL 조인과 같은 방식으로 진행하지만, PGA 영역에 저장한 데이터를 이용하기 때문에 빠르다.
• 따라서 소트 부하만 감수한다면, 건건이 버퍼캐시를 경유하는 NL 조인보다 빠르다.
• NL 조인은 인덱스 유무에 크게 영향을 받지만, 소트 머지 조인은 영향을 받지 않는다.
• 양쪽 집합을 개별적으로 읽고 나서 조인을 시작한다.
• 스캔 위주의 액세스 방식을 사용한다.