최근에 조회 소요시간을 크게 개선한 쿼리 튜닝에 대해 적어보고자 한다.
❗문제
50건의 조회결과를 갖는 쿼리 수행시간이 약 3800초이고, 해당 쿼리는 월 마감작업시 필수로 수행되는 쿼리이므로, 현업의 마감 작업을 원활하게 하기 위해 개선하고자한다.
해당 쿼리에는 뷰2개와 테이블 1개를 사용하고, 편의를 위해 아래와 같이 명명하겠다.
VIEW1, VIEW2, TABLE1
VIEW1, 2는 아래와같이 5개의 테이블의 UNION ALL로 이루어져있으며, 50,000,000건 이상의 ROW를 가지고있다.
/* VIEW1. */
SELECT *
FROM T1_VIEW1
UNION ALL
SELECT *
FROM T2_VIEW1
UNION ALL
SELECT *
FROM T3_VIEW1
UNION ALL
SELECT *
FROM T4_VIEW1
UNION ALL
SELECT *
FROM T5_VIEW1/* VIEW2. */
SELECT *
FROM T1_VIEW2
UNION ALL
SELECT *
FROM T2_VIEW2
UNION ALL
SELECT *
FROM T3_VIEW2
UNION ALL
SELECT *
FROM T4_VIEW2
UNION ALL
SELECT *
FROM T5_VIEW2기존의 조회 쿼리는 다음과 같다.
실제 쿼리는 더 길지만, 문제가 되는 부분만 가져왔다.
/* 기존쿼리 (아래쿼리와 유사함) */
SELECT AA.A2, BB.B2, AA.A3, AA.A4
, SUM(A5), SUM(A6), SUM(A7), ...
, FUNC_A(AA.A2, AA.A3, AA.A4) AMT
FROM (
SELECT *
FROM VIEW1 A
WHERE A1 < '202210'
EXISTS (SELECT 1 FROM TABLE1 B WHERE B.B1 = A.A2 AND B.B3 = '0')
UNION ALL
SELECT *
FROM VIEW1 A
WHERE A1 = '202210'
EXISTS (SELECT 1 FROM TABLE1 B WHERE B.B1 = A.A2 AND B.B3 = '0')
UNION ALL
SELECT *
FROM VIEW2 A
WHERE A1 >= '20221001'
AND A1 <= '20221030'
EXISTS (SELECT 1 FROM TABLE1 B WHERE B.B1 = A.A2 AND B.B3 = '0')
) AA
, TABLE1 BB
WHERE BB.B1 = AA.A2 (+)
GROUP BY AA.A2, BB.B2, AA.A3시도1. FUNC_A 함수의 수행 속도의 문제라 생각하고 해당 함수 처리속도를 개선 시도
👉 FUNC_A의 처리속도를 개선하였으나 크게 차이점이 없음.
시도2. VIEW 대신, 실제로 사용하는 컬럼의 테이블만 UNION ALL로 묶어서 처리
/* 개선쿼리1 */
SELECT AA.A2, BB.B2, AA.A3, AA.A4
, SUM(A5), SUM(A6), SUM(A7), ...
, FUNC_A(AA.A2, AA.A3, AA.A4) AMT
FROM (
SELECT *
FROM VIEW1 A
WHERE A1 < '202210'
EXISTS (SELECT 1 FROM TABLE1 B WHERE B.B1 = A.A2 AND B.B3 = 'J')
UNION ALL
SELECT *
FROM T1_VIEW1 A
WHERE A1 = '202210'
EXISTS (SELECT 1 FROM TABLE1 B WHERE B.B1 = A.A2 AND B.B3 = 'J')
UNION ALL
SELECT *
FROM T2_VIEW2 A
WHERE A1 >= '20221001'
AND A1 <= '20221030'
EXISTS (SELECT 1 FROM TABLE1 B WHERE B.B1 = A.A2 AND B.B3 = 'J')
) AA
, TABLE1 BB
WHERE BB.B1 = AA.A2 (+)
GROUP BY AA.A2, BB.B2, AA.A3👉 2800초대로 줄었으나 여전히 조회시간이 오래 걸림 추가 개선 필요, 서브쿼리 EXISTS 를 위해 TABLE1 테이블을 FULL SCAN 하는것을 확인.
이후로 많은 시도를 해 보았지만, 유의미한 개선점을 찾지 못하였다. 열심히 구글링하는 중에 아래와 같은 키워드를 찾았다.
IN > EXISTS > JOIN의 순서대로 조회 시간이 오래 걸린다. (물론 예외는 있다.)
UNINON ALL 은 RESULT SET의 개수를 줄이는게 성능에 큰 효과를 준다.
어찌보면 당연한 말인데, 눈에 보이는 쿼리 외에 VIEW내부의 UNION ALL 을 간과하고있었다는 생각이 들었다.
위 사항과 시도2에서 발견한 내용을 고려하여 아래와 같이 쿼리를 최종 개선했다.
시도3. TABLE1의 수행 결과를 WITH로 변경하여 FULL SCAN 테이블의 ROW수를 줄이고 EXISTS 대신 JOIN 사용, VIEW1을 구성하고 있는 테이블을 쪼개어 각각 조건 부여.
/* 개선쿼리2 */
WITH DATA AS(
SELECT /*+ MATERIALIZE */ B1, B2
FROM TABLE1
WHERE B3 ='0'
)
SELECT AA.A2, BB.B2, AA.A3, AA.A4
, SUM(A5), SUM(A6), SUM(A7), ...
, FUNC_A(AA.A2, AA.A3, AA.A4) AMT
FROM (
SELECT *
FROM T1_VIEW1 A, DATA B
WHERE B.B1 = A.A2
AND A1 < '202210'
UNION ALL
SELECT *
FROM T2_VIEW1 A, DATA B
WHERE B.B1 = A.A2
AND A1 < '202210'
UNION ALL
SELECT *
FROM T3_VIEW1 A, DATA B
WHERE B.B1 = A.A2
AND A1 < '202210'
.
.
.
UNION ALL
SELECT *
FROM T2_VIEW2 A, DATA B
WHERE B.B1 = A.A2
AND A1 >= '20221001'
AND A1 <= '20221030'
) AA
, TABLE1 BB
WHERE BB.B1 = AA.A2
GROUP BY AA.A2, BB.B2, AA.A3👉 수행속도를 150초대로 대폭 줄임.
❗결론
VIEW 내부에 별도의 조건없이 UNION ALL 되어있는 테이블들을 쪼개어 조회 조건을 부여해 RESULT SET을 줄인것이 성능에 큰 효과를 준 것으로 보인다. HINT나 INDEX를 사용하는것도 좋지만, MAIN 쿼리를 좀 더 분석하도록 하자!
