[Oracle] WITH가 성능에 미치는 영향

Q:

오라클 통계쿼리를 짜다보면 WITH 이름 AS (쿼리)해서 임시테이블을 만들어 쓰는데 실행계획을 보면 일반 서브쿼리로 짜서 돌리는 것보다 메모리로드가 약간 더 많이 걸리는거 같습니다.

유지보수 할때 쿼리 이해도가 좋아지는거 같아서 필요할 때만 쓰는데 WITH로 임시테이블을 만들어 쓰는건 좋지 못한 습관일까요?

성능테스트를 하다 쿼리에서 WITH를 제거해달라는 요구사항을 받았는데 성능에 얼마나 영향을 주는지 잘 모르겠습니다.

 

A:

WITH로 만드는건 그 쿼리내에서 임시 테이블을 생성해 버퍼캐쉬에 올린다는 건데 아마도 그 부분에서 메모리 사용량이 올라갈 거로 보이네요. 상황에 따라 다르겠지만 WITH구문을 쓸 때의 장점은 같은 쿼리문을 여러 번 서브뤄리로 사용할 시는 각각의 실행 계획이 생성되지만, WITH구문은 버퍼에서 풀스캔을 하기에 쿼리문의 내용을 재사용 시 버퍼에서 계속 가져다 쓴 장점이 있다. 서브쿼리로 한다면 쿼리 변환을 통해 옵티마이저가 더 좋은 실행 계획을 생성할 수 있지만, WITH문으로 사용하면 해당 쿼리를 버퍼에 올리는 과정에서 처리 범위를 줄이지 못한다면 단점이 될 수 있습니다. 실제 WITH구문을 이용해 튜닝을 하는 경우도 있기에 WITH구문을 쓰는건 자체가 문제가 되는 습관이라 할 수는 없겠죠. 결론적으로 말씀드리자면 WITH구문이 좋고 나쁘고라기 보다 상황에 맞게 사용해야 할 듯 하네요. 무조건 적으로 안좋게 보기 보다, WITH구문으로 충분히 범위를 줄이고, 재사용하기 좋은 쿼리라면 사용하는게 좋을 수 있습니다.

- 통계쿼리나 튜닝할때, 많이 사용하고 쿼리량을 줄여주는 장점도 있다