운영 환경 오픈 후, 속도가 급격히 저하되거나 쿼리 조회가 멈추는 현상이 발생했습니다.
이를 모니터링하면서 문제가 심화되는 것을 확인했고, 이에 따라 원인을 분석하고 해결책을 모색하게 되었습니다. 함수와 배치에서의 쿼리 조회 방식을 변경하여 성능을 크게 개선할 수 있었습니다.
이번 글에서는 제가 진행한 함수 및 쿼리 최적화 과정을 공유하려고 합니다.
문제 분석 및 원인 파악
운영 환경에서 배치 작업 중 속도 저하와 조회 멈춤 현상을 겪으면서, 먼저 시스템의 성능을 모니터링하며 문제의 원인을 분석했습니다. 조회 대상은 약 200건 정도였으며, 상태가 '전송완료'인 건들 중 '상태 전송대상'으로 변경된 값들을 조회하는 쿼리였습니다. 이때 대상 건들이 많아지면서 조회 속도가 급격히 떨어지거나, 아예 멈추는 현상이 발생했습니다.
아래는 예시 쿼리입니다. 일부 이해를 돕기 위해 한글로 테이블 명칭을 변경하였습니다.
SELECT /* [QueryId][batch.selectResetList][대출상태전송 API] */
L1.신청번호
FROM 원장테이블 L1
INNER JOIN 혁신원장테이블 F2 ON L1.신청번호 = F2.혁신원장_신청번호
WHERE ... -- 조건 생략
AND ... -- 나머지 필터 조건 생략
AND ... -- 나머지 필터 조건 생략
AND FN_FIN_STATUS(....) -- 나머지 필터 조건 생략
AND TRUNC(NVL(L1.CHG_DTM, L1.CRT_DTM)) = TRUNC(SYSDATE)
AND F2.REQ_YN = 'Y'
1. 함수 변경
쿼리 성능 저하 문제를 해결하기 위해 여러 조건을 기준으로 원장 테이블과 혁신원장 테이블을 JOIN하여 데이터를 필터링하고 있는 상황에서, 주요 원인 중 하나는 FN_FIN_STATUS 함수의 사용이었습니다.
이 함수는 성능에 큰 영향을 미쳤으며, 이를 최적화해야 했습니다.
FN_FIN_STATUS 함수를 사용하는 이유는 고객사의 요청에 따른 것이었고, 현업에서 빠르게 변경할 수 있도록 하기 위함이었습니다. 고객사는 이 함수의 사용을 통해 상태 전송을 관리하고, 변경 사항을 실시간으로 반영할 수 있도록 요구했습니다. 그러나 이 함수가 쿼리 내에서 여러 번 호출되면서 성능 문제를 야기한 것을 확인하고, 이를 개선하기 위해 몇 가지 최적화 작업을 진행했습니다.
아래는 기존 FN_FIN_STATUS 함수입니다.
create or replace FUNCTION "FN_FIN_STATUS" (
p_insp_no IN VARCHAR2
) return VARCHAR2
IS
V_RETURN VARCHAR2(30) := ''; -- 반환 값
V_AD_NO VARCHAR2(10) := ''; -- 광고 번호
V_AGENT_ID VARCHAR2(10) := ''; -- 기관 번호
V_LAST_SEND_STATUS VARCHAR2(30); -- 마지막 전송 상태
V_CNT INT := 0; -- 레코드 개수
BEGIN
-- 신청 번호에 대한 마지막 전송 상태와 기관 정보를 가져오는 부분
BEGIN
SELECT F2.LAST_SEND_STATUS,
F2.AGENT_ID,
F1.AD_NO
INTO V_LAST_SEND_STATUS,
V_AGENT_ID,
V_AD_NO
FROM FTC002TM F2
INNER JOIN FTC001TM F1 ON F2.INSP_NO = F1.INSP_NO
WHERE SUB_INSP_NO = p_insp_no
AND F2.REQ_YN = 'Y';
END;
...
...
-- 각 기관별 상태 코드 처리
IF V_AGENT_ID IN ('....') THEN
BEGIN
SELECT
CASE
CASE
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
ELSE '...'
END
INTO V_RETURN
FROM 원장테이블 L1
WHERE INSP_NO = p_insp_no;
END;
END IF;
IF V_AGENT_ID IN ('....') THEN
BEGIN
SELECT
CASE
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
ELSE '...'
END
INTO V_RETURN
FROM 원장테이블 L1
WHERE INSP_NO = p_insp_no;
END;
END IF;
RETURN V_RETURN;
END;
중복된 쿼리 (Repeated Queries):
- 함수 내에서 V_AGENT_ID 값에 따라 두 개의 거의 동일한 SELECT 쿼리가 반복적으로 실행되고 있습니다. 이는 중복 코드로, 지속적으로 대량의 건들을 조회할때마다 반복적으로 실행되고 있었습니다.
불필요한 BEGIN/END (Unnecessary BEGIN/END):
- BEGIN과 END가 불필요하게 사용된 부분이 있습니다. SELECT INTO 구문은 별도의 BEGIN/END 블록이 없어도 충분히 실행 가능합니다.
이러한 함수를 변경하고자 하였습니다. 굳이 조회를 하지 않아도 되는 부분이란 것을 확인한 후
파라미터를 기반으로 상태를 계산하는 방법으로 변경을 결정하였습니다. 매번 데이터베이스 조회를 하지 않고, 파라미터를 기반으로 필요한 값을 계산하여 반환하면, 데이터베이스 I/O를 줄이고 성능을 개선할 수 있습니다
아래와 같이 함수 내에서 파라미터를 기반으로 상태를 계산하는 로직을 구현하였습니다.
CREATE OR REPLACE FUNCTION FN_FIN_STATUS (
p_insp_no IN VARCHAR2, -- 신청번호
p_LAST_SEND_STATUS IN VARCHAR2,
P_AD_NO IN VARCHAR2,
P_AGENT_ID IN VARCHAR2,
P_STB_CD IN VARCHAR2,
P_REJCT_CD IN VARCHAR2
) RETURN VARCHAR2 IS
V_RETURN VARCHAR2(30) := '';
V_AD_NO VARCHAR2(10) := P_AD_NO;
V_AGENT_ID VARCHAR2(10) := P_AGENT_ID;
V_LAST_SEND_STATUS VARCHAR2(30) := p_LAST_SEND_STATUS;
V_STB_CD VARCHAR2(4) := P_STB_CD;
V_REJCT_CD VARCHAR2(10) := P_REJCT_CD;
BEGIN
-- 파라미터로 상태 계산 처리
SELECT CASE
WHEN V_AGENT_ID = '....' THEN
CASE
CASE
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
ELSE '...'
END
WHEN V_AGENT_ID = '....' THEN
CASE
CASE
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
ELSE '...'
END
WHEN V_AGENT_ID = '....' THEN
CASE
CASE
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
WHEN L1.STB_CD IN ('...') THEN '...'
ELSE '...'
END
ELSE ''
END INTO V_RETURN FROM DUAL;
RETURN V_RETURN;
END;
2. 쿼리 변경
운영 환경에서는 2분마다 한번씩 배치가 돌기때문에 전체 조회를 하는 것은 비효율적이라고 판단하였습니다.
협의 후 최근 1시간 내에 변경된 데이터만 조회를 하기로 했습니다.
AND NVL(L1.CHG_DTM, L1.CRT_DTM) >= TO_CHAR(SYSDATE - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS')
발송되지 않는 건에 관하여 협의 후 따로 일괄 발송 진행을 하였습니다.
시간 범위를 1시간으로 좁히는 방식 추가
SELECT /* [QueryId][batch.selectResetList][대출상태전송 API] */
L1.신청번호
FROM 원장테이블 L1
INNER JOIN 혁신원장테이블 F2 ON L1.신청번호 = F2.혁신원장_신청번호
WHERE ... -- 조건 생략
AND ... -- 나머지 필터 조건 생략
AND ... -- 나머지 필터 조건 생략
AND FN_FIN_STATUS(....) -- 나머지 필터 조건 생략
AND NVL(L1.CHG_DTM, L1.CRT_DTM) >= TO_CHAR(SYSDATE - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS')
AND F2.REQ_YN = 'Y'
이번 글에서는 배치 작업 중 발생한 쿼리 성능 저하 및 조회 멈춤 현상에 대한 원인 분석과 해결 방안을 공유하였습니다.
주요 문제는 FN_FIN_STATUS 함수에서의 중복된 데이터베이스 조회와 불필요한 로직이 성능을 저하시켰다는 점이었습니다.
이를 개선하기 위해, 함수 내에서 파라미터 기반으로 상태를 계산하는 방식으로 변경하여 데이터베이스 I/O를 줄이고 성능을 대폭 향상시킬 수 있었습니다. 또한, 배치 작업에서 전체 조회 대신 최근 1시간 이내의 데이터만 조회하는 방식으로 쿼리를 최적화하여 성능을 더욱 개선했습니다.
이러한 방식을 통해서 운영환경에서 평균 3분 ~ 7분, 트래픽이 증가함에 따라 조회 멈춤 현상이 발생한 것을
1.112~1.116 초 까지 단축되는 성능 최적화를 진행하였습니다.
이러한 최적화 작업을 통해, 운영 환경에서의 배치 작업이 원활히 수행되도록 하였습니다.
