월단위 파티션을 일단위 파티션으로 변경후 속도저하발생
kwindow2 1학년

답변 채택시 : 0코아

답변 1 추천 0 조회 398 2017.01.05

월 단위 파티션을 일단위파티션으로 변경후 속도가 늦게 저하되었습니다. ..이유가  궁금합니다.

*** 월파티션인 경우
select a14.ZONE_ANA_CD  ZONE_ANA_CD,
 a13.ADJT_QUT  ADJT_QUT,
 a11.EXCV_CRD_ANA_CD  EXCV_CRD_ANA_CD,
 count(distinct CASE WHEN  MBR_NO <> 'XXXXXXXXXX' THEN MBR_NO END)  WJXBFS1,
 sum(a11.CHARG_CNT)  WJXBFS2,
 sum(a11.GENL_FST_CHARG_CNT)  WJXBFS3,
 sum(a11.TNAGR_FST_CHARG_CNT)  WJXBFS4,
 sum(a11.CHILD_FST_CHARG_CNT)  WJXBFS5,
 count(distinct a11.CRD_IDNTF_NO)  WJXBFS6,
 sum(a11.TRF_TR_AMT)  WJXBFS7,
 sum(a11.OTSDG_TR_AMT)  WJXBFS8,
 sum(a11.K7_BTW_TR_AMT)  WJXBFS9
from SRADM.TB_FCA_COP_CRD_TR_PCND_MON a11
 ,SRADM.TB_FLC_DT a12
 
,SRADM.TB_FLC_YM a13
 
 ,SRADM.TB_FLC_ZONE_DTL a14
 
where a13.ADJT_QUT in ('201601', '201602', '201603', '201604')
  and a11.ADJT_DT = a12.ADJT_DT(+)
   and  a12.ADJT_YM = a13.ADJT_YM(+)
    and a11.ZONE_DTL_ANA_CD = a14.ZONE_DTL_ANA_CD(+)

 and a11.EXCV_CRD_ANA_CD in ('01')
 and a14.ZONE_ANA_CD in ('00', '02', '11', '17', '26', '27', '28', '29', '30', '31', '41', '42', '43', '44', '45', '46', '47', '48', '49', '70', '71', '99')
group by a14.ZONE_ANA_CD,
 a13.ADJT_QUT,
 a11.EXCV_CRD_ANA_CD;
Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     55  SORT GROUP BY (cr=5268602 pr=5333314 pw=64985 time=118297643 us cost=894089 size=4488 card=44)
30221027   HASH JOIN  (cr=5268602 pr=5268329 pw=0 time=34137486 us cost=894034 size=239298834 card=2346067)
    198    TABLE ACCESS STORAGE FULL TB_FLC_ZONE_DTL (cr=16 pr=0 pw=0 time=130 us cost=4 size=1782 card=198)
30221027    HASH JOIN  (cr=5268586 pr=5268329 pw=0 time=23577652 us cost=894024 size=218184138 card=2346066)
    366     JOIN FILTER CREATE :BF0001 (cr=68 pr=0 pw=0 time=2480 us cost=894024 size=218184138 card=2346066)
    366      PART JOIN FILTER CREATE :BF0000 (cr=68 pr=0 pw=0 time=2228 us cost=894024 size=218184138 card=2346066)
    366       NESTED LOOPS  (cr=68 pr=0 pw=0 time=2036 us cost=894024 size=218184138 card=2346066)
    366        STATISTICS COLLECTOR  (cr=68 pr=0 pw=0 time=2095 us)
    366         HASH JOIN  (cr=68 pr=0 pw=0 time=1766 us cost=14 size=10950 card=365)
     12          JOIN FILTER CREATE :BF0002 (cr=7 pr=0 pw=0 time=203 us cost=3 size=168 card=12)
     12           TABLE ACCESS STORAGE FULL TB_FLC_YM (cr=7 pr=0 pw=0 time=189 us cost=3 size=168 card=12)
    366          JOIN FILTER USE :BF0002 (cr=61 pr=0 pw=0 time=198 us cost=11 size=75984 card=4749)
    366           TABLE ACCESS STORAGE FULL TB_FLC_DT (cr=61 pr=0 pw=0 time=194 us cost=11 size=75984 card=4749)
      0        PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=893936 size=404586 card=6422)
      0         TABLE ACCESS STORAGE FULL TB_FCA_COP_CRD_TR_PCND_MON PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=893936 size=404586 card=6422)
30221027     JOIN FILTER USE :BF0001 (cr=5268518 pr=5268329 pw=0 time=12037465 us cost=893936 size=1923167484 card=30526468)
30221027      PARTITION RANGE JOIN-FILTER PARTITION: :BF0000 :BF0000 (cr=5268518 pr=5268329 pw=0 time=8557653 us cost=893936 size=1923167484 card=30526468)
30221027       TABLE ACCESS STORAGE FULL TB_FCA_COP_CRD_TR_PCND_MON PARTITION: :BF0000 :BF0000 (cr=5268518 pr=5268329 pw=0 time=4324081 us cost=893936 size=1923167484 card=30526468)

******************************************************************************** 

*** 일파티션 경우
select a14.ZONE_ANA_CD  ZONE_ANA_CD,
 a13.ADJT_QUT  ADJT_QUT,
 a11.EXCV_CRD_ANA_CD  EXCV_CRD_ANA_CD,
 count(distinct CASE WHEN  MBR_NO <> 'XXXXXXXXXX' THEN MBR_NO END)  WJXBFS1,
 sum(a11.CHARG_CNT)  WJXBFS2,
 sum(a11.GENL_FST_CHARG_CNT)  WJXBFS3,
 sum(a11.TNAGR_FST_CHARG_CNT)  WJXBFS4,
 sum(a11.CHILD_FST_CHARG_CNT)  WJXBFS5,
 count(distinct a11.CRD_IDNTF_NO)  WJXBFS6,
 sum(a11.TRF_TR_AMT)  WJXBFS7,
 sum(a11.OTSDG_TR_AMT)  WJXBFS8,
 sum(a11.K7_BTW_TR_AMT)  WJXBFS9
from SRADM.TB_FCA_COP_CRD_TR_PCND_DAY a11
 ,SRADM.TB_FLC_DT a12
 
,SRADM.TB_FLC_YM a13
 
 ,SRADM.TB_FLC_ZONE_DTL a14
 
where a13.ADJT_QUT in ('201601', '201602', '201603', '201604')
  and a11.ADJT_DT = a12.ADJT_DT(+)
   and  a12.ADJT_YM = a13.ADJT_YM(+)
    and a11.ZONE_DTL_ANA_CD = a14.ZONE_DTL_ANA_CD(+)

 and a11.EXCV_CRD_ANA_CD in ('01')
 and a14.ZONE_ANA_CD in ('00', '02', '11', '17', '26', '27', '28', '29', '30', '31', '41', '42', '43', '44', '45', '46', '47', '48', '49', '70', '71', '99')
group by a14.ZONE_ANA_CD,
 a13.ADJT_QUT,
 a11.EXCV_CRD_ANA_CD;
 

Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     55  SORT GROUP BY (cr=10420499 pr=7296160 pw=65136 time=350985204 us cost=356904 size=4488 card=44)
30221027   HASH JOIN  (cr=10420499 pr=7231024 pw=0 time=258311399 us cost=356849 size=239164704 card=2344752)
    198    TABLE ACCESS STORAGE FULL TB_FLC_ZONE_DTL (cr=16 pr=0 pw=0 time=37 us cost=4 size=1782 card=198)
30221027    HASH JOIN  (cr=10420483 pr=7231024 pw=0 time=246545580 us cost=356839 size=218061936 card=2344752)
30221027     NESTED LOOPS  (cr=10420483 pr=7231024 pw=0 time=241633464 us cost=356839 size=218061936 card=2344752)
    366      STATISTICS COLLECTOR  (cr=68 pr=0 pw=0 time=2408 us)
    366       HASH JOIN  (cr=68 pr=0 pw=0 time=1638 us cost=14 size=10950 card=365)
     12        JOIN FILTER CREATE :BF0000 (cr=7 pr=0 pw=0 time=260 us cost=3 size=168 card=12)
     12         TABLE ACCESS STORAGE FULL TB_FLC_YM (cr=7 pr=0 pw=0 time=240 us cost=3 size=168 card=12)
    366        JOIN FILTER USE :BF0000 (cr=61 pr=0 pw=0 time=222 us cost=11 size=75984 card=4749)
    366         TABLE ACCESS STORAGE FULL TB_FLC_DT (cr=61 pr=0 pw=0 time=216 us cost=11 size=75984 card=4749)
30221027      PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=10420415 pr=7231024 pw=0 time=224837615 us cost=978 size=404397 card=6419)
30221027       TABLE ACCESS STORAGE FULL TB_FCA_COP_CRD_TR_PCND_DAY PARTITION: KEY KEY (cr=10420415 pr=7231024 pw=0 time=221852736 us cost=978 size=404397 card=6419)
      0     PARTITION RANGE JOIN-FILTER PARTITION: :BF0000 :BF0000 (cr=0 pr=0 pw=0 time=0 us cost=978 size=404397 card=6419)
      0      TABLE ACCESS STORAGE FULL TB_FCA_COP_CRD_TR_PCND_DAY PARTITION: :BF0000 :BF0000 (cr=0 pr=0 pw=0 time=0 us cost=978 size=404397 card=6419)

******************************************************************************** 

댓글 (0)
목록 답변등록
월단위 파티션을 일단위 파티션으로 변경후 속도저하발생
moonsunki 2017.01.17

월파티션과 일파티션 plan을 비교 해보면 가장 큰 차이점이

월파티션에는 bloom filter가 발생되었는데 일파티션에서는 bloom filter가 발생 되지 않은것 같습니다. SRADM.TB_FCA_COP_CRD_TR_PCND_MON a11 후행 테이블이 bloom filter가 발생 될수 있도록

힌트를 추가 해보시고 검증 해 보세요.(힌트 : PX_JOIN_FILTER(a11))

댓글 (0)