[쿼리튜닝] 공통쿼리 개선
[쿼리튜닝] 공통쿼리 개선
문제 상황
시스템의 주요 통합 조회 화면에서 조회시간이 오래걸려 사용자들의 불만이 지속적으로 발생했습니다.
확인 결과 조회쿼리가 공통 쿼리로 사용되고 있어 20개 이상의 팝업과 그리드에서 동시에 사용 중이었고, 모든 화면의 응답시간이 평균 4.5초로 매우 느렸습니다.
하나의 공통 쿼리를 호출하는 수많은 페이지들…..
해당 공통쿼리 실행계획
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 388 | 255K| 480K (2)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL | TEST_RESULT_VAR | 5 | 60 | 275 (2)| 00:00:01 | ← 1
| 3 | SORT AGGREGATE | | 1 | 16 | | |
|* 4 | TABLE ACCESS FULL | TEST_RESULT_VAR | 3 | 48 | 275 (2)| 00:00:01 | ← 2
| 5 | SORT AGGREGATE | | 1 | 12 | | |
|* 6 | TABLE ACCESS FULL | TEST_RESULT_VAR | 5 | 60 | 275 (2)| 00:00:01 | ← 3
| 7 | SORT AGGREGATE | | 1 | 16 | | |
|* 8 | TABLE ACCESS FULL | TEST_RESULT_VAR | 1 | 16 | 275 (2)| 00:00:01 | ← 4
| 9 | SORT AGGREGATE | | 1 | 12 | | |
|* 10 | TABLE ACCESS FULL | TEST_RESULT_VAR | 5 | 60 | 275 (2)| 00:00:01 | ← 5
| 11 | SORT AGGREGATE | | 1 | 16 | | |
|* 12 | TABLE ACCESS FULL | TEST_RESULT_VAR | 3 | 48 | 275 (2)| 00:00:01 | ← 6
| 13 | SORT AGGREGATE | | 1 | 12 | | |
|* 14 | TABLE ACCESS FULL | TEST_RESULT_VAR | 5 | 60 | 275 (2)| 00:00:01 | ← 7
| 15 | SORT AGGREGATE | | 1 | 16 | | |
|* 16 | TABLE ACCESS FULL | TEST_RESULT_VAR | 1 | 16 | 275 (2)| 00:00:01 | ← 8
| 17 | SORT GROUP BY | | 3 | 45 | 276 (2)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | TEST_RESULT_VAR | 5 | 75 | 275 (2)| 00:00:01 | ← 9
| 19 | SORT ORDER BY | | 388 | 255K| 480K (2)| 00:00:19 |
|* 20 | HASH JOIN RIGHT OUTER | | 388 | 255K| 675 (2)| 00:00:01 |
... (나머지 JOIN 생략)
------------------------------------------------------------------------------------------------------------------------------
공통쿼리 분석
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
SELECT
F.STATUS_NM AS PROGRESS_STATUS
, A.CLIENT_ID
, A.CLIENT_NAME
, A.OUTSOURCING_YN
, CASE
WHEN (SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID) =
(SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID
AND IA.RESULT_CODE IS NULL)
THEN '입력대기'
WHEN (SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID) =
(SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID
AND IA.RESULT_CODE IS NOT NULL)
THEN '입력완료'
ELSE '입력중'
END AS INPUT_STATUS
, CASE
WHEN (SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID) =
(SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID
AND IA.RESULT_CODE IS NULL)
THEN 'N'
WHEN (SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID) =
(SELECT COUNT(*) FROM TEST_RESULT_VAR IA
WHERE IA.RESULT_SN = A.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID
AND IA.RESULT_CODE IS NOT NULL)
THEN 'Y'
ELSE 'N'
END AS IS_COMPLETE
, A.SKIP_YN
, A.STAGE_FLAG
, (SELECT IA.STAGE_NO
FROM TEST_RESULT_VAR IA
WHERE A.RESULT_SN = IA.RESULT_SN
AND IA.ACTIVE_YN = 'Y'
AND IA.LAB_ID = A.LAB_ID
GROUP BY IA.STAGE_NO) AS CURR_STAGE
, (SELECT MAX(IA.STAGE_NO)
FROM TEST_RESULT_VAR IA
WHERE A.RESULT_SN = IA.RESULT_SN
AND IA.LAB_ID = A.LAB_ID) AS MAX_STAGE
, A.CYCLE_NO
, CASE WHEN K.PLAN_END_DT < K.REAL_END_DT THEN 'Y' ELSE 'N' END AS TIMEOUT_YN
, K.SHEET_ID
...
FROM ORDER_INFO A
LEFT JOIN STATUS_CODE F ON F.CODE_ID = A.STATUS_CODE
LEFT JOIN TEST_PLAN K ON K.RESULT_SN = A.RESULT_SN;
...
공통 쿼리 문제점
SELECT 절에서 동일한 TEST_RESULT_VAR 테이블을 중복적으로 조회해 각 row 마다 서브쿼리가 반복 실행
선택지
각 페이지별 쿼리 분리
- 장점: 각 화면에 최적화
- 단점: 모든 파일 수정, 사이드 이펙트 예측 불가, 소요 시간 큼
공통 쿼리 최적화
- 장점: 한 번의 수정으로 사용하는 곳 전체 개선
- 단점: 여전히 일부 불필요한 컬럼 존재
영향 범위와 효율을 고려해 2번을 선택했습니다.
개선 전략
9번의 서브쿼리 → 1번의 LEFT JOIN으로 통합
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- BEFORE: 각 row마다 9번 실행
각 row마다:
- COUNT(*) 실행 (1번)
- COUNT(*) WHERE IS NULL 실행 (1번)
- COUNT(*) 실행 (1번) - 중복!
- COUNT(*) WHERE IS NOT NULL 실행 (1번)
... (총 9번)
-- AFTER: 1번의 GROUP BY로 모든 값 사전 계산
LEFT JOIN (
SELECT ... COUNT(*) ... -- 한 번에 계산!
GROUP BY RESULT_SN, LAB_ID
) VAR_SUM
성능 개선 결과
개선 후 실행계획
1
2
3
4
5
6
7
8
9
10
11
12
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 388 | 274K| 676 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 388 | 274K| 676 (2)| 00:00:01 |
|* 2 | HASH JOIN RIGHT OUTER | | 388 | 274K| 675 (2)| 00:00:01 |
... (중간 JOIN 생략)
| 47 | VIEW | | 1861 | 136K| 277 (2)| 00:00:01 |
| 48 | HASH GROUP BY | | 1861 | 35359 | 277 (2)| 00:00:01 | ← 집계!
| 49 | JOIN FILTER USE | :BF0000 | 25501 | 473K| 275 (2)| 00:00:01 |
|* 50 | TABLE ACCESS FULL| TEST_RESULT_VAR| 25501 | 473K| 275 (2)| 00:00:01 | ← 1번만!
-------------------------------------------------------------------------------------------------------------------------------
실제 조회 시간 비교
7배 성능 향상
- 이전 평균 4.3초 —> 튜닝 후 평균 0.6초 (약 86% 개선)
배운 점
SELECT 절 서브쿼리의 위험성
SELECT 절의 스칼라 서브쿼리는 메인 쿼리의 각 row마다 실행되므로 성능 저하의 주요 원인이 됩니다.
1
2
3
SELECT
(SELECT COUNT(*) FROM TABLE WHERE ...) -- N번 실행
FROM MAIN_TABLE -- N개 row
JOIN vs 서브쿼리
- 서브쿼리: 가독성 좋지만 반복 실행
- JOIN: 한 번에 처리 + 옵티마이저 최적화 가능
공통 쿼리?
공통 쿼리는 유지보수는 편하지만,각 화면에 맞지 않는 불필요한 컬럼과 조건이 포함될 수 있습니다.
이번에는 공통 쿼리 최적화로 빠른 성과를 냈지만, 장기적으로는 화면별 쿼리 분리하는게 맞다 생각합니다.
설계가 진짜 중요한거 같다.
해당 튜닝 과정에 사용된 테이블 및 컬럼 이름은 회사 보안 정책에 따라 실제 이름과 다르게 수정하였습니다. 매칭되지 않는 부분이 있을 수 있습니다.
This post is licensed under
CC BY 4.0
by the author.