오라클에서 WITH가 성능에 미치는 영향

Question

오라클 통계쿼리를 짜다보면 WITH 이름 AS ( 쿼리 ) 해서 임시테이블을 만들어 쓰는데 실행계획을 보면 일반 서브쿼리로 짜서 돌리는 것보다 메모리로드가 약간더 많이 걸리는거같습니다. 유지보수할때 쿼리 이해도가 좋아지는거같아서 필요할 때만 쓰는데 WITH로 임시테이브을 만들어 쓰는건 좋지 못한 습관일까요? 성능테스트를 하다 쿼리에서 WITH를 제거해달라는 요구사항을 받았는데....성능에 얼마나 영향을 주는지 잘 모르겠습니다. 고수분들 좀 매몰차게 답변좀 달아주시면 감사하겠습니다 ㅎㅎㅎ

larcyuki · Answer

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

peopleWare · Answer

통계쿼리나 튜닝할때, 많이 사용하긴 하고,쿼리량을 줄여주는 장점도 있고.. 캐바케라서, 좋지 못한 습관이다라고 딱 단정지울 수 없습니다. 근데, 말씀하신 내용만 가지고는...판단하기 좀 그러네요; 서브쿼리->with변환하는 과정에서 잘못해서 그럴수도 있으니..trace떠서 보여주세요. 쿼리,실행계획,통계를 봐야 문제점을 진단할 수 있습니다.

larcyuki · Answer

좀더 쉬운 이해를 위해... with a as (select * from dept) select * from a, emp b where a.dept_no = b.dept_no union all select * from a, emp b emp2 b where a.dept_no = b.dept_no select * from (select * from dept) a, emp b where a.dept_no = b.dept_no and b.dept_no = 10 위와 같은 두개의 쿼리가 있을 경우 전자의 경우 서브쿼리는 무조건 풀스캔이 필요한 상황입니다. 때문에 WITH절로 빼더라도 문제가 없고, 만약 UNION등을 통해 다시 활용 될 수 있다면 오히려 성능에 이득이 될 수도 있죠. 후자의 경우 서브쿼리 일 경우는 b의 dept_no=10이고 a.dept_no=b.dept_no라는 조건에 따라 옵티마이저가 a.dept_no=10이라고 추론하고, 조건절 pushing에 따라 서브 쿼리를 (select * from dept where dept_no=10)이라는 쿼리로 변환을 할 수가 있습니다. 이 쿼리를 재사용할 일도 없다면 후자는 서브쿼리를 사용하는 것이 더 효율적일 수 있습니다. 구지 WITH문 뿐 아니라 오라클에서 사용되는 기능은 상황에 맞게 잘 사용하는 것이 중요합니다 ``;

ZARD · Answer

맹호님,고도님 답변을 보고선 WITH제거 명령을 하사하신 그분을 설득을 시켜 일을 없앴습니다 ㅎㅎㅎㅎ WITH와 조인을 별로 안좋아하는 사람이라.. 힘드네요 ㅎㅎㅎㅎㅎ

오라클에서 WITH가 성능에 미치는 영향

4개의 답변

Q&A 목록