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

NL 조인

기본 메커니즘

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

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

NL 조인 실행계획 제어

ordered 힌트는 FROM 절에 기술한 순서대로 조인하라는 의미다.

leading 힌트는 FROM 절을 바꾸지 않고도 마음껏 순서를 제어할 수 있다.

NL 조인 수행 과정 분석

기억할 것은, 각 단계를 모두 완료하고 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다는 사실이다.

NL 조인 튜닝 포인트

• T1_X1 인덱스를 읽고 나서 테이블을 액세스하는 부분
• T2_X1 인덱스를 탐색하는 부분
• T2_X1 인덱스를 읽고 나서 테이블을 액세스하는 부분

T1_X1 인덱스를 스캔하면서 추출한 레코드가 많으면 T1 테이블로 랜덤 액세스하는 횟수, T2_X1 인덱스를 탐색하는 횟수, T2 테이블로 랜덤 액세스하는 횟수가 전반적으로 많아진다.

올바른 조인 메소드 선택

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

NL 조인 특징 요약

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

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

NL 조인 튜닝 실습

인덱스 컬럼 추가, 인덱스 컬럼 순서 조정, 조인 순서 변경 방법을 사용

조인 순서를 바꿔도 별 소득이 없다면 소트 머지 조인과 해시 조인을 검토해야 한다.

NL 조인 확장 메커니즘

테이블 Prefetch

• 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능
• nlj_prefetch 힌트 사용

배치 I/O

• 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능
• 데이터 정렬 순서를 보장하지 않는다.
• nlj_batching 힌트 사용

내부 원리까지 자세히 알 필요는 없지만, 튜닝하는 과정에 이들 기능을 표현한 실행계획을 자주 보게 되므로 표현 방식은 익혀둘 필요가 있다.

1. 전통적인 실행계획

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

2. 테이블 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

3. 배치 I/O 실행계획

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

• 일부를 디스크에서 읽게 되면 결과집합의 정렬 순서가 다를 수 있다.

소트 머지 조인

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

SGA vs PGA

PGA

• 각 오라클 서버 프로세스에 할당된 메모리 영역
• 다른 프로세스와 공유하지 않는 독립적인 메모리 공간
• 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용
• 래치 메커니즘이 불필요해서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.
• 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블스페이스(디스크)를 이용한다.

기본 메커니즘

소트 머지 조인은 두 단계로 진행한다.

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

use merge 힌트를 사용한다.

정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다.(담을 수 없이 크면 Temp 테이블스페이스에 저장)

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

소트 머지 조인이 빠른 이유

대량 데이터를 조인할 때 NL 조인이 느린 이유

• NL 조인은 조인 과정에서 액세스하는 모든 블록을 랜덤 액세스 방식으로 건건이' DB 버퍼캐시를 경유해서 읽는다.
• 즉, 인덱스든 테이블이든, 읽는 모든 블록에 래치 획득 및 캐시버처 체인 스캔 과정을 거친다.
• 버퍼캐시에서 찾지 못한 블록은 '건건이' 디스크에서 읽어 들인다.

대량 데이터를 조인할 때 소트 머지 조인이 빠른 이유

• 양쪽 테이블로부터 조인 대상 집합(조인 조건 이외 필터 조건을 만족하는 집합)을 '일괄적으로' 읽어 PGA에 저장한 후 조인한다.
• PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 래치 획득 과정이 없다. 그래서 빠르다.

소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시를 경유한다. 이때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 소트 머지 조인도 피할 수 없다.

소트 머지 조인의 주용도

주로 사용되는 상황

• 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인(Cross Join)

소트 머지 조인 제어하기

use_merge 힌트를 사용한다.

소트 머지 조인 특징 요약

소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.

PGA 영역에 저장한 데이터를 이용하기 때문에 빠르다. 따라서, 소트 부하만 감수한다면, 건건이 버퍼캐시를 경유하는 NL 조인보다 빠르다.

조인 컬럼에 인덱스가 없는 상황에서도 아주 유리하다.

스캔 위주의 액세스 방식을 사용한다는 점도 중요한 특징이다. 하지만 양쪽 조인 대상 레코드를 찾을 때는 랜덤 액세스가 일어난다.

해시 조인

소트 머지 조인은 항상 양쪽 테이블을 정렬하는 부담이 있는데, 해시 조인은 그런 부담도 없다.

기본 메커니즘

해시 조인의 두 단계

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

use_hash 힌트를 사용한다.

해시 조인이 빠른 이유

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

NL 조인보다 빠른 이유

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

소트 머지 조인보다 빠른 이유

• 소트 머지 조인에서 두 집합 중 어느 하나가 중대형 이상이면, Temp 테이블 스페이스, 즉 디스크에 쓰는 작업을 반드시 수반한다.
• 해시 조인은 둘 중 작은 집합을 해시 맵 Build Input으로 선택하므로 두 집합 모두 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면 디스크에 쓰는 작업은 전혀 일어나지 않는다.
• 설령 Temp 테이블스페이스를 쓰게 되더라도 대량 데이터를 조회할 때는 일반적으로 해시 조인이 가장 빠르다.

대용량 Buil Input 처리

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

아래 두 단계로 나눠서 진행된다. 분할 정복 방식이다.

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

해시 조인 실행계획 제어

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

swap_join_inputs 힌트로 Build Input을 명시적으로 선택할 수도 있다.

세 개 이상 테이블 해시 조인

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

조인 메소드 선택 기준

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

1. 소량 데이터 조인할 때: NL 조인
2. 대량 데이터 조인할 때: 해시 조인
3. 대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때, 즉 조인 조건식이 등치 조건이 아닐 때: 소트 머지 조인

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

1. (최적화된) NL 조인과 해시 조인 성능이 같으면: NL 조인
2. 해시 조인이 약간 더 빨라도: NL 조인
3. NL 조인보다 해시 조인이 매우 빠른 경우: 해시 조인

왜 NL 조인을 선택해야 할까?

• NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조다.
• 반면, 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다.
• 따라서 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리를 해시 조인으로 처리하면 CPU와 메모리 사용률이 크게 중가한다. 해시 맵을 만드는 과정에 여러 가지 래치 경합도 발생한다.

결론적으로 해시 조인은 아래 세 가지 조건을 만족하는 SQL문에 주로 사용한다.

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

이 세 가지 조건은 배치 프로그램 쿼리의 특징이기도 하다.

OLTP 환경에서도 해시 조인을 쓸 수 있지만, 이 세 가지 기준을 만족하는지 점검해 봐야 한다.

서브쿼리 조인

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

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

쿼리 변환

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

서브쿼리

• 쿼리에 내장된 또 다른 쿼리

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

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

옵티마이저는 쿼리 블록 단위로 최적화를 수행한다.

SQL을 최적화할 때 옵티마이저가 나무가 아닌 숲 전체를 바라보는 관점에서 쿼리를 이해하려면 먼저 서브쿼리를 풀어내야만 한다.

서브쿼리와 조인

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

필터 오퍼레이션

• no_unnest 힌트는 서브쿼리를 풀어내지 말고 그대로 수행하라는 의미다.
• 필터 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같지만 차이점이 있다.
  1. 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다는 점이다.
  2. 필터는 캐싱기능을 갖는다. 이 기능이 작동하므로 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다.
  3. 필터 서브쿼리는 일반 NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.

서브쿼리 Unnesting

• unnest 힌트를 사용한다.
• 메인과 서브쿼리 같의 계층구조를 풀어 서로 같은 레벨로 만들어 준다는 의미에서 '서브쿼리 Flattening'이라고 부르기도 한다.
• 서브쿼리를 그대로 두면 필터 방식을 사용할 수밖에 없지만, Unnesting 하고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.

NL 세미 조인

• unnest 힌트와 함께 nl_sj 힌트를 사용한다.
• 기본적으로 NL 조인과 같은 프로세스다.
• 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점만 다르다. 필터 오퍼레이션의 기능이기도 하다.

Unnesting된 서브쿼리는 NL 세미조인 외에도 다양한 방식으로 실행될 수 있다.

필터방식은 항상 메인쿼리가 드라이빙 집합이지만, Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.

서브쿼리를 Unnesting 해서 메인쿼리와 같은 레벨로 만들면, 다양한 조인 메소드를 선택할 수 있고, 조인 순서도 마음껏 정할 수 있다.

필터 오퍼레이션보다 더 좋은 실행경로를 찾을 가능성이 높아진다.

ROWNUM - 잘 쓰면 약, 잘못 쓰면 독

• 서브쿼리에 rownum을 쓰면 옵티마이저에게 "이 서브쿼리 블록은 손대지 말라"고 선언하는 것과 다름없다.
• 서브쿼리 Unnesting을 방지하려는 목적이 아니면 서브쿼리에 함부로 쓰지 말자.

서브쿼리 Pushing

• 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
• push_subq, no_push_subq 힌트로 제어한다.
• 이 기능은 Unnesting 되지 않은 서브쿼리에만 작동한다.
• 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술해야 한다.

뷰(View)와 조인

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

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

부분처리가 불가능한 상황에서 NL 조인은 좋은 선택이 아니다. 이런 상황에선 보통 해시 조인이 빠르다.

조인 조건 Pushdown

• 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능
• 이 방식을 사용하면 '건건이' 데이터만 읽어서 조인하고 Group By를 수행할 수 있다.
• 부분범위 처리가 가능하다.
• VIEW PUSHED PREDICATE 오퍼레이션을 통해 이 기능의 작동 여부를 알 수 있다.
• push_pred 힌트를 사용한다.
• no_merge 힌트와 함꼐 사용해야 한다.

스칼라 서브쿼리 조인

스칼라 서브쿼리의 특징

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

스칼라 서브쿼리 캐싱 효과

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

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

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

두 개 이상의 값 반환

• 구하는 값들을 문자열로 모두 결합하고, 바깥쪽 액세스 쿼리에서 substr 함수로 다시 분리하는 방식을 사용
• 인라인 뷰를 사용
  • 뷰를 사용하면, 전체를 읽어야 하거나, 뷰가 머징될 때 Group By 때문에 부분범위 처리가 안 되는 문제가 있다.
• SQL 튜너들이 두 개 이상의 값을 반환해야 할 때 스칼라 서브쿼리와 인라인 뷰 사이에서 많은 고민을 하곤 했다.
  • 11g 이후로는 '조인 조건 Pushdown' 기능이 잘 작동하므로 인라인 뷰를 마음 편히 사용할 수 있게 됐다.

스칼라 서브쿼리 Unnesting

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