SQL BOOSTER - NL조인과 성능
1. 성능 테스트를 위한 테이블 생성
2. 후행 집합에 필요한 인덱스
- NL조인은 후행 테이블 쪽의 조인 조건 컬럼에 인덱스가 필수이다.!
SELECT /*+ GATHER_PLAN_STATSTICS */
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC)ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID T2.CUS_ID
AND T1.CUS_ID = 'CUS_0009'
AND T2.ORD_YMD = '20170218'
GROUP BY T1.CUS_ID;
특정 고객의 주문 일자를 조회하는 패턴이다.
실행 계획을 살펴보면 다음과 같다.
- NL조인을 하고있다. 위쪽 3번째 단계가 선행집합, 아래쪽 단계가 후행집합이다.
- 다시 말해 M_CUS에 먼저 접근하고 T_ORD_JOIN을 나중에 NL조인한다.
- 연결 컬럼은 CUS_ID이다.
실제 실행과정은 위와 같다.
- PK_M_CUS 인덱스를 사용해 CUS_ID 조건에 맞는 데이터 찾음
- 인덱스 리프 블록의ROWID를 사용해 M_CUS데이터에 접근
- M_CUS의 CUS_ID를 이용해 NL 조인을 수행
- 3번의 CUS_ID와 같은 T_ORD_JOIN의 CUS_ID값 찾음 (FULL SCAN)
- 3번 조건과 ORD_YMD 조건이 맞으면 결과로 보낸다.
해당 과정은 비합리적이다. 2000건의 데이터를 위해 26,021번 블록 IO를 수행했다.
T_ORD_JOIN의 CUS_ID컬럼에 인덱스를 만든 후 다시 SQL을 수행해보자
CREATE INDEX X_T_ORD_JOIN_1 ON T_ORD_JOIN(CUS_ID);
SELECT /*+ GATHER_PLAN_STATSTICS LEADING(T1) USE_NL(T2) INDEX(T2.X_T_ROD_JOIN_1)*/
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC)ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID T2.CUS_ID
AND T1.CUS_ID = 'CUS_0009'
AND T2.ORD_YMD = '20170218'
GROUP BY T1.CUS_ID;
- PK_M_CUS 인덱스를 이용해CUS_ID 조건에 맞는 데이터를 찾는다.
- 인덱스 리프 블록의 POWID를 이용해 M_CUS의 실제 데이터 접근한다.
- M_CUS의 CUS_ID로 NL조인을 처리한다.
- CUS_ID값을 사용해 T_ORD_JOIN테이블을 탐색한다. -> 이때 INDEX_RANGE_SCAN으로 검색한다.
- X_T_ORD_JON_1 리프 블록 ROWID를 이용해 T_ORD_JOIN의 실제 데이터에 접근한다.
- ROWID로 접근한 데이터 블록에서 ORD_YMD의 WHERE 조건을 확인한다.
인덱스를 통해 성능을 개선했지만, 여전히 비효율이 발생하는데 6번에서 버려지는 데이터가 많다.
이는 T_ORD_JOIN 인덱스에 ORD_YMD가 없기 때문이다.
실행계획의 5,6단계에서 최종 2000건만 조인결과가 나왔고, 나머지 53000건의 데이터는 fiter에 걸려서 데이터가 버려졌다.
이를 방지하기 위해서는 복합 인덱스로 ORD_YMD를 추가해야한다.
CREATE INDEX X_T_ORD_JOIN_2 ON T_ORD_JOIN(CUS_ID,ORD_YM);
SELECT /*+ GATHER_PLAN_STATSTICS LEADING(T1) USE_NL(T2) INDEX(T2.X_T_ROD_JOIN_2)*/
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC)ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID T2.CUS_ID
AND T1.CUS_ID = 'CUS_0009'
AND T2.ORD_YMD = '20170218'
GROUP BY T1.CUS_ID;
6번 5번 단계를 보면 A-Rows가 모두 2000이다 비효율이 많이 줄었다. Buffers도 줄었다.
NL조인 에서 후행 집합의 인덱스를 제대로 탄 것이다.
NL조인에서 다음을 고려하자
- 후행 집합의 조인 조건 컬럼에는 인덱스가 필수이다.
- 후행 집합에 사용된 조인 조건과 WHERE 조건 컬럼에 복합 인덱스를 고려해야한다.
2. 선행 집합 변경에 따른 쿼리 변경
앞서 본 쿼리에서 선후행 집합을 변형해보자 힌트만 조금 수정하면 된다.
CREATE INDEX X_T_ORD_JOIN_2 ON T_ORD_JOIN(CUS_ID,ORD_YM);
SELECT /*+ GATHER_PLAN_STATSTICS LEADING(T2) USE_NL(T1) INDEX(T2.X_T_ROD_JOIN_2)*/
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC)ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID T2.CUS_ID
AND T1.CUS_ID = 'CUS_0009'
AND T2.ORD_YMD = '20170218'
GROUP BY T1.CUS_ID;
실행계획을 확인해보면, 재밌는 점은 5번을 확인해보면, access조건에 CUS_ID가 있는데, T_ORD_JOIN에 CUS_ID 조건을 준적이 없다! (T1에 있고, CUS_ID는 조인 조건으로 사용됨!)
X_T_ORD_JOIN은 CUS_ID,ORD_YMD 순서로 구성된 인덱스이다. CUS_ID 조건이 같다 조건으로 사용되어야만 ORD_YMD 조건도 효율적으로 사용할 수 있다.
그러므로 오라클의 옵티마이져가 CUS_ID 조건을 T_ORD_JOIN 쪽에도 자동으로 추가해준 것이다.
이처럼 옵티마이져가 자동으로 SQL을 변형하는 기능을 쿼리 변형이라고 한다.
쿼리 변형은 옵티마이져의 영역만이 아니다. 필요에 따라 직접 쿼리를 변형할 필요가 있을 것이다.
정리하자면
- SQL의 변형이 실행 결과에 전혀 영향이 없다.
- T_ORD_JOIN을 선행 집합으로 처리할 경우 이와 같은 변형이 성능에 더 좋다. (변형 하는게 성능이 더 좋아진다는 뜻)
3. 조인 횟수를 줄이자 (1)
NL조인에서 한 가지 더 고려해야할 사항은 조인 횟수를 줄여야한 점이다.
NL조인에서 조인 횟수를 줄인다는 것은 선행 집합의 결과 건수를 줄인다는 말과 같다.
중첩된 반복문 형태의 조인에서 안쪽 반복문은 바깥쪽 반복문의 반복 횟수만큼 수행된다. 그러므로 바깥쪽 반복의 횟수를 줄이면 안쪽 반복 횟수는 저절로 줄어들게 된다.
T_ORD_JOIN테이블을 선행 집합으로 M_CUS 테이블을 NL조인해보자 이때 20170218 주문에 대해 고객등급 A인 고객만 조회한다.
SELECT /*+ GATHER_PLAN_STATSTICS LEADING(T2) USE_NL(T1) INDEX(T2.X_T_ROD_JOIN_2)*/
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC)ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.CUS_GD = 'A'
AND T2.ORD_YMD = '20170218'
GROUP BY T1.CUS_ID;
SQL 힌트를 보면, T_ORD_JOIN를 선행 집합으로 조인하도록 하고 있다. 실행 계획은 아래와 같다.
실행 계획을 살펴보면 NL 조인이 2번과 3번 단계에서 총 두번 나타났다. (일반적으로 두 테이블을 조인하면 한번의 조인만 수행된다)
이와같이 NL 조인이 두 번 나타나는 것은 오라클의 버전이 올라가면서 NL 조인 성능을 높이려는 방법 정도로 이해하면 된다.
5번 단계를 보면 INDEX SKIP SCAN이 있다. INDEX_SKIP_SCAN은 인덱스를 이용해 데이터를 검색하는 방법 중 하나다.
X_T_ORD_JOIN_2는 CUS_ID,ORD_YMD 순서로 구성되어 있다. SQL에 CUS_ID에 대한 조건이 없음으로 위 방식이 활용되었다. 특수한 경우가 아니면 INDEX RANGE SCAN이 더 효율적이다.
주의 깊게 살펴볼 것은 후행 집합의 접근 횟수다. 6번 Starts가 12000번의 접근이 발생했다.
T_ORD_JOIN에서 찾아낸 데이터 건수가 12000이고, 후행 집합인 M_CUS에 12000번의 접근이 발생한다.
이럴 때 적용할 수 있는 방법은 선후행을 뒤집는 것이다. 무엇이 선행일지 알아보기 전 각각의 조건으로 카운트 해보자
SELECT COUNT(*) FROM M_CUS WHERE T1.CUS_GD = 'A'; --60건
SELECT COUNT(*) FROM T_ORD_JOIN T2 WHERE T2.ORD_YMD ='20170218' --12000건
누가봐도 M_CUS가 선행 집합이 되는 것이 현명한 선택이다.
SELECT /*+ GATHER_PLAN_STATSTICS LEADING(T1) USE_NL(T2) INDEX(T2.X_T_ROD_JOIN_2)*/
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC)ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.CUS_GD = 'A'
AND T2.ORD_YMD = '20170218'
GROUP BY T1.CUS_ID;
4. 조인 횟수를 줄이자 (2)
CREATE INDEX X_T_ORD_JOIN_3 ON T_ORD_JOIN(ORD_YMD);
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T2) USE_NL(T1) INDEX(T2 X_T_ORD_JOIN_3) */
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T2.ORD_YMD LIKE '201702%'
GROUP BY T1.CUS_ID;
T_ORD_JOIN의 ORD_YMD 컬럼에만 LIKE 조건이 있다. ORD_YMD에 단독 인덱스만 생성하고 SQL을 수행한다.
6번 단계의 Starts 항목을 보면 209K라는 수치를 확인할 수 있다. NL 조인을 위해 후행 집합을 209,000번 반복 접근하고 있다.
선행 집합을 M_CUS로 변경하는 전략을 고민해보자, 다만 무턱대고 하지말고, 각각 카운트 해보자
SELECT COUNT(*) FROM M_CUS; --90
SELECT COUNT(*) FROM T_ORD_JOIN WHERE ORD_YMD LIKE '201702%'; --209000
CREATE INDEX X_T_ORD_JOIN_3 ON T_ORD_JOIN(ORD_YMD);
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T2 X_T_ORD_JOIN_2) */
T1.CUS_ID, MAX(T1.CUS_NM) CUS_NM, MAX(T1.CUS_GD) CUS_GD, COUNT(*) ORD_CNT, SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT
FROM M_CUS T1, T_ORD_JOIN T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T2.ORD_YMD LIKE '201702%'
GROUP BY T1.CUS_ID;
후행 집합의 연결 조건 컬럼에는 인덱스가 필수다. 조인 조건과 필터 조건을 모두 만족할 수 있는 X_T_ORD_JOIN_2를 사용하자
총 Buffers수치를 보면 1,939번으로 확실하게 개선되었다.
NL 조인에서 후행 집합의 접근 횟수를 줄이려면 선행 집합의 건수가 작아야 한다.
조인에 참여하는 두 테이블을 각각 분리해서 카운트해보고 적은 결과가 나오는 쪽을 선행 집합으로 한다.
5. 여러 테이블의 조인
아래 SQL은 세 개의 테이블을 조인 처리한다. SQL을 실행하고 실행계획을 살펴보자
SELECT /*+ GATHER_PLAN_STATISTICS */
T1.ITM_ID, T1.ITM_NM, T2.ORD_ST, COUNT(*) ORD_QTY
FROM M_ITM T1, T_ORD_JOIN T2, M_CUS T3
WHERE T1.ITM_ID = T2.ITM_ID
AND T3.CUS_ID = T2.CUS_ID
AND T1.ITM_TP = 'ELEC'
AND T3.CUS_GD = 'B'
AND T2.ORD_YMD LIKE '201702%'
GROUP BY T1.ITM_ID, T1.ITM_NM, T2.ORD_ST;
총 Buffers도 964라 충분히 괜찮다.
먼저 M_CUS를 선행 집합으로 T_ORD_JOIN과 NL조인을 수행한다. 그 결과를 M_ITM과 해시 조인한다.
위 SQL도 이미 괜찮지만 조금 더 성능 개선이 가능하다.
SELECT COUNT(*) FROM M_CUS T3 WHERE T3.CUS_GD ='B'; --30
SELECT COUNT(*) FROM M_ITM T1 WHERE T1.ITM_TP ='ELEC'; --10
다음으로 M_CUS와 T_ORD_JOIN만 조인 처리해 카운트해보고, M_ITM과 T_ORD_JOIN도 별도로 조인해보자
---7만
SELECT COUNT(*) CNT
FROM M_CUS T3, T_ORD_JOIN T2
WHERE T3.CUS_ID = T2.CUS_ID
AND T3.CUS_GD = 'B'
AND T2.ORD_YMD LIKE '201702%';
-- 2만6천
SELECT COUNT(*) CNT
FROM M_ITM T1, T_ORD_JOIN T2
WHERE T1.ITM_ID = T2.ITM_ID
AND T1.ITM_TP = 'ELEC'
AND T2.ORD_YMD LIKE '201702%'
각각 7만건과 2만6천건이 나온다.
카운트 SQL을 종합해보면, M_ITM과 T_ORD_JOIN을 먼저 NL 조인하는 것이 성능에 유리하다.
M_ITM이 선행 집합이 되어야 한다. M_ITM이 선행하려면, T_ORD_JOIN에는 연결 조건 컬럼인 ITM_ID 인덱스가 있어야 한다.
아래와 같이 ITM_ID, ORD_YMD순서의 복합 인덱스를 만든 후 SQL을 실행해 보자. M_ITM, T_ORD_JOIN이 NL 조인이 되도록 힌트도 추가해야한다.
실행계획을 살펴보면 아래와 같이 Bufferes가 964에서 364로 개선되었다.
- 위쪽은 M_CUS와 T_ORD_JOIN이 NL 조인된 경우고, 아래쪽은 M_ITM과 T_ORD_JOIN이 NL조인된 경우다.
- 위쪽은 NL 조인 과정에서 T_ORD_JOIN을 30번 접근하고, 아래쪽은 10번만 접근한다.
- 어떤 테이블을 NL 조인의 선행 집합으로 정해야 할 지 판단이 서지 않을 때 유용하게 사용할 수 있는 방법이다.
6. 과도한 성능개선
위 쿼리는 Buffers도 364이고 성능이 꽤 괜찮은 SQL이다. 하지만 이 실행계획에는 여전히 비효율이 존재하므로 더 성능 개선 할 수 있다. (필자의 개인적인 생각은, 이 정도면 추가적인 개선은 필요 없다.)
실행계획을 보면 TABLE ACCESS FULL이 이 두 곳에서 발생하고 있다. 바로 3번과 6번 단계다.
성능 개선을 할 때, 가장 단순한 접근은 FULL SCAN을 제거하는 것이다. FULL SCAN을 제거하기 위해 다음과 같은 인덱스를 생성해보자
- M_ITM : ITM_TP, ITM_ID, ITM_NM으로 구성된 복합 인덱스
- (TABLE ACCESS BY INDEX ROWID)
- M_CUS: CUS_GD,CUS_ID 구성된 복합 인덱스
FULL SCAN이 모두 제거되었다. 따라서 Buffers 364에서 360 정도로 나아졌다. 인덱스를 두개나 추가했지만 IO를 고잦 4밖에 줄이지 못했다.
FULL SCAN을 제거했으나 효과가 없다면, 실행계획 5번 단계의 TABLE ACCESS BY INDEX ROWID를 제거하는 방법을 고민해 볼 수 있다.
이를 위해서는 T_ORD_JOIN에 ITM_ID, ORD_YMD,CUS_ID,ORD_ST칼럼이 모두 포함된 인덱스를 구성해야한다.
CREATE INDEX X_T_ORD_JOIN_5 ON TORD_JOIN(ITM_ID,ORD_YMD,CUS_ID,ORD_ST);
Buffers 177로 이전보다 절반 가까이 줄었다. 이렇게 까지 튜닝 해야 할까 고민이 필요하다.
노력보다 얻는 것이 크지 않다고 생각된다. 자주 사용되는 핵심 SQL이라면 이정도 까지 튜닝하는 것이 필요할 수도 있다. 하지만 모든 SQL을 이와 같은 측면으로 접근하면, 인덱스가 넘쳐나서 전반적으로 문제 발생할 수 있다.
7. 선행 집합은 항상 작은 쪽 이어야하는가?
NL 조인에서는, 조인 횟수를 줄이는 것이 주요 성능 개선 포인트다.
그러므로 건수가 작은 쪽을 선행 집합으로 선택해야 한다고 설명해왔다.
아래 SQL은 M_CUS와 T_ORD_BIG의 한달 간의 데이터를 조인하고 있다.
SELECT /*+GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T2 X_T_ORD_BIG_$) */
T1.CUS_ID, T1.CUS_NM, SUM(T2.ORD_AMT)
FROM M_CUS T1, T_ORD_BIG T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T2.ORD_YMD LIKE '201701%'
GROUP BY T1.CUS_ID, T1.CUS_NM
ORDER BY SUM(T2.ORD_AMT) DESC;
해당 쿼리를 수행하는데 꽤 오랜 시간이 걸린다. 실행계획을 살펴보자
M_CUS에는 90건의 데이터가 있고, T_ORD_BIG에는 2430000건이 있다. 당연히 작은 M_CUS를 선행집합으로 NL조인하는 것이 합당해보이지만, 성능이 좋지 않다.
위 SQL은 T_ORD_BIG에서 한 달간의 데이터를 인덱스로 읽기에는 무리가 있다.
인덱스로 읽어야 할 데이터가 너무 많기 때문이다. FULL SCAN이 더 효율적일 수 있다. 하지만 T_ORD_BIG 90번이나 FULL SCAN하기에는 부담이 크다.
그렇다면, T_ORD_BIG을 NL의 선행집합으로 FULL SCAN처리 해보면 어떨까 선행 집합은 한번만 접근하면 되므로 T_ORD_BIG에 FULL SCAN을 한 번만 하면 된다. 아래와 같이 힌트를 변경해 SQL을 실행해보자
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T2) USE_NL(T1) FULL(T2) */
T1.CUS_ID, T1.CUS_NM, SUM(T2.ORD_AMT)
FROM M_CUS T1, T_ORD_BIG T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T2.ORD_YMD LIKE '201701%'
GROUP BY T1.CUS_ID, T1.CUS_NM
ORDER BY SUM(T2.ORD_AMT) DESC;
시간이 7.58초로 단축되었다.
항상 크기가 작은 쪽을 선행집합으로 선택해야하는 것은 아니다. 몇 개의 규칙에 너무 얽매이지말자!
출처
https://www.yes24.com/Product/Goods/82818767
SQL BOOSTER - 예스24
SQL BOOSTER는 프로젝트 성공을 위한 SQL 필독서다. 이 책은 마치 프로젝트를 진행하는 순서처럼 구성되어 있다. 프로젝트 투입을 위해 필요한 SQL 기술을 설명하고, 성능 테스트를 위해 필요한 기술
www.yes24.com