오라클 DB에서 첫 번째 조회는 되나 두 번째 조회는 되지 않는 문제

개발자라면 이게 무슨 말인가 싶어 클릭했을 거라고 생각한다.

 

말 그대로 쿼리를 실행하면 첫 번째는 잘 조회되나 두 번째는 조회가 되지 않는 문제가 발생했다.

 

=======================================================================

 

처음에는 와스가 한 번씩 먹통 되는 현상이 발생했다.

 

제니퍼라는 모니터링 솔루션을 통해 시스템을 모니터링하고 있었는데 이중화된 와스가 스택이 계속 쌓이다가 한 개의 와스에 60개쯤 쌓일 때 먹통 되는 현상이 발생했다.

 

전산실에 말하니 하드웨어 문제는 아닌 거 같은데 티맥스 엔지니어를 불러보라고 했다. (웹투비, 제우스 사용중)

 

그래서 티맥스 엔지니어가 방문하여 웹서버와 와스는 아무 이상 없는 거 같으니 DB 엔지니어를 불러보라고 돌아갔다.

 

혹시 연도별 파티션이 생성되면서 인덱스가 제대로 생성되지 않은 문제인가 싶어서 오라클 DB 엔지니어에게 상황을 알려주고 점검 좀 해달라고 했으나 아무 이상 없다고 최종 답변을 받았다.

 

잘 되던 게 왜 말썽일까?

 

이젠 믿을 구석이 없다, 일을 해야 된다.

 

제니퍼를 통해 문제가 되는 쿼리를 찾았는데 대시보드에 있는 간단한 쿼리였다.

 

거래내역마스터, 거래내역상세, 거래내역입출고관계 3개 테이블을 조인하여 조회하는 쿼리인데 구조는 아래와 같다.

 

select sum(c.무게), sum(c.수량)
from 거래마스터 a, 거래입출고관계 b, 거래상세 c
where b.삭제여부 = 'N'
and a.거래날짜 = b.거래날짜
and a.사업장번호 = b.사업장번호
and a.거래순번 = b.거래순번
-- 그외 날짜기간과 a와 c를 조인하는 조건들

 

특정 기간 거래마스터 데이터 중 거래입출고관계에도 있는 데이터의 무게와 수량을 조회하는 쿼리다.

 

처음엔 인덱스 문제인가 싶어 확인해 보았지만 실행계획을 확인해도 문제가 없었다.

 

두 번째론 이 쿼리가 40초 가량 걸리는 쿼리라서 처음에만 인덱스를 제대로 타고 두 번째 실행할 때 첫 번째의 실행계획이 너무 느려 오라클 자체에서 다른 실행계획을 통해 조회한게 아닌가 의심되어 옵티마이저를 세팅을 하고 조회해 보았지만 여전히 첫 번째에만 조회가 되고 두 번째 조회하면 무한 조회가 되는 현상이 발생했다.

 

여기서 첫 번째만 조회되고 두 번째에는 조회가 안되는데 어떻게 계속 조회해 볼 수 있냐 물어볼 텐데 5분 정도 기다리면 다시 조회가 된다.

 

그래서 세 번째로 처음에 조회할 때 만들어진 캐시가 문제가 있어서 두 번째 조회 때 문제가 생기는 게 아닐까 싶어 옵티마이저의 노 캐시를 이용하여 해보았지만 마찬가지였다.

 

네 번째로는 DB서버와 세션이 끊기는게 아닌가 싶어 DB서버에 직접 붙어서 콘솔에서 쿼리를 날려보았지만 동일한 현상이 발생했다.

 

시간이 더 걸리면 안되니 근본적인 문제는 나중에 찾고 일단 해결먼저 하는게 좋을거 같았다.

 

다섯 번째로 문제가 되는 거래입출고관계 테이블을 서브쿼리를 빼버렸다.

 

select sum(
		(casw when 
                (select count(*) from 거래입출고관계 b 
                where b.삭제여부 = 'N' and a.거래날짜 = b.거래날짜
                and a.사업장번호 = b.사업장번호 and a.거래순번 = b.거래순번
                ) <= 1
            then c.무게
            else 0
        end)) as wt,
        sum(
		(casw when 
                (select count(*) from 거래입출고관계 b 
                where b.삭제여부 = 'N' and a.거래날짜 = b.거래날짜
                and a.사업장번호 = b.사업장번호 and a.거래순번 = b.거래순번
                ) <= 1
            then c.수량
            else 0
        end)) as ct,
from 거래마스터 a, 거래상세 c
where 1=1
-- 날짜기간과 a와 c를 조인하는 조건들

 

보라 얼마나 무자비한 쿼리인가 결과는? 대성공!

 

무자비하게 parallel(4)까지 추가하여 10초 이내로 조회되는 쿼리로 완성하였다. (기존 40초)

 

처음 조회 시 10초 두 번째 부터는 캐시로 인해 1초 이내 조회 된다.

 

이렇게 마무리했으나 시간적 여유가 된다면 원인을 찾아 올리도록 하겠다.

 

곧 다른 시스템으로 이동하기에 올릴 수 없을 수도 있다.

 

힌트를 댓글로 달아주시면 좀 더 쉽게 찾을지도?