같은 쿼리/같은(표기상) 오라클 버전인데 테스트 서버 환경에서는 정상, 운영db(운영성 데이터 대량 보유) 환경에서는 특정 쿼리만 1분 이상 지연 또는 Timeout(500에러가 발생). 원인은 파서/옵티마이저 동작(실행계획) 차이와 데이터양이 운영서버 환경에서 더 많았고, 응급 해결은 인덱스 힌트 강제 사용이었다.
🧩 문제 상황1
- 테스트 서버: 동일 쿼리 즉시 응답(정상)
- 운영 서버: 동일 쿼리만 심각하게 느려지거나 타임아웃
- 핵심 단서: WITH /* 이건 그냥 텍스트임 */ TEST AS (...) SELECT ... FROM TEST; 형태로 WITH 바로 뒤에 주석이 있었을 때
- 테스트 서버: 정상 실행
- 운영 서버: 실행 불가/오류 양상
- 주석을 제거하면 운영서버에서도 실행은 되지만 매우 느림
1-1. 1차 가설과 검증
- <![CDATA[ ... ]]> 안의 /* ... */ 는 XML에선 텍스트일 뿐, DB에선 주석으로 파싱됨
- 운영서버에서만 문제가 난 이유는 데이터량/파서/옵티마이저의 미묘한 동작 차이 가능성
- 같은 버전 표기라도 패치 레벨, 파라미터, 세션 환경에 따라 파싱/계획 선택이 달라질 수 있음
- 어떤 운영서버 (구버전 오라클) 환경에서는 WITH 바로 뒤의 /* ... */ 때문에 파서가 혼동을 일으킬 수 있음.
-> 공공기관 프로젝트였기때문에 오라클이 한자리수 버전임. (현재 23c버전까지 출시됨.) - 버전/환경 확인
- SELECT * FROM v$version; -- 파라미터 확인 SHOW PARAMETER optimizer_features_enable; -- 또는 SELECT name, value FROM v$parameter WHERE name LIKE '%optimizer%';
- 실행계획 확인(두 환경 비교)
- 테스트서버: 인덱스 활용 + 짧은 경로
- 운영서버: 풀스캔/비효율 조인 경향 → 지연·타임아웃
- 데이터 분포(Data skew): 같은 쿼리라도 컬럼 값 분포가 테스트/로컬과 운영에서 크게 다르면 옵티마이저가 다른 계획을 고름.
- 통계(Statistics) 불일치: 운영의 통계가 오래되어 옵티마이저가 잘못된 비용 추정 → 잘못된 실행계획 선택.
- 옵티마이저 / 파서 차이:
- 같은 "버전 표기"라도 패치 수준(SP), optimizer_features_enable, compatible 같은 파라미터나 세션 환경에 따라 파서/옵티마이저 동작이 달라짐.
- WITH 바로 뒤에 일반 주석(/* ... */)이 놓이면 구버전 파서에서 오류/해석 문제를 일으키는 경우가 있음 → 테스트 서버는 패치돼서 통과, 운영서는 문제 발생.
더보기
더보기
🔹 1. SP (Service Pack, Patch Set)
- 오라클 DB는 같은 버전이라도 계속 버그 패치, 보안 업데이트, 성능 개선이 이루어짐.
- 예를 들어 같은 Oracle 11g라도:
- 11.2.0.3 → 버그가 있는 상태
- 11.2.0.4 → 일부 버그(예: 파서 문제) 패치된 상태
- 그래서 테스에는 패치가 적용돼 있고, 운에는 적용이 안 돼 있다면 같은 버전 표시라도 동작이 달라질 수 있음.
- 주석 문제도 사실 오라클 패치 릴리스 노트에 “SQL 파서에서 주석 처리 문제 수정됨” 같은 게 기록된 적 있음.
🔹 2. NLS (National Language Support)
- 오라클의 다국어/지역화 설정 시스템.
- 예: 날짜, 숫자, 언어, 문자셋 관련 설정.
- 대표 파라미터:
- NLS_LANGUAGE → 에러 메시지, 달 이름 언어
- NLS_TERRITORY → 날짜/숫자 기본 포맷
- NLS_CHARACTERSET → 데이터 저장 시 문자 인코딩 (UTF8, AL32UTF8 등)
- 쿼리 실행 자체에는 직접적 영향이 없지만, 주석이나 줄바꿈, 특수문자 처리에 영향을 줄 수 있다.
- 예: 한쪽 DB는 UTF8, 다른 쪽은 EUC-KR → CDATA 안 주석 처리 시 깨짐 → 파서가 문법 오류로 오해
🔹 3. 옵티마이저 (Optimizer)
- 오라클의 SQL 실행계획을 결정하는 두뇌.
- 하나의 SQL을 여러 방식으로 실행할 수 있는데, 옵티마이저가 그중 최적이라고 판단한 계획을 선택.
- 종류:
- 규칙 기반 옵티마이저(RBO, Rule-Based): 오래된 방식, 규칙 우선
- 비용 기반 옵티마이저(CBO, Cost-Based): 현재 기본, 통계정보 기반
- 관련 파라미터:
- optimizer_features_enable → 특정 버전의 옵티마이저 동작 강제
- optimizer_mode → FIRST_ROWS, ALL_ROWS 등 튜닝 목표
- 힌트(/*+ ... */)는 사실 옵티마이저에게 “이 계획대로 실행해라”라고 지시하는 명령.
- 구버전/옵티마이저 모드 차이 때문에 테스트 서버 와 운영서버가 동일 쿼리라도 다르게 동작할 수 있다.
✅ 정리
- SP (Service Pack): 같은 버전이라도 버그 수정 여부 다름 → 테스트서버는 패치, 운영서버는 미적용 가능.
- NLS (National Language Support): 문자셋/언어 설정 → 주석·특수문자 인식 차이 발생 가능.
- 옵티마이저 (Optimizer): 실행계획 결정 엔진 → 버전/설정에 따라 같은 쿼리도 다르게 실행됨.
1-2. 중간 조치 (주석 위치 수정)
- WITH 키워드 바로 뒤 주석 제거/이동
- 안전한 패턴들:
-
-- 1) WITH와 주석 사이 줄바꿈 WITH /* 이건 안전한 주석 */ TEST AS (...) -- 2) CTE 이름 뒤에 주석 WITH TEST /* 설명 */ AS ( ... ) -- 3) CTE 내부에 주석 WITH TEST AS ( SELECT * -- 사용자 테이블 조회 FROM USERS )
-
- 안전한 패턴들:
- 결과: 내부망 운영까지는 실행 자체는 정상화
- 하지만 성능은 여전히 느리며 외부망 운영서버는 작동이 안되고 외부 테스트 서버, 내부 운영서버, 내부 로컬에서만 정상작동(근본 원인은 실행계획/데이터 분포/통계)
🧩 문제상황 2: 내부망에선 5분 뒤 정상, 외부망에선 타임아웃
- 내부망(운영서버 직접 접속): 5분 이상 걸리지만 결과 반환
- 외부망(운영서버 접속 - 애플리케이션/게이트웨이 경유): 동일 쿼리 타임아웃
- 해석:
- 쿼리는 DB에서 정상 수행되고 있었음(단지 매우 느림)
- 외부망 경로에는 연결/쿼리 대기 시간 제한(예: 60초, 120초)이 존재
- 결과적으로 DB는 돌고 있는데, 중간(드라이버/WAS/게이트웨이)에서 먼저 끊김
즉, 문제상황 1의 ‘느림’이 해결되지 않은 상태에서, 문제상황 2(타임아웃 조건)가 겹쳐 드러난 것.
DBeaver에서 실행계획 확인 방법: 그냥 티베로에서 쿼리 실행하면 plan확인가능... 디비버를 처음 사용하면서 여기서 또 시간을 소비했다..
🛠️ 최종 해결: 인덱스 힌트로 실행계획 강제
- 최종 해결: 인덱스 힌트로 실행계획 강제
- 운영서버 에서 느린 원인은 옵티마이저가 인덱스를 못 타고 풀스캔을 선택한 것
- 응급 처방으로 인덱스 힌트 적용:
-
SELECT /*+ INDEX(u USERS_IDX1) */ u.* FROM USERS u WHERE u.ID = 1;
효과:- 내부망 운영서버: 5분 → 짧은 시간 내 정상
- 외부망 운영서버: 타임아웃 소멸, 짧은 시간 내 정상 응답
- 추가 권장 조치(영구):
- 테이블/인덱스 통계 갱신 (DBMS_STATS) → 옵티마이저 스스로 올바른 계획을 선택하도록.
- 히스토그램 생성(특정 컬럼의 값 분포가 치우쳐 있으면).
- 테스트/운영 간 패치 레벨(SP), 옵티마이저 관련 파라미터(optimizer_features_enable, optimizer_mode) 차이 점검 및 문서화.
- 가능하면 운영에도 최신 패치 적용(운영정책에 따라 검토).
🔎 핵심 원인 분석
- 데이터 분포/규모 차이
- 테스트/로컬은 소량이라 인덱스 경로가 자연스럽게 선택
- 운영은 대용량 + 특정 값의 스큐(skew) 로 비용 추정 오차 커짐
- 통계/히스토그램 부족 또는 오래됨
- 옵티마이저가 잘못된 비용을 계산 → 풀스캔/비효율 조인 선택
- 파서/옵티마이저 환경 차이(세션/파라미터/패치)
- WITH 직후 주석 같은 구문 민감도 차이로 파싱/계획 영향
- 외부망 타임아웃 설정
- 쿼리 자체는 완료되지만, 대기 한도를 초과해 중간에서 세션 종료
✅재발 방지 체크리스트
- 실행계획 상시 확인
-
EXPLAIN PLAN FOR <쿼리>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); - 통계 최신화 + 히스토그램
-
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE', cascade=>TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE', METHOD_OPT=>'FOR COLUMNS SIZE AUTO'); - 안전한 주석 패턴 유지
- WITH 바로 뒤 주석 금지, CTE명 뒤/내부에 위치
- 외부망 타임아웃 값 점검
- JDBC socketTimeout/queryTimeout, WAS/Hikari maxLifetime/idleTimeout, 게이트웨이/프록시의 read/connect timeout 등
- 쿼리 성능 개선이 우선이지만, 업무 특성상 장시간 쿼리가 불가피하면 정책적으로 한도 조정 검토
- 힌트는 응급처방
- 장기적으로는 인덱스/쿼리 구조/통계로 자연스러운 계획 유도
- 힌트를 넣은 경우, 이유/맥락을 소스나 위키에 주석으로 남기기
📚부록: 안전한 WITH+주석 예시 / 힌트절 모음
-- A. WITH와 주석 사이 줄바꿈
WITH
/* 설명 주석 */
T AS (SELECT ... )
SELECT ... FROM T;
-- B. CTE 이름 뒤 주석
WITH T /* 설명 */ AS (SELECT ... )
SELECT ... FROM T;
-- C. CTE 내부 주석
WITH T AS (
SELECT /* 내부 주석 */ col FROM tab WHERE ...
)
SELECT ... FROM T;
- /*+ INDEX(table index_name) */ — 특정 인덱스 강제 사용 (우리 사례에서 사용)
- /*+ FULL(table) */ — 풀스캔 강제 (대량 처리/배치가 더 효율적일 때)
- /*+ USE_NL(table) */ — Nested Loop 강제 (소량-다량 조합에서)
- /*+ USE_HASH(table) */ — Hash Join 강제 (대량-대량 조인에서)
- /*+ LEADING(t1 t2) */ — 조인 순서 지정
- /*+ PARALLEL(table, n) */ — 병렬 처리
- 주의: 힌트는 임시·긴급 수단으로 사용하고, 장기적으로는 통계/인덱스/쿼리 리팩토링으로 해결하는 것이 좋음.
📌결론
- 같은 쿼리·같은 버전 표기라도, 데이터 분포·통계·옵티마이저/파서·네트워크 타임아웃 같은 환경 요소가 다르면 결과가 완전히 달라질 수 있다.
- 문제를 쪼개서 본다:
- 쿼리 자체 성능(실행계획)
- 전달 경로/환경 설정(타임아웃)
- 응급으론 힌트가 빠르지만, 근본은 통계/인덱스/쿼리 구조/정책 정비다.
💿참고 쿼리 스니펫(정리)
-- 실행계획
EXPLAIN PLAN FOR <쿼리>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 통계
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE', cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE', METHOD_OPT=>'FOR COLUMNS SIZE AUTO');
-- (응급) 인덱스 힌트
SELECT /*+ INDEX(u USERS_IDX1) */ u.* FROM USERS u WHERE u.ID = :id;
'SQL' 카테고리의 다른 글
| [SQL]날짜 함수 ORALCE, MYSQL, MS-SQL, 정리 (0) | 2025.04.12 |
|---|---|
| [sql]Procedure(프로시저) (3) | 2024.09.09 |
| [SQL] DB LOCK (0) | 2024.09.09 |
| [SQL] TRRIGER(트리거), TRANSACTION(트랜잭션) (0) | 2024.09.09 |
| [SQL]VIEW - 가상의 테이블 (0) | 2024.09.09 |