쿼리의 Plan 결과에 대해서 질문합니다. 0 13 2,010

by 제일 [PL/SQL] [2013.03.28 10:06:42]


Plan1.jpg (68,528Bytes)
Plan2.jpg (80,398Bytes)

1번 플랜과 2번 플랜 두가지가 존재합니다.

1번 플랜은 풀스캔을 타는데다가 플랜에서 나타나는 Cardinality Cost가 수치가 매우 높게 나타납니다.
풀스캔을 타니 당연하다고 생각했습니다.

그런데 2번 플랜은 인덱스도 제대로 타고 있고, Cardinality와 Cost가 무척 적게 나타나는 것을 알 수 있습니다.

그런데 문제는 두 쿼리의 실행결과 속도입니다. 실행결과 속도를 비교해보았는데 놀랍게도
두 쿼리 5번 정도 돌려본 결과

1번 쿼리는 수행속도가 0.8~0.9초 정도 걸리는 반면에 2번 쿼리는 2.2~3.0초 정도의 수행속도 결과를 나타내고 있습니다.
어떻게 하면 이러한 결과가 나타나는지 궁금합니다. 
1번쿼리와 2번 쿼리의 차이는 WHERE 조건에서 기간입력한것과 안한것의 차이입니다.
아래의 쿼리를 수행결과 나타난 결과입니다. 조언 부탁드립니다.


SELECT MONTHS
  , ROUND(MAX(M1_VAL) * 100 / MAX(M2_VAL),1) PERC
   FROM
(
SELECT INDEX.FC_GET_CONV_DATE('1',ADM_DTE)    MONTHS
  , COUNT(A.PT_NO)     M1_VAL
  , NULL   M2_VAL
   FROM
(
SELECT PT_NO
  , ADM_DTE
  , DECODE(C074,'1',1,0)
  + DECODE(C078,'1',1,0)
  + DECODE(C082,'1',1,0)
  + DECODE(C086,'1',1,0)
  + DECODE(C090,'1',1,0)
  + DECODE(C094,'1',1,0) CSUM
  , PK_HEMODIALYSIS_INDEX.FC_GETCYCLE_YN(PT_NO, ADM_DTE)  C_YN
   FROM OM_DF_BL_DLSI_MEVLELDT
  WHERE (( C074 = '1') OR
( C078 = '1') OR
( C082 = '1') OR
( C086 = '1') OR
( C090 = '1') OR
( C094 = '1'))
    AND C015 = '1'
) A
WHERE CSUM >= 3
   AND C_YN = 'Y'
  GROUP BY ADM_DTE
UNION ALL
SELECT INDEX.FC_GET_CONV_DATE('1',ADM_DTE) MONTHS
  , NULL     M1_VAL
  , COUNT(PT_NO)    M2_VAL
   FROM OM_DF_BL_DLSI_MEVLELDT
  WHERE C015 = '1'
    AND LAYOUT_TYPE = 'HD'
    AND FORM_ID = 'HF2013'
    AND FORM_VER = '2'
  GROUP BY ADM_DTE
)
   WHERE MONTHS >= '2012-01' AND MONTHS <= '2012-06'
   GROUP BY MONTHS
   ORDER BY MONTHS

by 이재현 [2013.03.28 10:22:34]

우선 XPLAN이나 TRACE파일을 뜨면 명확하게 확인 하실수가 있을겁니다.


by 제일 [2013.03.28 10:46:15]

제가 잘 몰라서 그러는데 XPLAN이나 TRACE가 툴을 의미하는 것인가요?


by 마농 [2013.03.28 10:47:54]

질문하신 내용과는 전혀 다른 관점에서 답변 드리겠습니다.

fc_get_conv_date 이 사용자 함수는 별 특별한 내용이 없는 함수인듯 합니다.
단순 포멧 변경 함수인듯 한데요.
즉, 사용자 함수 사용안하고, 내장함수만으로도 충분히 구현가능한 기능이라 생각되구요.
사용자 함수 사용한 값에 대해 밖에서 조건주는것보다는
원본 컬럼(ADM_DTE)을 가공없이 그대로 조건을 주는게 좋습니다.
ADM_DTE 에 저장된 데이터 형태를 알려주세요.
date 타입인지? Varchar2 타입인지?
varchar2 타입이라면? 길이와 포멧은 어떻게 되는지?

아 그리고 위에 인덱스 구성이 어떻게 되는지?


by 제일 [2013.03.28 10:56:34]

ADM_DTE는 DATA 타입입니다.


by 마농 [2013.03.28 11:21:11]
마지막에 SUM 을 안하고 MAX 를 한건 좀 이상하네요?

by 마농 [2013.03.28 11:21:22]
SELECT TO_CHAR(adm_dte, 'yyyy-mm') months
     , ROUND(
       COUNT(CASE WHEN csum >= 3 AND c_yn = 'Y' THEN pt_no END)
     / COUNT(CASE WHEN layout_type = 'HD'
                   AND form_id = 'HF2013'
                   AND form_ver = '2'
                  THEN pt_no END)
     * 100, 1) perc
  FROM (
        SELECT pt_no
             , adm_dte
             , DECODE(C074, '1', 1, 0)
             + DECODE(C078, '1', 1, 0)
             + DECODE(C082, '1', 1, 0)
             + DECODE(C086, '1', 1, 0)
             + DECODE(C090, '1', 1, 0)
             + DECODE(C094, '1', 1, 0) csum
             , pk_hemodialysis_index.fc_getcycle_yn(pt_no, adm_dte) c_yn
             , layout_type
             , form_id
             , form_ver
          FROM om_df_bl_dlsi_mevleldt
         WHERE c015 = '1'
           AND adm_dte >= TO_DATE('2012-01', 'yyyy-mm')
           AND adm_dte < ADD_MONTHS(TO_DATE('2012-06', 'yyyy-mm'), 1)
        )
 WHERE (layout_type = 'HD' AND form_id = 'HF2013' AND form_ver = '2')
    OR (csum >= 3 AND c_yn = 'Y')
 GROUP BY TO_CHAR(adm_dte, 'yyyy-mm')
 ORDER BY months
;

by 제일 [2013.03.28 11:32:46]
성능이 완벽하게 좋아졌습니다! 놀라워요!
어떠한 부분이 달라진건지 대략적으로라도 기술해주실수 있으신가요?

by 마농 [2013.03.28 11:52:03]

1. 사용자 함수 제거
  사용자 함수는 본 쿼리와 별개의 쿼리로 동작합니다.
  본 쿼리의 건수만큼의 쿼리가 반복 수행됩니다.
  성능이 안좋구요.
  이를 내장함수로 바꾸었습니다.
  내장함수는 별개 쿼리가 아닌 하나의 쿼리 안에서 동작됩니다.
2. Union All 제거
  동일 테이블 두번 읽을 것을 한번만 읽습니다.
  공통되는 조건만 Where 절에 남기고, 다른 조건은 Case 문으로 체크합니다.
3. 조건절 삽입
  인라인 뷰 안으로 기간 조건 삽입함으로 처리량을 줄입니다.
  이때 컬럼을 가공하지 않고 반대로 조건값을 가공합니다.


by 제일 [2013.03.28 16:49:21]

알려주신 쿼리에서 ADD_MONTHS(TO_DATTE('20120-06', 'yyyy-mm'), 1)부분은 어째서 1월 조회하는 조건과는 다르게 걸어주신건가요?


by 마농 [2013.03.28 16:54:40]

한달을 더해서 7월1일을 만든것입니다.
   AND 날짜 >= '1월1일'
   AND 날짜 <  '7월1일'  -- 이부분엔 이퀄조건이 빠지죠.


by 제일 [2013.03.28 17:05:26]
AND ADM_DTE >= TO_DATE('2012-01', 'yyyy-mm')  AND ADM_DTE <= TO_DATE('2012-06', 'yyyy-mm')
같은 쿼리를 사용하면 문제점이 있을까요?

by 마농 [2013.03.28 17:44:51]
그러면 1월1일부터 6월1일까지입니다.
정확하게 말하면 6월1일0시0분0초 까지입니다.
6월 한달치 자료(6월1일0시0분1초 ~ 6월30일23시59분59초)가 누락되는거죠.

by 제일 [2013.03.28 19:15:31]
정말 많은 도움이 되었습니다. 감사합니다. ^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입