[친절한 SQL 튜닝] 4. 조인 튜닝

 

NL 조인

NL 조인은 '인덱스를 이용한 조인 방식'이라고 할 수 있다.

 

소트 머지 조인과 해시 조인도 각각 Sort Area와 Hash Area에 가공해 둔 데이터를 이용한다는 점만 다를 뿐, 기본적인 조인 프로세싱은 다르지 않다.

 

NL 조인 수행 과정 분석

각 단계를 모두 완료하고 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다.

 

 

• 사원_X1 인덱스를 읽고 나서 테이블을 액세스하는 부분
• 고객_X1 인덱스를 탐색하는 부분
• 고객_X1 인덱스를 읽고 나서 테이블을 액세스하는 부분
• 사원_X1 인덱스를 스캔하면서 추출한 레코드가 많으면 사원 테이블로 랜덤 액세스하는 횟수, 고객_X1 인덱스를 탐색하는 횟수, 고객 테이블로 랜덤 액세스하는 횟수가 전반적으로 많아진다.

올바른 조인 메소드 선택

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

 

NL 조인 특징 요약

• 랜덤 액세스 위주의 조인 방식이다.
  • 레코드 하나를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다.
  • 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유다.
• 조인을 한 레코드씩 순차적으로 진행한다.
  • 부분범위 처리가 가능한 상황에서, 아무리 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있다.
  • 순차적으로 진행하므로 먼저 액세스되는 테이블 처리 범위에 의해 전체 일량이 결정된다.
• 다른 조인 방식과 비교할 때 인덱스 구성 전략이 특히 중요하다.
  • 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.

이런 여러가지 특징을 종합할 때, NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 OLTP 시스템에 적합한 조인 방식이라고 할 수 있다.

 

 NL 조인 확장 메커니즘

테이블 Prefetch

• 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능
• 랜덤 액세스 성능을 향상시킬 때 주로 사용
• 디스크 I/O에 의한 대기 횟수를 감소시킨다.
• 인덱스 CF가 나쁠 때 특히 효과적으로 사용(CF가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문)

 

소트 머지 조인

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

 

SGA VS PGA

• SGA
  • 여러 프로세스가 공유할 수 있지만, 동시에 액세스할 수 없다.
  • 동시에 액세스하려는 프로세스 간 액세스를 직렬화하기 위한 Lock 메커니즘으로서 래치가 존재한다.
  • 데이터 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 가장 핵심적인 구성요소이다.
• PGA
  • 각 오라클 서버 프로세스에 할당된 메모리 영역
  • 다른 프로세스와 공유하지 않는 독립적인 메모리 공간
  • 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용
  • 래치 메커니즘이 불필요해서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다
  • 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블스페이스를 이용한다.

기본 메커니즘

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

소트 머지 조인의 용도

• 조인 조건식이 등치조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인

소트 머지 조인 특징 요약

• 소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.
• PGA 영역에 저장한 데이터를 이용하기 때문에 빠르다.
• 조인 컬럼에 인덱스가 없는 상황에서도 유리하다.

 

해시 조인

기본 메커니즘

1. Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블(해시 맵)을 생성한다.
2. Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인한다.

해시 조인이 빠른 이유

Hash Area에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 조인 프로세싱 자체는 NL 조인과 같다.

해시 테이블을 PGA 영역에 할당하기 때문이다.

 

대용량 Build Input 처리

두 테이블 모두 대용량 테이블이어서 인메모리 해시 조인이 불가능한 상황일 때 DBMS는 어떤 방식으로 해시 조인을 처리할까?

 

• 분할&정복 방식으로 진행
  1. 파티션 단계
     • 조인하는 양쪽 집합(조인 이외 조건절을 만족하는 레코드)의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다.
     • 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성하는 단계
  2. 조인 단계
     • 파티션 단계를 완료하면 각 파티션 짝에 대해 하나씩 조인을 수행한다.
     • 각 파티션 짝별로 작은 쪽을 Build Input으로 선택하고 해시 테이블을 생성한다.
     • 해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다. 모든 파티션 짝에 대한 처리를 마칠 때까지 이 과정을 반복한다.

 

해시 조인 실행계획 제어

조인 대상 테이블이 두 개라면 leading이나 ordered 힌트를 사용하여 Build Input을 직접 선택할 수 있다.

 

세 개 이상 테이블 해시 조인

• 조인하는 테이블이 몇 개든, 조인 연결고리를 따라 순방향 또는 역방향으로 leading 힌트에 기술한 후, Build Input으로 선택하고 싶은 테이블을 swap_join_inputs 힌트에 지정해 주면 된다.

 

조인 메소드 선택 기준

 

• 일반적인 조인 메소드 선택 기준
  • 소량 데이터 조인할 때 : NL 조인
  • 대량 데이터 조인할 때 : 해시 조인
  • 대량 데이터 조인이지만 해시 조인으로 처리할 수 없을 때(조인 조건식이 등치 조건이 아닐 때) : 소트 머지 조인
• 수행빈도가 매우 높은 쿼리에 대한 기준
  • (최적화된) NL 조인과 해시 조인 성능이 같으면 : NL 조인
  • 해시 조인이 약간 더 빨라도 : NL 조인
  • NL 조인보다 해시 조인이 매우 빠른 경우 : 해시 조인
• 왜 NL 조인을 선택해야 할까?
  • NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조다.
  • 반면, 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다.
  • 따라서 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리를 해시 조인으로 처리하면 CPU와 메모리 사용률이 크게 증가한다. 해시 맵을 만드는 과정에 여러 가지 래치 경합도 존재한다.
• 결론적으로 해시 조인은 아래 세 가지 조건을 만족하는 SQL문에 주로 사용한다.
  • 수행 빈도가 낮고
  • 쿼리 수행시간이 오래 걸리는
  • 대량 데이터 조인할 때

 

서브쿼리 조인

서브쿼리 변환이 필요한 이유

• 최근 옵티마이저는 비용을 평가하고 실행계획을 생성하기에 앞서 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업, 즉 쿼리 변환부터 진행한다.

* 쿼리 변환 : 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것

 

오라클에서 분류하는 서브쿼리

 

• 인라인 뷰
  • FROM 절에 사용한 서브쿼리
• 중첩된 서브쿼리
  • 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리
  • 서브쿼리가 메인쿼리 컬럼을 참조한 형태를 '상관관계있는 서브쿼리'라고 부름
• 스칼라 서브쿼리
  • 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리
  • 주로 SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에 사용할 수 있음

 

서브쿼리와 조인

서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없다. 메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행해야 한다.

 

• 필터 오퍼레이션
  • no_unnest 힌트 사용
  • 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속처리한다.
  • 캐싱기능을 갖는다. 그러므로 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다.
  • NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.
• 서브쿼리 Unnesting
  • unnest 힌트 사용
  • 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어 준다는 의미이다.
  • 서브쿼리를 그대로 두면 필터 방식을 사용할 수밖에 없지만, Unnesting을 하고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.
• NL 세미 조인
  • unnest 힌트와 함께 nl_sj 힌트 사용
  • 기본적으로 NL 조인과 같은 프로세스다.
  • 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다.
• 서브쿼리 Pushing
  • 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능

뷰와 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.

merge 힌트를 이용해 뷰를 메인 쿼리와 머징하면 옵티마이저가 전체적으로 변환된 쿼리를 변환해 준다.

 

조인 조건 Pushdown

 

• 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어넣는 기능이다.
• 이 방식을 사용하면 건건이 데이터만 읽어서 조인하고 Group By를 수행할 수 있다.

 

스칼라 서브쿼리 조인

• Outer 조인문처럼 NL 조인 방식으로 실행된다.
• 처리과정에서 캐싱 작용이 일어난다.

스칼라 서브쿼리 캐싱 효과

• 필터 서브쿼리 캐싱과 같은 기능이다.
• SELECT-LIST에 사용한 함수를 스칼라 서브쿼리를 덧씌워서 호출 횟수를 최소화할 수 있다.

스칼라 서브쿼리 캐싱 부작용

• 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 효과가 있다.
• 반대의 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만들며 메모리도 더 사용한다.

스칼라 서브쿼리 Unnesting

• 스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다.
• 그래서 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환하고 싶을 때가 있다.
• 특히, 병렬 쿼리에선 될 수 있으면 서브쿼리를 사용하지 않아야 한다.
  • 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이기 때문이다.