SQL 튜닝 - JOIN(1)

1. Nested Loop Join

- 정의: 2개 이상의 테이블에서 하나의 테이블을 기준으로 순차적으로 상대방 테이블의 Row를 결합하여 원하는 결과를 추출하는 테이블 연결 방식

- 성능: Driving Table의 Row를 어느 정도 줄일 수 있는가에 따라 NL 조인의 성능이 결정된다.

- 사용 케이스: 조인된 결과가 두 테이블의 카테시안 곱에 비해 10~15% 이하라면, 인덱스에 의한 조인에서 발생하는 랜덤 액세스의 오버헤드가 전체 테이블을 검색하는 비용보다 적게 되어 조인이 더 효율적으로 된다.

- NL 조인에서의 튜닝: 드라이빙 테이블을 선정하는 기준과 드리븐 테이블에 사용된 적절한 인덱스의 선정

 

cf) Unique 인덱스 -> Index Unique Scan

    Non-Unique 인덱스 -> Index Range Scan

    인덱스가 없으면 -> FTS

* 조인 절차

일반적인 경우 연결고리 인덱스에 의한 조인을 한 후, 랜덤 액세스에 의하여 Row값을 읽어 들인다. 읽어 들이는 기본 단위는 데이터 블록이기 때문에 한 데이터 블록을 통째로 읽어 들인다.

 

* NL 조인의 예

SELECT /*+ USE_NL(A B)*/ A.ENAME, A.JOB, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND A.HIREDATE >= TO_DATE('19810101', 'YYYYMMDD');

* NL 조인의 장단점

- NL조인은 데이터를 랜덤 액세스에 의하여 접근하기 때문에 결과 집합이 많다면 수행 속도가 저하된다.

- 드라이빙 테이블은 테이블의 크기가 작거나, WHERE절 조건을 사용하여 적절히 결과 집합을 제한할 수 있어야 한다.

- 연결이 되는 테이블인 Driven 테이블에는 조인을 위한 적절한 인덱스가 생성되어 있어야 한다.

- 연결고리가 되는 칼럼은 Unique한 속성을 가진 칼럼 인덱스를 이용하거나 분포도가 좋은 칼럼에 대한 인덱스를 이용한다.

* Nested Loop 조인 파라미터

- OPTIMIZER_INDEX_CACHING

: CBO가 Nested Loop 조인에 대한 반복 처리를 인덱스를 사용하여 수행하도록 영향을 줄 수 있다. 이러한 처리를 위한 인덱스의 실행 비용은 Buffer Cache에 존재하는 인덱스 블록에 의해 영향을 받는다.

파라미터 유형	정수
기본값	0
파라미터 동적 설정 영역	ALTER SESSION
파라미터 값 범위	0 ~ 100

 

- OPTIMIZER_INDEX_COST_ADJ

: 옵티마이저가 엑세스 경로를 결정하는데 있어 인덱스에 대한 선호도를 나타내는 값이다.

파라미터 유형	정수
기본값	100
파라미터 동적 설정 영역	ALTER SESSION
파라미터 값 범위	0 ~ 10000

2. Driving Table

- 정의: 조인으로 연결되는 테이블 중 첫 번째로 액세스되는 테이블(Outer Table)

- "분포도가 양호하다" = Where절 조건에 의해서 출력된 레코드가 가장 적은 테이블

- 조건 절에 의해 선택되는 결과 집합의 크기가 중요하다.

 

- 오라클은 두 테이블의 인덱스를 선택하는데 칼럼의 매칭률을 고려한다. 다음으로 인덱스의 연결고리 칼럼에 대한 인덱스 생성 여부를 고려한다. 조인되는 테이블 한 쪽에 인덱스가 없는 경우에는 인덱스가 없는 쪽 테이블을 드라이빙 테이블로 사용해야 한다. 인덱스가 있는 테이블의 경우 조인 작업마다 FTS가 반복적으로 일어나기 때문이다.

 동일한 조건 하에서는 From절의 테이블 Parsing 순서를 따르게 된다. From절에서 멀리있는 테이블이 드라이빙 테이블로 선정된다.

3. Sort Merge Join

연결고리에 이상이 있는 경우, 대용량의 자료를 조인할 경우 인덱스의 치명적인 단점인 랜덤 액세스의 오버헤드가 NL조인의 장점을 넘어서 버리는 경우에 적절하게 사용하는 조인 방법

* Sort Merge 조인의 개념

- 조인이 되는 각각의 테이블 자료를 SCAN 방식으로 읽어 들이거나 인덱스를 사용하여 메모리로 읽어들인다. 이 때 사용되는 인덱스는 조인을 위한 목적이 아닌 조인이 수행될 집합을 제한하기 위해 사용된다.

- SORT_AREA_SIZE 튜닝: 정렬을 수행하기 위한 공간은 SORT_AREA_SIZE의 크기만큼 메모리를 할당 받아 사용되는데, 메모리가 부족하다면 Temporary 테이블 스페이스(디스크 공간)을 사용하여 정렬하게 되고 이는 조인의 성능을 저하시킬 수 있다.

- 단점: 정렬 작업으로 인해 발생, 두 조인 집합의 크기가 많이 차이 난다면 두 집합을 정렬하는 시간의 차이가 커진다. 정렬이 완료되기 전까지 조인 작업을 시작할 수 없기 때문에 대기 시간이 발생한다.

- 필요 이유: 조인되는 각 집합에서 일치되는 자료를 보다 효율적으로 찾기 위해서

* SM 조인의 예

SELECT /*+ USER_MERGE(A B)*/ A.ENAME, A.JOB, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.HIREDATE >= TO_DATE('19810101', 'YYYYMMDD')
AND A.DEPTNO = B.DEPTNO;

* SM 조인의 장단점

- NL조인에서처럼 조인 결합 조건으로 사용되는 연결고리 칼럼에 인덱스가 생성되어 있지 않은 경우에 빠른 조인을 위해 사용

- 결합이 되는 테이블을 각각 독립적으로 읽어 들인 뒤, 각 결과 집합을 조인되는 칼럼 조건에 대하여 정렬을 수행

- SM 튜닝: 독립적으로 읽혀지는 두 테이블을 빨리 읽어 들이는 것, Sort Area를 잘 활용하는 것( 예: 오라클 Parallel Query를 이용하여 FTS 속도를 높이는 것)

 

* SM 조인 파라미터

- DB_FILE_MULTIBLOCK_READ_COUNT

테이블을 스캔할 때 I/O를 최소화하기 위해 사용하는 파라미터, Sequential Scan을 수행할 때 한 번에 읽을 최대 블록의 수를 지정

OLTP에서는 4~16으로 설정하고, DSS(Decision Support System)와 DW 환경에서는 크게 설정하여 수행 성능을 향상시킨다.

파라미터 유형	정수
기본값	8
파라미터 동적 설정 영역	ALTER SESSION, ALTER SYSTEM
파라미터 값 범위	운영 체제에 따라 다름

 

- SORT_AREA_SIZE

조회된 자료를 정렬하는데 사용할 메모리의 크기를 Byte 단위로 지정한다. 정렬한 후에는 SORT_AREA_RETAINED_SIZE에 의해 지정된 만큼만 유지되고 나머지 영역은 반환된다. 오라클이 모든 결과를 출력한 후에는 모든 메모리를 반환한다.

파라미터 유형	정수
기본값	65536
파라미터 동적 설정 영역	ALTER SESSION
파라미터 값 범위	운영 체제에 따라 다름

4. Hash Join

* Hash 조인의 개념

- Hash 함수를 사용하여 두 집합의 자료를 결합하는 것

- Hash 함수를 적용하여 각 자료를 Hash Bucket으로 나누어 담는다면 같은 값을 가진 자료는 같은 Bucket에 모이게 되어 정렬을 수행하지 않고도 보다 효율적으로 상대 집합을 검색할 수 있게 된다.

- 단점: Hash Bucket이 조인 집합에 구성되어 Hash 함수 결과를 저장해야 하는데 이 때 많은 메모리와 CPU 자원이 소모되게 된다.

 

* Hash 조인의 절차

1) Hash 조인에서 사용될 Hash 파티션의 개수를 정한다.

 

 " Number Of Partitions = ( 0.8 * HASH_AREA_SIZE ) / ( DB_BLOCKS_SIZE * HASH_MULTIBLOCK_IO_COUNT) "

 

2) 두 테이블 중 작은 테이블을 먼저 읽어 HASH 알고리즘을 적용한다.

3) 두 알고리즘 중 첫 번째 HASH 알고리즘을 적용한 값을 기준으로 하여 적절한 파티션에 HASH된 값이 저장된다. 이 때, 두 번째 알고리즘이 적용된 HASH값은 나중에 다시 HASH를 적용해야 할 경우를 대비하여 ROW 값과 함께 저장된다.

4) 두 가지 HASH값에 대한 비트맵이 구성된다. (이렇게 구성된 비트맵은 다른 테이블을 조인할 때 같은 HASH 키가 있는지 체크할 수 있도록 해준다.) 

HASH 영역이 모두 사용되는 경우에는 파티션 중 가장 큰 것을 선택하여 디스크에 저장하고, 디스크에 써진 파티션에 해당되는 HASH 값을 저장할 경우가 생기면 디스크 정보를 갱신한다.(이 때문에 DISK I/O가 발생)

5) 다른 테이블에 첫 번째 HASH 알고리즘을 적용한다.

6) HASH된 값을 현재 메모리에 있는 첫 번째 테이블 파티션의 HASH값을 기준으로 검색하여 일치되는 값을 두 번째 테이블 파티션에 저장한다. 이 작업은 두 번째 테이블의 모든 ROW에 대해 한 번씩 실행한다.

7) 두 번째 HASH 함수를 사용해 값을 고르게 분포시킨 후 상대 테이블의 매칭되는 파티션과 조인을 수행한다.

 

[내부적인 분류]

- 인-메모리 HASH 조인

  빌드 입력이 HASH 영역에 모두 위치할 수 있는 경우 인-메모리 HASH 조인을 수행한다.

- 유예 HASH 조인

  빌드 입력이 HASH 영역보다 크면 Tempoaray Segment에 저장된 파티션과 조인하는 유예 HASH 조인을 수행한다.

 

* HASH 조인의 예

SELECT /*+ USE_HASH(B A) */ A.ENAME, A.JOB, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO;

DEPT가 EMP보다 작은 테이블이므로 HASH 테이블의 대상이 된다.

* HASH 조인의 장단점

- CBO 모드에서 옵티마이저가 판단해서 적용할 수 있으며, 테이블의 통계 정보가 있어야 한다.

- HASH 조인은 Equi Join에서만 가능하다. 크거나 같다, 작거나 같다와 같은 관계 연산자를 사용하여 조인을 수행할 수 없다.

- Driving 테이블의 row를 조인하기 위해 인덱스를 필요로 하지 않는다.

- Hash 조인은 병렬 처리를 사용함으로써 조인의 성능을 향상시키기 때문에 많은 하드웨어의 CPU와 메모리 자원이 필요하다. 따라서 작은 집합보다 비 대칭형으로 구성된 대량의 자료를 조인할 경우에 사용하는 것이 효율적이다.

 

* HASH 조인 파라미터

- HASH_JOIN_ENABLED

TRUE로 지정할 경우 쿼리에서 HASH 조인을 사용할 수 있게 된다.

파라미터 유형	문자
기본값	TRUE
파라미터 동적 설정 영역	ALTER SESSION
파라미터 값 범위	TRUE/FALSE

- HASH_AREA_SIZE

HASH 조인을 수행할 때 HASH 테이블을 구성하기 위해 사용될 메모리의 크기를 Byte 단위로 지정한다. 너무 작게 지정하면 Hash 파티션이 임시 테이블 스페이스에 저장되어 조인 성능이 떨어지며, 값이 너무 크면 OS의 메모리가 부족하게 된다.

파라미터 유형	정수
기본값	SORT_AREA_SIZE * 2
파라미터 동적 설정 영역	ALTER SESSION
파라미터 값 범위	운영 체제에 따라 다름

- HASH_MULTIBLOCK_IO_COUNT

HASH 조인을 수행할 때 한 번의 I/O 작업에 읽을 Sequential 블록의 수를 지정한다.

파라미터 유형	정수
기본값	질의문에 따라 다름, V$PARAMETER에 0으로 나타남
파라미터 동적 설정 영역	ALTER SESSION
파라미터 값 범위	운영 체제에 따라 다름, 대략 1 to (65536/DB_BLOCK_SIZE)

5. Join 비교

NL 조인	원하는 결과가 전체 집합에 비해 적은 경우
SM 조인	두 결과 집합의 크기가 많이 차이나는 경우에는 비효율적
Hash 조인	크기가 비슷하지 않은 집합의 조인

구분	Nested Loop Join	Sort Merge Join	Hash Join
Optimizer Hint	/*+ USE_NL( ) */	/*+ USE_MERGE( ) */	/*+ USE_HASH( ) */
조인 조건	Any Join	Any Join(주로 Equi Join)	Equi Join만 가능
조인 성능 포인트	조인순서, 연결고리 칼럼의 인덱스	Memory, Temporary Segment	CPU, Memory, Temporary Segment
특징	- 드라이빙 테이블의 row 수가 적거나 조인의 연결고리에 적절한 액세스 경로가 있는 경우 효율적 - Sort Merge or Hash Join에 비해 First_Rows 방식에 효율적	- 조인 연결고리 인덱스가 없거나, 조인 집합을 구성하는 검색 조건이 조인 범위를 줄여주지 못하는 경우 효율적 - 제한된 메모리로도 실행 가능	- 조인 연결고리 인덱스가 없거나, 조인 집합을 구성하는 검색 조건이 조인 범위를 줄여 주지 못하는 경우 효율적 - 일반적으로 Sort Merge 조인보다 수행 성능이 좋음
단점	조인 연결고리 인덱스가 업석나, 조인 집합을 구성하는 검색 조건이 범위를 줄여주지 못할 경우 비효율적	- 조인이 되는 두 집합을 정렬해야 함 - First_Rows 보다 All_Rows를 위해 설계됨.	- Hash 테이블을 위한 많은 메모리가 필요 - First_Rows를 항상 빠르게 출력하는 것은 아님