[Oracle]서브쿼리와 IN 문으로 인한 성능 저하
by EastGlow
사건의 발단
얼마 전까지 한창 날 괴롭히던 문제가 하나 있었다. 유지보수를 맡고 있는 시스템이 하나 있는데 언제부턴가 자꾸 Java Application 단과 DB 사이의 커넥션이 날아가버리는 현상이 발생하는 것이었다. 몇 년 동안 잘 운영되던 시스템이었고 최근 몇 달은 특별히 작업한 부분도 없었다.
대충 로그를 살펴보니 아래와 같은 로그가 계속 보였다.
java.sql.SQLRecoverableException: No more data to read from socket
우선은 해당 로그를 토대로 구글링을 해보니 원인에 관해서 몇 가지 추측들을 찾을 수 있었다.
무엇이 원인일까?
- Oracle 버전이 11.2 이하라면 11g 버전으로 업그레이드 하면 버그가 픽스될 것이다. => 시스템의 DB는 이미 11.2 버전이었다.
- Oracle DB 내부의 설정 중
_optim_peek_user_binds = false항목을 설정 => 회사에 할당된 DB 계정으론 권한이 없었다. - ojdbc 드라이버 버전을 올려보아라. => ojdbc4 버전을 쓰고 있길래 5~6 버전을 올려보았으나 똑같았다.
- JDK 버전이 JEUS 서버 버전과 안 맞아서 그럴 수 있다. => 해당 시스템은 JEUS5 버전을 사용하고 있었고 JEUS5는 JDK 1.5까지만 지원한다고 한다. 그런데 현재 시스템의 소스들은 JDK 1.7로 컴파일 된 소스들이었다. 그래서 JDK 1.5로 재컴파일하여 올렸다. 처음 며칠 간은 괜찮다가 다시 동일한 오류가 발생하였다.
- DB 쪽 보안툴이나 방화벽 문제가 있을 수 있다고 하나 내가 확인할 방법은 없었다.
그래서?
이것저것 다 해봐도 계속 그러길래 DBMS툴을 통해 오류가 나는 메뉴의 쿼리를 직접 실행해보았다. 실행계획도 확인하고 싶었지만 DB 계정에 권한이 없었다…
여튼 쿼리를 직접 실행해보니 DBMS툴에서도 같은 소켓 에러가 나는 것이다. 심지어 그 쿼리를 한번 돌리고 소켓 에러가 나면 그 DB에 재접속 하기 전까진 다른 쿼리나 테이블 조회 등 모든 것이 안 되었다. 그냥 연결이 끊겨버린 것이다.
그래서 일단 그 쿼리를 파보기로 했다. 그러다 크게 세가지의 문제점을 찾았는데,
- 조건절에 서브쿼리가 꽤나 무거운 녀석이 들어가있다.
- SELECT 절에 함수가 많다.
- 심지어 그 함수의 쿼리가 무겁다.
일단 하나씩 문제점들을 해결해보기로 했다.
SELECT 절의 함수 수정
일단 조금 세부적인(?) 것들부터 손보기로 했다. SELECT절에 함수가 5개 정도 쓰이고 있었는데 그 중 1개가 문제가 있었다. 실제 시스템에 쓰이는 쿼리이기 때문에 올리진 못하고 내가 어느정도 재구성한 쿼리를 대략적으로 보자면,
SELECT A.COL1, A.COL2, A.COL3, B.COL1, B.COL2, B.COL3
FROM TABLE1 A JOIN TABLE2 B
ON A.PKID = B.PKID
WHERE A.COL1 IN (SELECT COL1
FROM TABLE3
WHERE COL2 = 'B')
위 쿼리보다 5~6배는 더 긴 쿼리인데 그 긴 쿼리 중 문제가 되는 부분만 살짝 떼와봤다. 그냥 보면 간단한 쿼리인데 두 테이블을 조인시키고 또다른 테이블에서 COL1이라는 값을 가져와서 A테이블의 COL1과 IN으로 비교하여 조건을 걸어주도록 해주었다.
희한하게 이 쿼리만 들어가면 쿼리 조회시간이 5~6초씩 걸리길래 TABLE1, TABLE2, TABLE3의 데이터 건수를 각각 체크해보니 몇 십만 건이나 되는 테이블들이었다. 심지어 이 테이블 3개 다 다른 DB에서 다이렉트로 받아다 쓰는 VIEW라서 일반 테이블에 비해 속도가 느린 것 같았다.
이런저런 문제점들이 있었지만 제일 문제가 되는건 조건절에 쓴 IN 절이었다. IN은 ROW를 체크만하는 EXISTS랑 다르게 ROW의 데이터를 모두 확인하기 때문에 목적에 따라서는 EXISTS에 비해 훨씬 성능이 떨어질 수 있다는 점이다. 위 쿼리에선 굳이 IN을 안 쓰고도 단순히 TABLE3의 COL1이라는 값이 A테이블의 COL1과 동일한 값들만 체크하면 되는 거라 EXISTS로도 대체 가능하였다.
SELECT A.COL1, A.COL2, A.COL3, B.COL1, B.COL2, B.COL3
FROM TABLE1 A JOIN TABLE2 B
ON A.PKID = B.PKID
WHERE EXISTS (SELECT 1
FROM TABLE3 C
WHERE C.COL2 = 'B'
AND A.COL1 = C.COL1)
위와 같이 수정하였더니 5~6초에 걸리던 시간이 0.몇 초대로 줄어들었다. 이렇게 함수 수정은 완료되었다.
조건절의 서브쿼리 수정
문제가 되는 쿼리의 조건절에 서브쿼리가 하나 들어가있었는데 다른 특정 테이블에서 가져온 년도값과 현재년도 값을 비교하는 절이었다.
SELECT A.COL1, A.COL2, A.COL3
FROM TABLE1 A
WHERE (SELECT B.YEAR FROM TABLE2 B WHERE B.COL1 = A.COL1) = '2018'
뭐 대략 이런 쿼리였다. A테이블의 정보들을 뽑는데 조건이 하나 걸린다. 현재년도(2018)와 같아야 하는게 있는데 B테이블에서 A테이블의 COL1과 같은 것들을 뽑아서 YEAR 값을 비교하는… 내가 설명하면서도 무엇을 설명하려는 지 잘 모르는 조건절이었다. 저 조건만 들어가면 속도가 엄청 느려졌다. 거의 5~7초 정도 걸리는 듯 하였다. 그래서 이 쿼리 역시 수정을 하였는데,
SELECT A.COL1, A.COL2, A.COL3
FROM TABLE1 A JOIN TABLE2 B
ON A.COL1 = B.COL1 AND B.YEAR = '2018'
위와 같이 서브쿼리 절을 조인절로 바꾸어 수정하였다. 그랬더니 속도는 0.몇 초대로 줄어드는… 아주 좋은 광경을 볼 수 있었다.
결론
그래서 결론을 내리자면,
- 엄-청 많은 데이터를 비교하는 부분은 IN절보단 EXISTS를 이용할 수 있다면 되도록 EXISTS로 대체하자.
- 조건절에는 가공될 수 있는(?), 바뀔 수 있는 서브쿼리 절은 넣으면 좋지 않다고 한다. 고정된 컬럼과 비교할 수 있는 조건절을 넣어주도록 하자.
일단 저렇게 수정을 하고 나니 쿼리 조회 속도도 잘 나오고 시스템에 적용을 하니 오류도 생기지 않았다. 실제로 쿼리가 무거워서 생겼던 오류인지는 확실하지 않지만 며칠 간 모니터링을 하며 다시 오류가 생기지 않는 지 지켜봐야할 것 같다.
쿼리 튜닝이라고도 부르기 민망할 정도의 초보적인 수정이었지만 쿼리 짜는 것에 대해 다시 한 번 생각해볼 수 있던 좋은 기회였다.
참고로 IN과 EXISTS는 대체하여 쓸 수 있지만 NOT IN과 NOT EXISTS는 NULL 처리 방식이 다르기 때문에 조심해서 써야한다고 한다.
참고: https://m.blog.naver.com/mk1126sj/220985926185
예시로 든 쿼리는 제 기억을 끄집어내어 재구상한 쿼리이고 실제 시스템에 쓰이는 쿼리를 그대로 옮겨올 수 없기에 대략적으로 흐름만 알아볼 수 있도록 하였습니다. 문법상으로 혹시라도 틀린 부분이 있을 수 있으니 그런 부분에 대한 피드백은 언제나 환영합니다.
Subscribe via RSS