최근 운영 환경에서 특정 조회 기능이 갑자기 DB 락이 걸린 것처럼 느려지는 현상이 발생했다.
증상은 다음과 같았다.
• 같은 쿼리를 로컬 PC에서 SQL Developer로 실행하면 수 ms 만에 응답
• 동일한 쿼리를 WAS(MyBatis)를 통해 실행하면 수 초~수십 초 이상 걸림
• 심한 경우 DB 전체 부하가 증가하면서 다른 쿼리까지 지연 발생
특히 이상한 점은, 예전에도 같은 쿼리 구조에서 바인드 변수를 썼는데
최근 쿼리가 복잡해진 뒤부터 현상이 심해졌다는 것이다.
결과부터 말하자면 잘못된 바인드 변수 스니핑에 의해 풀스캔 + 오버헤드가 발생하여 생긴 현상이었다.
아래부터 예시를 들어 설명하겠다.
바인드 변수(매개변수) 스니핑 이란
Oracle 옵티마이저는 첫 번째 바인드 변수 값을 기반으로 실행 계획을 세우고,
해당 계획을 캐싱해서 이후 동일한 SQL ID에 재사용한다.
SELECT *
FROM USERS
WHERE
<choose>
<when test='type == "A"'>
USER_ID = #{value}
</when>
<when test='type == "B"'>
USER_NAME = #{value}
</when>USER_ID에는 인덱스가 걸려있고, USER_NAME에는 인덱스가 안걸려있다고 가정함.
이렇게 같은 SQL에 있는 경우 ID는 하나로 유지되며 실행계획, 캐싱또한 공유함.
•첫 번째 실행에서 B 타입으로 실행하여 풀스캔
•이후 A타입으로 실행 -> 인덱스 타야되지만 바인드변수 스니핑 으로 인해 풀스캔
•결과적으로 불필요한 I/O가 폭증 → 성능 저하
왜 최근들어 심해졌는지
문제는 바인드 스니핑에도 있었지만, 쿼리가 점점 무거워진 것과 관련 있다.
여러개의 조인테이블 추가, 서브쿼리 추가되어 아래와 같은 문제가 발생하였다.
•인덱스를 쓰면 여전히 빠름 → 문제 없음
•하지만 바인드 스니핑 때문에 풀스캔 계획 고정 → 연결된 모든 테이블 다 조회함
•TEMP 공간 폭발, CPU 과다 사용, DB 전체 부하 발생 → 락 걸린 듯한 현상 체감
핵심 포인트
•예전엔 단일 테이블 풀스캔 → 부하가 크지 않았음
•쿼리가 복잡해질수록 풀스캔 1회가 가져오는 오버헤드가 기하급수적으로 증가
•바인드 스니핑 때문에 잘못된 계획이 고정되면 → DB 전체 성능 저하
해결방안 : 인덱스 힌트 적용
SELECT
<when test='type == "A"'>
/*+ INDEX(USERS A인덱스명)*/
</when>
<when test='type == "B"'>
/*+ INDEX(USERS B인덱스명)*/
</when>
*
FROM USERS
WHERE
<choose>
<when test='type == "A"'>
USER_ID = #{value}
</when>
<when test='type == "B"'>
USER_NAME = #{value}
</when>•옵티마이저에게 인덱스를 강제 사용하게 유도
•단, 데이터 편중이 심한 경우 오히려 느려질 수도 있어 → 신중히 적용
여러가지 해결방안이 있지만 가장 간단하게 인덱스 힌트를 통해 해결하였다.
결론
바인드 변수 스니핑 문제는 예전부터 있었지만,
쿼리가 복잡해질수록 잘못된 실행 계획 하나가 시스템 전체를 마비시킬 수 있다.
WAS 환경에서 바인드 변수를 사용할 땐 항상 실행 계획 캐싱을 주의 깊게 살펴야 한다.