데이터베이스 질문과 답변
오라클 쿼리작성중인데 너무 느린 쿼리가 있습니다.
그런데 좀 이해가 안되는 상황이 발생..
WITH eqipInfo AS (
SELECT a.equip_id,
a.wkor_id,
b.item_no,
a.comp_no,
b.sub_mng_id,
b.eqctg_id,
b.old_eq_no,
b.description ,
b.eqctg_type
FROM TAWOEQUIP a, TAEQUIPMENT b
WHERE a.comp_no = b.comp_no
AND a.equip_id = b.equip_id )
SELECT
(SELECT ''
FROM eqipInfo a
WHERE a.wkor_id = x.wkor_id
AND a.comp_no = x.comp_no ) AS equipDesc,
(SELECT TO_CHAR(WM_CONCAT(c.description))
FROM eqipInfo b, TAEQCTG c
WHERE b.comp_no = c.comp_no
AND b.equip_id = b.equip_id
AND b.eqctg_id = c.eqctg_id
AND b.wkor_id = x.wkor_id
AND b.comp_no = x.comp_no
GROUP BY b.comp_no, b.wkor_id ) AS eqCtgDesc
FROM TAWORKORDER x
WHERE 1=1
AND x.wkor_date = '20170223'
AND x.comp_no = '160'
AND x.wo_status = 'P'
위의 쿼리는 실행시 1초만에 완료..
WITH eqipInfo AS (
SELECT a.equip_id,
a.wkor_id,
b.item_no,
a.comp_no,
b.sub_mng_id,
b.eqctg_id,
b.old_eq_no,
b.description ,
b.eqctg_type
FROM TAWOEQUIP a, TAEQUIPMENT b
WHERE a.comp_no = b.comp_no
AND a.equip_id = b.equip_id )
SELECT
(SELECT TO_CHAR(WM_CONCAT(c.description))
FROM eqipInfo b, TAEQCTG c
WHERE b.comp_no = c.comp_no
AND b.equip_id = b.equip_id
AND b.eqctg_id = c.eqctg_id
AND b.wkor_id = x.wkor_id
AND b.comp_no = x.comp_no
GROUP BY b.comp_no, b.wkor_id ) AS eqCtgDesc
FROM TAWORKORDER x
WHERE 1=1
AND x.wkor_date = '20170223'
AND x.comp_no = '160'
AND x.wo_status = 'P'
아래의 쿼리는 17초 걸립니다.
차이점은 컬럼에
(SELECT ''
FROM eqipInfo a
WHERE a.wkor_id = x.wkor_id
AND a.comp_no = x.comp_no ) AS equipDesc,
이걸 조회 해주면 빠르고 안해주면 17초나 걸리는데요...
도대체 왜 이런 현상이 발생하는것이며.. 저 필드는 사실 조회할필요가 없는데 어떻게 제외하고 최적화 (2번쨰쿼리) 로 빠르게 실행시킬수 있을까요.
WITH문은 INLINE, MATERIALIZE 두 가지 형태로 운용될 수 있습니다. INLINE의 경우 단순히 반복되는 쿼리를 WITH문으로 빼내어 가독성과 관리성을 높여주는 목적이 크구요.
MATERIALIZE 의 경우에는 가상의 공간에 데이터를 미리 테이블처럼 만들어놓아 여러번 쿼리가 반복되었을 때 결과를 재사용하기 위한 목적이 큽니다.
WITH문 SELECT 절에 /*+INLILNE*/ 이나 /*+MATERIALIZE */ 힌트를 통해 지정이 가능하구요. 지정을 안 하게 되면 옵티마이저 판단에 따라 선택하게 됩니다.
저도 아직 공부 중이라 확실하지 않지만 위의 경우에 INLINE의 형태로 풀린 것으로 보이구요. 굵은 글씨 아래 서브쿼리에 EQIPINFO와 TAEQCTG를 조인하고 GROUP BY 하게 되는데
이것이 INLINE으로 풀리면서 효율적인 조인이 일어나지 못한 것으로 보입니다. 제 추측으로는 X의 COMP_NO, WKOR_ID를 받기 전에 GROUP BY 로 결과부터 만들고
나중에 COMP_NO로 WKOR_ID로 필터링하는 게 아닌가 하는 생각이 드네요. 굵은 글씨 서브쿼리에서 COMP_NO, WKOR_ID를 먼저 받아서 결과셋을 만들어 놓게 함으로써
뒷쪽 조인이 풀릴 때 해당 결과를 반영해서 실행계획이 바뀌는 것이 아닌가.. 하는 생각이 듭니다. 물론 저도 공부중이기에 추측일 뿐 정확하지는 않습니다.
만약 WITH문의 결과셋이 크지 않다면(결과 건수가 많다면 오히려 비효율적이겠죠.)
WITH eqipInfo AS (
SELECT /*+MATERIALIZE */ a.equip_id,
~~
의 형태로 변경해보셔도 비슷한 성능이 나올 것 같네요.
감사합니다. 말씀하신대로 처리하니 정말 빠르게 쿼리가 도네요.
말씀하신내용 정리해보면 힌트를 주지않는경우 inline 혹은 materialie 를 옵티마이저가 알아서 선택하게되는데
지금은 옵티마이저가 inline으로 plan 되어서 느리다는 맥락인가요?
어떠한 차이점이 있어서 이렇게 속도차이가 크게 발생하는지 정확한 이유를 알고싶네요ㅠㅠ
네 INLINE으로 풀린 것 같구요. 굵은 글씨 아래 서브쿼리에 EQIPINFO, TAEQCTG 두개를 조인하게 되는데
해당 서브쿼리가 INLINE 성격으로 풀리면 EQIPINFO자리에 WITH문 쿼리가 들어가게 되는데 그렇게 되면
EQIPINFO와 TAEQCTG 가 조인되게 되는 것이 아니라 TAWOEQUIP, TAEQUIPMENT, TAEQCTG 세 가지 테이블이
조인을 하게 됩니다. 옵티마이저가 이때 조인을 좀 비효율적으로 한 게 아닌가 싶구요.
제가 말씀 드린 MATERIALIZE 힌트는 결과셋이 작고 재사용이 많이 될때 사용하면 좋고
굳이 INLINE의 효율이 높을 때는 사용할 필요가 없습니다. 기존 쿼리와 MATERIALIZE 힌트를 사용한
쿼리의 실행계획을 비교하여 더 좋은 쪽으로 적용하시는 것이 좋겠네요.
혹시 MATERIALIZE 힌트 자리에 NO_MERGE 힌트 주고 테스트 한번 부탁드립니다.
저도 안 해봐서 될지는 모르겠네요.
성의있는 답변 감사드립니다.
대략적으로 이해는 되는데 역시 이번 계기로 좀더 공부의 필요성을 느끼게 되네요.
감사합니다. ^^
서브쿼리 안에 이상한 조건이 하나 있네요.
- AND b.equip_id = b.equip_id
- 쓸데 없이 추가된 조건인지? 아니면 오타인건지?
- 만약 c. 을 b. 으로 잘못 적은거라면? 이거 때문에 느린 것일 수도..
핫.. 맞네요 ㅠㅠ
b.equip_id = x.equip_id 로 변경해서 조회하니 materialize 제외하고도 바로 쿼리가 나옵니다..
어이없는 실수를 해버렸네요 ㅠㅠ
감사합니다
헛! 매의 눈이시네요. 그렇다고 했을 때도 첫 번째 서브쿼리가 무슨 역할을 해서 실행계획을 변경시켜줬는지 궁금하네요.
보통 with 문의 집합을
한번만 사용하면 inline 으로 풀리고
두번 이상 사용하면 materialize 로 풀리죠.
아! 매우 단순한 이유였네요. 너무 복잡하게 생각했나봅니다ㅋㅋ
) 버튼을 클릭하여 작성 하시면 됩니다.