Recently I’ve met many long running issues that due to hidden paraleter _optimizer_extend_jppd_view_types set to true.
I have open an SR (3-3225436011) to oracle, and oracle said that it’s a bug
Bug 10190002: POOR PERFORMANCE WITH /*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED','TRUE')*/
Here is an example for this bug:
SQL:
CREATE TABLE robinson AS
SELECT PRMTN.BUS_UNIT_SKID,
PRMTN.ACCT_PRMTN_SKID,
PRMTN.PRMTN_SKID,
CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND NVL(ACTVY.ACTL_MDA_LOR_AMT, 0 ) < NVL(ACTVY.ESTMT_MDA_LOR_AMT, 0))
THEN DECODE( ( PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM ), 0, 0, ( NVL(BASLN.ACTL_TMP_GIV - BASLN.BASLN_TMP_GIV, 0 ) - NVL(ACTVY.ESTMT_MDA_LOR_AMT, 0 ) ) / ( PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM ))
ELSE DECODE( ( PRMTN.ACTL_COST_TOT + PRMTN.ACTL_SPND_NUM ), 0, 0, ( NVL(BASLN.ACTL_TMP_GIV - BASLN.BASLN_TMP_GIV, 0 ) - NVL(ACTVY.ACTL_MDA_LOR_AMT, 0) ) / ( PRMTN.ACTL_COST_TOT + PRMTN.ACTL_SPND_NUM ))
END AS ACTL_EVENT_ROI, -- Actual Event ROI
/*DECODE((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM), 0, 0, ( NVL(PRMTN.ESTMT_INCRM_NR_AMT, 0) - NVL(ACTVY.ESTMT_ROI_MDA_LOR_AMT, 0) ) / (PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM)) AS ESTMT_EVENT_ROI, -- Estimated Event ROI*/
(NVL(PRMTN_PROD.ACTL_TMP_NOS, 0 ) * ( 100 - PRMTN.PCT_CNBLN_NUM) / 100 - CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND NVL(ACTVY.ACTL_MDA_LOR_AMT, 0 ) < NVL(ACTVY.ESTMT_MDA_LOR_AMT, 0 ))
THEN NVL(ACTVY.ESTMT_MDA_LOR_AMT, 0 ) ELSE NVL(ACTVY.ACTL_MDA_LOR_AMT, 0 ) END) AS ACTL_NOS_AMT, -- Actual NOS
(NVL(BASLN.ACTL_TMP_GIV - BASLN.BASLN_TMP_GIV, 0 ) - CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND NVL(ACTVY.ACTL_MDA_LOR_AMT, 0 ) < NVL(ACTVY.ESTMT_MDA_LOR_AMT, 0 ))
THEN NVL(ACTVY.ESTMT_MDA_LOR_AMT, 0 ) ELSE NVL(ACTVY.ACTL_MDA_LOR_AMT, 0 ) END ) AS ACTL_INCRM_NOS_AMT, -- Actual Incremental NOS
(NVL(PRMTN_PROD.ESTMT_TMP_NOS , 0 ) * ( 100 - PRMTN.PCT_CNBLN_NUM) / 100 - NVL(ACTVY.ESTMT_MDA_LOR_AMT , 0 )) AS ESTMT_NOS_AMT, -- Estimated NOS
(NVL(PRMTN_PROD.ESTMT_TMP_INC_NOS , 0 ) * ( 100 - PRMTN.PCT_CNBLN_NUM ) / 100 - NVL(ACTVY.ESTMT_MDA_LOR_AMT , 0 )) AS ESTMT_INCRM_NOS_AMT, -- Estimated Incremental NOS
NVL(ACTVY.ESTMT_MDA_LOR_AMT , 0 ) AS ESTMT_MDA_LOR_AMT, -- Estimated MDA LOR
NVL(ACTVY.ACTL_MDA_LOR_AMT , 0 ) AS ACTL_MDA_LOR_AMT, -- Actual MDA LOR,
NVL(EPOS_FCT.ACTL_EPOS_SU_AMT , 0 ) AS ACTL_EPOS_SU_AMT, -- EPOS SU Amount
CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND NVL(ACTVY.ACTL_MDA_LOR_AMT , 0 ) < NVL(ACTVY.ESTMT_MDA_LOR_AMT , 0 ))
THEN NVL(EPOS_BASLN.EPOS_ACTL_TMP_GIV - EPOS_BASLN.EPOS_BASLN_TMP_GIV , 0) - NVL(ACTVY.ESTMT_MDA_LOR_AMT , 0)
ELSE NVL(EPOS_BASLN.EPOS_ACTL_TMP_GIV - EPOS_BASLN.EPOS_BASLN_TMP_GIV , 0) - NVL(ACTVY.ACTL_MDA_LOR_AMT , 0)
END AS ACTL_EPOS_INCRM_NOS_AMT, -- Actual ePOS Incremental NOS
CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND NVL(ACTVY.ACTL_MDA_LOR_AMT , 0 ) < NVL(ACTVY.ESTMT_MDA_LOR_AMT , 0 ))
THEN DECODE( ( PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM ), 0, 0, ( NVL(EPOS_BASLN.EPOS_ACTL_TMP_GIV - EPOS_BASLN.EPOS_BASLN_TMP_GIV , 0) - NVL(ACTVY.ESTMT_MDA_LOR_AMT,0) ) / ( PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM ))
ELSE DECODE( ( PRMTN.ACTL_COST_TOT + PRMTN.ACTL_SPND_NUM ), 0, 0, ( NVL(EPOS_BASLN.EPOS_ACTL_TMP_GIV - EPOS_BASLN.EPOS_BASLN_TMP_GIV , 0) - NVL(ACTVY.ACTL_MDA_LOR_AMT, 0) ) / ( PRMTN.ACTL_COST_TOT + PRMTN.ACTL_SPND_NUM ))
END AS ACTL_EPOS_EVENT_ROI, -- Actual ePOS Event ROI
------ Optima90, B010, Bean, Begin
( PRMTN.ESTMT_INCRM_NIV_AX_AMT - (PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT - PRMTN.ESTMT_VAR_COST_ON_INVC_NUM) ) AS ESTMT_INCRM_NOS_NIV_AMT,
---Estimated Incremental Net Outside Sales by Net Invoice Value Amount
(DECODE((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM),NULL,0,0,0,
( PRMTN.ESTMT_INCRM_NIV_AX_AMT - (PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT - PRMTN.ESTMT_VAR_COST_ON_INVC_NUM)) / (PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM))) AS ESTMT_EVENT_ROI_NIV_RATE, ---Estimated Event Return On Investment by Net Invoice Value
CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND
((((PRMTN.CALC_INDEX_NUM + PRMTN.ACTL_VAR_COST_NUM) - PRMTN.ACTL_VAR_COST_ON_INVC_NUM)) ----ACTUAL INCREMENTAL NIV - ACTUAL MDA NOT PAID ON INVOICE
< (((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT) - PRMTN.ESTMT_VAR_COST_ON_INVC_NUM )))) ----- ESTIMATED INCREMENTAL NIV - ESTIMATED MDA NOT PAID ON INVOICE
THEN ( NVL(BASLN.ACTL_TMP_NIV - BASLN.BASLN_TMP_NIV, 0 ) - NVL(((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT) - PRMTN.ESTMT_VAR_COST_ON_INVC_NUM ),0))
ELSE ( NVL(BASLN.ACTL_TMP_NIV - BASLN.BASLN_TMP_NIV, 0 ) - NVL(((PRMTN.CALC_INDEX_NUM + PRMTN.ACTL_VAR_COST_NUM) - PRMTN.ACTL_VAR_COST_ON_INVC_NUM),0))
END AS ACTL_INCRM_NOS_NIV_AMT, ---Actual Incremental Net Outside Sales by Net Invoice Value Amount
-----Actual Event Return On Investment by Net Invoice Value
CASE WHEN (PRMTN.PRMTN_STTUS_CODE <> 'Completed' AND
((((PRMTN.CALC_INDEX_NUM + PRMTN.ACTL_VAR_COST_NUM) - PRMTN.ACTL_VAR_COST_ON_INVC_NUM)) ----ACTUAL INCREMENTAL NIV - ACTUAL MDA NOT PAID ON INVOICE
< ((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT) - PRMTN.ESTMT_VAR_COST_ON_INVC_NUM ))) ----- ESTIMATED INCREMENTAL NIV - ESTIMATED MDA NOT PAID ON INVOICE
THEN DECODE((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM),null,0,0,0,
( NVL(BASLN.ACTL_TMP_NIV - BASLN.BASLN_TMP_NIV, 0 ) - NVL(((PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT) - PRMTN.ESTMT_VAR_COST_ON_INVC_NUM ),0)) /
(PRMTN.FIXED_COST_ESTMT_AMT + PRMTN.VAR_COST_ESTMT_AMT + PRMTN.BASE_COST_AMT + PRMTN.ESTMT_SPND_NUM))
ELSE DECODE(PRMTN.ACTL_COST_TOT + PRMTN.ACTL_SPND_NUM, null, 0, 0, 0, (( BASLN.ACTL_TMP_NIV - BASLN.BASLN_TMP_NIV) - ((PRMTN.CALC_INDEX_NUM + PRMTN.ACTL_VAR_COST_NUM) - PRMTN.ACTL_VAR_COST_ON_INVC_NUM))/(PRMTN.ACTL_COST_TOT + PRMTN.ACTL_SPND_NUM))
END AS ACTL_EVENT_ROI_NIV_RATE, --- Actual Event Return On Investment by Net Invoice Value
BASLN.ACTL_TMP_NIV AS ACTL_SHPMT_NIV_AMT,
BASLN.BASLN_TMP_NIV AS ACTL_SHPMT_BASLN_NIV_AMT
FROM (
SELECT BFCT.PRMTN_SKID,BFCT.ACCT_SKID,
SUM( BFCT.ACTL_GIV_AMT * (100 - NVL(BRAND_BASLN_FCT.TRADE_TERM_PCT,0)) / 100 ) AS ACTL_TMP_GIV,
SUM( BFCT.BASLN_GIV_AMT * (100 - NVL(BRAND_BASLN_FCT.TRADE_TERM_PCT,0)) / 100 ) AS BASLN_TMP_GIV,
------ Optima90, B010, Bean, Begin
SUM( BFCT.ACTL_NIV_AMT ) AS ACTL_TMP_NIV,
SUM( BFCT.BASLN_NIV_AMT ) AS BASLN_TMP_NIV
------ Optima90, B010, Bean, End
FROM ( -- Agrregate Actual GIV and Baseline GIV from Week to Month Level
SELECT BFCT.BUS_UNIT_SKID, BFCT.PROD_SKID, BFCT.ACCT_SKID, BFCT.PRMTN_SKID, D.MTH_SKID,
SUM(BFCT.ACTL_GIV_AMT) AS ACTL_GIV_AMT,
SUM(BFCT.BASLN_GIV_AMT) AS BASLN_GIV_AMT,
------ Optima90, B010, Bean, Begin
SUM(BFCT.ACTL_NIV_AMT) AS ACTL_NIV_AMT,
SUM(BFCT.BASLN_NIV_AMT) AS BASLN_NIV_AMT
------ Optima90, B010, Bean, End
FROM OPT_BASLN_FCT BFCT, OPT_CAL_MASTR_MV01 D
WHERE BFCT.WK_SKID = D.CAL_MASTR_SKID
GROUP BY BFCT.BUS_UNIT_SKID, BFCT.PROD_SKID, BFCT.ACCT_SKID, BFCT.PRMTN_SKID, D.MTH_SKID
)BFCT, OPT_BRAND_BASLN_FCT BRAND_BASLN_FCT, (SELECT DISTINCT PFCT.BASE_PRMTN_SKID AS PRMTN_SKID
FROM OPT_PRMTN_FCT PFCT
/*WHERE PFCT.REGN_CODE = 'AP'*/) PRMTN_FLT
WHERE BFCT.BUS_UNIT_SKID = BRAND_BASLN_FCT.BUS_UNIT_SKID(+)
AND BFCT.PROD_SKID = BRAND_BASLN_FCT.PROD_SKID(+)
AND BFCT.ACCT_SKID = BRAND_BASLN_FCT.PRMTN_ACCT_SKID(+)
AND BFCT.MTH_SKID = BRAND_BASLN_FCT.DATE_SKID(+)
AND BFCT.PRMTN_SKID = PRMTN_FLT.PRMTN_SKID
GROUP BY BFCT.PRMTN_SKID,BFCT.ACCT_SKID
) BASLN,
(
SELECT PFCT.BUS_UNIT_SKID,
PFCT.ACCT_PRMTN_SKID,
PFCT.BASE_PRMTN_SKID AS PRMTN_SKID,
PFCT.ACTL_SPND_NUM AS ACTL_SPND_NUM,
PFCT.CALC_INDEX_NUM + PFCT.ACTL_VAR_COST_NUM AS ACTL_COST_TOT,
PDIM.PRMTN_STTUS_CODE,
PFCT.VAR_COST_ESTMT_AMT AS VAR_COST_ESTMT_AMT,
PFCT.FIXED_COST_ESTMT_AMT AS FIXED_COST_ESTMT_AMT,
PFCT.BASE_COST_AMT AS BASE_COST_AMT,
PFCT.ESTMT_SPND_NUM AS ESTMT_SPND_NUM,
PFCT.ACTL_VAR_COST_NUM AS ACTL_VAR_COST_NUM,
PFCT.ESTMT_INCRM_NR_AMT,
PFCT.PCT_CNBLN_NUM,
-- Optima90, B010, Bean, Begin
PFCT.ESTMT_VAR_COST_ON_INVC_NUM,
PFCT.ACTL_VAR_COST_ON_INVC_NUM,
PFCT.CALC_INDEX_NUM,
PFCT.ESTMT_INCRM_NIV_AX_AMT
-- Optima90, B010, Bean, End
FROM OPT_PRMTN_FCT PFCT, OPT_PRMTN_DIM PDIM, (SELECT DISTINCT PFCT.BASE_PRMTN_SKID AS PRMTN_SKID
FROM OPT_PRMTN_FCT PFCT
/*WHERE PFCT.REGN_CODE = 'AP'*/) PRMTN_FLT
WHERE PDIM.PRMTN_SKID = PFCT.BASE_PRMTN_SKID
AND PDIM.PRMTN_SKID<>0
AND PFCT.BASE_PRMTN_SKID = PRMTN_FLT.PRMTN_SKID
) PRMTN,
(
SELECT PRMTN_PROD_FCT.PRMTN_SKID,
SUM(PRMTN_PROD_FCT.ACTL_GIV_AMT * ( 100 - PRMTN_PROD_FCT.TRADE_TERM_PCT) / 100) AS ACTL_TMP_NOS,
SUM(PRMTN_PROD_FCT.TOT_IN_GIV_AMT * ( 100 - PRMTN_PROD_FCT.TRADE_TERM_PCT) / 100) AS ESTMT_TMP_NOS, -- ESTMT_SPND_AMT -> TOT_IN_GIV_AMT, Changed by joy on Oct. 21, 2009
SUM(PRMTN_PROD_FCT.INCRM_IN_GIV_AMT * ( 100 - PRMTN_PROD_FCT.TRADE_TERM_PCT) / 100) AS ESTMT_TMP_INC_NOS
FROM OPT_PRMTN_PROD_FCT PRMTN_PROD_FCT, (SELECT DISTINCT PFCT.BASE_PRMTN_SKID AS PRMTN_SKID
FROM OPT_PRMTN_FCT PFCT
/*WHERE PFCT.REGN_CODE = 'AP'*/) PRMTN_FLT
WHERE PRMTN_PROD_FCT.PRMTN_SKID = PRMTN_FLT.PRMTN_SKID
GROUP BY PRMTN_PROD_FCT.PRMTN_SKID
) PRMTN_PROD,
(
SELECT OPT_ACTVY_FCT.PRMTN_SKID,
-- optima90, B019, Bean, begin
-- SUM(DECODE(SUBSTR(OPT_ACTVY_FDIM.COST_ELEM_CODE,1,2),'30', OPT_ACTVY_FCT.PRDCT_FIXED_COST_AMT + OPT_ACTVY_FCT.VAR_COST_ESTMT_AMT)) AS ESTMT_MDA_LOR_AMT, -- Estimated MDA LOR
-- SUM(DECODE(SUBSTR(OPT_ACTVY_FDIM.COST_ELEM_CODE,1,2),'30', OPT_ACTVY_FCT.PRDCT_FIXED_COST_AMT * ( 100 + OPT_ACTVY_FDIM.ACTVY_PCT_SPLIT_NUM) / 100 + OPT_ACTVY_FCT.VAR_COST_ESTMT_AMT)) AS ESTMT_ROI_MDA_LOR_AMT,
-- Estimated MDA LOR for Estimated Event ROI
-- SUM(DECODE(SUBSTR(OPT_ACTVY_FDIM.COST_ELEM_CODE,1,2),'30', OPT_ACTVY_FCT.CALC_INDEX_NUM + OPT_ACTVY_FCT.ACTL_VAR_COST_NUM)) AS ACTL_MDA_LOR_AMT -- Actual MDA LOR
--Bean, optima90, B019, remarked end
-- optima90, B019, Bean, begin recalcuate Estimated MDA LOR, Actual MDA LOR to avoid cost double counting according to SRS B019
SUM(DECODE(SUBSTR(OPT_ACTVY_FDIM.COST_ELEM_CODE,1,2),'30',DECODE(OPT_ACTVY_FDIM.ACTVY_SPECL_PACK_IND , NULL, OPT_ACTVY_FCT.PRDCT_FIXED_COST_AMT + OPT_ACTVY_FCT.VAR_COST_ESTMT_AMT ,0))) AS ESTMT_MDA_LOR_AMT,
-- Estimated MDA LOR
-- optima90, B019, Bean, end
SUM(DECODE(SUBSTR(OPT_ACTVY_FDIM.COST_ELEM_CODE,1,2),'30', OPT_ACTVY_FCT.PRDCT_FIXED_COST_AMT * ( 100 + OPT_ACTVY_FDIM.ACTVY_PCT_SPLIT_NUM) / 100 + OPT_ACTVY_FCT.VAR_COST_ESTMT_AMT)) AS ESTMT_ROI_MDA_LOR_AMT,
-- Estimated MDA LOR for Estimated Event ROI
-- Optima90, B019, Bean Begin
SUM(DECODE(SUBSTR(OPT_ACTVY_FDIM.COST_ELEM_CODE,1,2),'30',DECODE(OPT_ACTVY_FDIM.ACTVY_SPECL_PACK_IND , NULL, OPT_ACTVY_FCT.CALC_INDEX_NUM + OPT_ACTVY_FCT.ACTL_VAR_COST_NUM, 0))) AS ACTL_MDA_LOR_AMT
-- Actual MDA LOR
-- Optima90, B019, Bean end
FROM OPT_ACTVY_FCT, OPT_ACTVY_FDIM, (SELECT DISTINCT PFCT.BASE_PRMTN_SKID AS PRMTN_SKID
FROM OPT_PRMTN_FCT PFCT
/*WHERE PFCT.REGN_CODE = 'AP'*/) PRMTN_FLT
WHERE OPT_ACTVY_FCT.ACTVY_SKID = OPT_ACTVY_FDIM.ACTVY_SKID
AND OPT_ACTVY_FCT.PRMTN_SKID = PRMTN_FLT.PRMTN_SKID
GROUP BY OPT_ACTVY_FCT.PRMTN_SKID
) ACTVY,
(
SELECT PRMTN_PROD_FCT.PRMTN_SKID AS PRMTN_SKID,
SUM(EPOS_FCT.VOL_SU) AS ACTL_EPOS_SU_AMT
FROM OPT_EPOS_FCT EPOS_FCT,
OPT_PRMTN_PROD_FCT PRMTN_PROD_FCT,
OPT_CAL_MASTR_MV01 CAL_MASTR,
OPT_PRMTN_FDIM PRMTN_FDIM,
/*
(SELECT ACCT_SKID,
CONNECT_BY_ROOT ACCT_SKID AS ROOT_ACCT_SKID,
ACCT_TYPE_DESC
FROM OPT_ACCT_FDIM
CONNECT BY PRIOR ACCT_ID = PARNT_ACCT_ID
) HIER,
*/
-- Type 2 Account denormalized dimension
OPT_ACCT_ASDN_TYPE2_DIM HIER, (SELECT DISTINCT PFCT.BASE_PRMTN_SKID AS PRMTN_SKID
FROM OPT_PRMTN_FCT PFCT
/*WHERE PFCT.REGN_CODE = 'AP'*/) PRMTN_FLT
WHERE EPOS_FCT.PROD_SKID = PRMTN_PROD_FCT.PROD_SKID
AND PRMTN_PROD_FCT.PRMTN_SKID = PRMTN_FDIM.PRMTN_SKID
AND EPOS_FCT.DATE_SKID = CAL_MASTR.CAL_MASTR_SKID
AND CAL_MASTR.DAY_DATE BETWEEN HIER.ASDN_EFF_START_DATE AND HIER.ASDN_EFF_END_DATE
AND CAL_MASTR.DAY_DATE BETWEEN PRMTN_FDIM.PGM_START_DATE AND PRMTN_FDIM.PGM_END_DATE
-- ACCT_SKID in EPOS_FCT is on lower level, so ACCT_SKID in OPT_ACCT_ASDN_TYPE2_DIM is used
AND EPOS_FCT.ACCT_SKID = HIER.ACCT_SKID
-- ACCT_SKID in PROMOTION dimension is on higher level, so ASSOC_ACCT_SKID in OPT_ACCT_ASDN_TYPE2_DIM is used
--AND HIER.ROOT_ACCT_SKID = PRMTN_FDIM.ACCT_SKID
AND HIER.ASSOC_ACCT_SKID = PRMTN_FDIM.ACCT_SKID
AND PRMTN_PROD_FCT.PRMTN_SKID = PRMTN_FLT.PRMTN_SKID
GROUP BY PRMTN_PROD_FCT.PRMTN_SKID
) EPOS_FCT,
(
SELECT EPOS_BFCT.PRMTN_SKID AS PRMTN_SKID,
SUM( EPOS_BFCT.ACTL_GIV_AMT * (100 - NVL(BRAND_BASLN_FCT.TRADE_TERM_PCT,0)) / 100 ) AS EPOS_ACTL_TMP_GIV,
SUM( EPOS_BFCT.BASLN_GIV_AMT * (100 - NVL(BRAND_BASLN_FCT.TRADE_TERM_PCT,0)) / 100 ) AS EPOS_BASLN_TMP_GIV
FROM ( -- Agrregate Actual GIV and Baseline GIV from Week to Month Level
SELECT EPOS_BFCT.BUS_UNIT_SKID, EPOS_BFCT.ACCT_SKID, EPOS_BFCT.PRMTN_SKID, EPOS_BFCT.PROD_SKID, D.MTH_SKID,
SUM(EPOS_BFCT.ACTL_GIV_AMT) AS ACTL_GIV_AMT,
SUM(EPOS_BFCT.BASLN_GIV_AMT) AS BASLN_GIV_AMT
FROM OPT_EPOS_POST_EVENT_BASLN_FCT EPOS_BFCT, OPT_CAL_MASTR_MV01 D
WHERE EPOS_BFCT.WK_SKID = D.CAL_MASTR_SKID
GROUP BY EPOS_BFCT.BUS_UNIT_SKID, EPOS_BFCT.ACCT_SKID, EPOS_BFCT.PRMTN_SKID, EPOS_BFCT.PROD_SKID, D.MTH_SKID
)EPOS_BFCT,
OPT_BRAND_BASLN_FCT BRAND_BASLN_FCT
WHERE EPOS_BFCT.BUS_UNIT_SKID = BRAND_BASLN_FCT.BUS_UNIT_SKID(+)
AND EPOS_BFCT.PROD_SKID = BRAND_BASLN_FCT.PROD_SKID(+)
AND EPOS_BFCT.ACCT_SKID = BRAND_BASLN_FCT.PRMTN_ACCT_SKID(+)
AND EPOS_BFCT.MTH_SKID = BRAND_BASLN_FCT.DATE_SKID(+)
-- AND EPOS_BFCT.PRMTN_SKID = PRMTN_FLT.PRMTN_SKID
AND EPOS_BFCT.PRMTN_SKID IN (SELECT DISTINCT PFCT.BASE_PRMTN_SKID AS PRMTN_SKID
FROM OPT_PRMTN_FCT PFCT
/*WHERE PFCT.REGN_CODE = 'AP'*/ )
GROUP BY EPOS_BFCT.PRMTN_SKID
) EPOS_BASLN
WHERE PRMTN.PRMTN_SKID = BASLN.PRMTN_SKID(+)
AND PRMTN.ACCT_PRMTN_SKID = BASLN.ACCT_SKID(+)
AND PRMTN.PRMTN_SKID = ACTVY.PRMTN_SKID(+)
AND PRMTN.PRMTN_SKID = PRMTN_PROD.PRMTN_SKID(+)
AND PRMTN.PRMTN_SKID = EPOS_FCT.PRMTN_SKID(+)
AND PRMTN.PRMTN_SKID = EPOS_BASLN.PRMTN_SKID(+);
Explain plan for the SQL , Execution plan as below:
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 392063744
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 313 | 257K (1)| 00:13:27 | | | | | |
| 1 | LOAD AS SELECT | ROBINSON | | | | | | | | | |
| 2 | NESTED LOOPS | | | | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 313 | 257K (1)| 00:13:27 | | | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 297 | 257K (1)| 00:13:27 | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 258 | 257K (1)| 00:13:26 | | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 232 | 257K (1)| 00:13:25 | | | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 219 | 256K (1)| 00:13:23 | | | | | |
| 8 | NESTED LOOPS SEMI | | 1 | 193 | 256K (1)| 00:13:22 | | | | | |
| 9 | NESTED LOOPS OUTER | | 168K| 26M| 86690 (1)| 00:04:32 | | | | | |
| 10 | PARTITION LIST ALL | | 168K| 18M| 1279 (7)| 00:00:05 | 1 | 16 | | | |
|* 11 | TABLE ACCESS FULL | OPT_PRMTN_FCT | 168K| 18M| 1279 (7)| 00:00:05 | 1 | 16 | | | |
| 12 | VIEW PUSHED PREDICATE | | 1 | 52 | | | | | | | |
|* 13 | FILTER | | | | | | | | | | |
| 14 | SORT AGGREGATE | | 1 | 206 | | | | | | | |
|* 15 | FILTER | | | | | | | | | | |
| 16 | NESTED LOOPS OUTER | | 1 | 206 | 605 (7)| 00:00:02 | | | | | |
| 17 | NESTED LOOPS | | 1 | 182 | 56 (4)| 00:00:01 | | | | | |
| 18 | VIEW | | 1 | 13 | 19 (6)| 00:00:01 | | | | | |
| 19 | SORT UNIQUE | | 1 | 6 | 18 (6)| 00:00:01 | | | | | |
|* 20 | FILTER | | | | | | | | | | |
| 21 | PARTITION LIST ALL | | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
|* 22 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
|* 23 | VIEW | | 1 | 169 | 37 (3)| 00:00:01 | | | | | |
| 24 | SORT GROUP BY | | 1 | 63 | 36 (3)| 00:00:01 | | | | | |
|* 25 | FILTER | | | | | | | | | | |
| 26 | NESTED LOOPS | | | | | | | | | | |
| 27 | NESTED LOOPS | | 1 | 63 | 35 (0)| 00:00:01 | | | | | |
| 28 | PARTITION LIST ALL | | 1 | 53 | 34 (0)| 00:00:01 | 1 | 16 | | | |
| 29 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_BASLN_FCT | 1 | 53 | 34 (0)| 00:00:01 | 1 | 16 | | | |
| 30 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | |
| 31 | BITMAP AND | | | | | | | | | | |
|* 32 | BITMAP INDEX SINGLE VALUE | OPT_BASLN_FCT_NX4 | | | | | 1 | 16 | | | |
|* 33 | BITMAP INDEX SINGLE VALUE | OPT_BASLN_FCT_NX3 | | | | | 1 | 16 | | | |
|* 34 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_MV01_PK | 1 | | 0 (0)| 00:00:01 | | | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_MV01 | 1 | 10 | 1 (0)| 00:00:01 | | | | | |
| 36 | PX COORDINATOR | | | | | | | | | | |
| 37 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 24 | 549 (7)| 00:00:02 | | | Q1,00 | P->S | QC (RAND) |
| 38 | PX BLOCK ITERATOR | | 1 | 24 | 549 (7)| 00:00:02 | KEY | KEY | Q1,00 | PCWC | |
|* 39 | TABLE ACCESS FULL | OPT_BRAND_BASLN_FCT | 1 | 24 | 549 (7)| 00:00:02 | KEY | KEY | Q1,00 | PCWP | |
| 40 | VIEW PUSHED PREDICATE | | 1 | 26 | | | | | | | |
|* 41 | FILTER | | | | | | | | | | |
| 42 | PARTITION LIST ALL | | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
| 43 | BITMAP CONVERSION TO ROWIDS | | 1 | 6 | 17 (0)| 00:00:01 | | | | | |
|* 44 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | | | | | 1 | 16 | | | |
| 45 | VIEW PUSHED PREDICATE | | 1 | 26 | | | | | | | |
|* 46 | FILTER | | | | | | | | | | |
| 47 | SORT AGGREGATE | | 1 | 147 | | | | | | | |
| 48 | NESTED LOOPS SEMI | | 1 | 147 | 569 (8)| 00:00:02 | | | | | |
| 49 | NESTED LOOPS OUTER | | 1 | 141 | 560 (8)| 00:00:02 | | | | | |
| 50 | VIEW | | 1 | 117 | 3 (34)| 00:00:01 | | | | | |
| 51 | SORT GROUP BY | | 1 | 101 | 2 (50)| 00:00:01 | | | | | |
|* 52 | FILTER | | | | | | | | | | |
| 53 | NESTED LOOPS | | | | | | | | | | |
| 54 | NESTED LOOPS | | 1 | 101 | 1 (0)| 00:00:01 | | | | | |
| 55 | PARTITION LIST ALL | | 1 | 91 | 1 (0)| 00:00:01 | 1 | 16 | | | |
| 56 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_EPOS_POST_EVENT_BASLN_FCT | 1 | 91 | 1 (0)| 00:00:01 | 1 | 16 | | | |
| 57 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | |
|* 58 | BITMAP INDEX SINGLE VALUE | OPT_EPOS_PST_EVNT_BSLN_FCT_BX4 | | | | | 1 | 16 | | | |
|* 59 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_MV01_PK | 1 | | 0 (0)| 00:00:01 | | | | | |
| 60 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_MV01 | 1 | 10 | 0 (0)| 00:00:01 | | | | | |
| 61 | PX COORDINATOR | | | | | | | | | | |
| 62 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 24 | 557 (8)| 00:00:02 | | | Q2,00 | P->S | QC (RAND) |
| 63 | PX BLOCK ITERATOR | | 1 | 24 | 557 (8)| 00:00:02 | KEY | KEY | Q2,00 | PCWC | |
|* 64 | TABLE ACCESS FULL | OPT_BRAND_BASLN_FCT | 1 | 24 | 557 (8)| 00:00:02 | KEY | KEY | Q2,00 | PCWP | |
| 65 | PARTITION LIST ALL | | 1 | 6 | 569 (8)| 00:00:02 | 1 | 16 | | | |
| 66 | BITMAP CONVERSION TO ROWIDS | | 1 | 6 | 569 (8)| 00:00:02 | | | | | |
|* 67 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | | | | | 1 | 16 | | | |
| 68 | VIEW PUSHED PREDICATE | | 1 | 13 | | | | | | | |
|* 69 | FILTER | | | | | | | | | | |
| 70 | SORT AGGREGATE | | 1 | 135 | | | | | | | |
|* 71 | PX COORDINATOR | | | | | | | | | | |
| 72 | PX SEND QC (RANDOM) | :TQ30002 | 1 | 135 | | | | | Q3,02 | P->S | QC (RAND) |
| 73 | SORT AGGREGATE | | 1 | 135 | | | | | Q3,02 | PCWP | |
|* 74 | FILTER | | | | | | | | Q3,02 | PCWC | |
| 75 | NESTED LOOPS SEMI | | 1 | 135 | 655 (1)| 00:00:03 | | | Q3,02 | PCWP | |
| 76 | NESTED LOOPS | | 1 | 109 | 636 (1)| 00:00:02 | | | Q3,02 | PCWP | |
| 77 | NESTED LOOPS | | 1 | 81 | 634 (1)| 00:00:02 | | | Q3,02 | PCWP | |
|* 78 | HASH JOIN | | 3 | 204 | 633 (1)| 00:00:02 | | | Q3,02 | PCWP | |
|* 79 | HASH JOIN | | 1 | 46 | 334 (1)| 00:00:02 | | | Q3,02 | PCWP | |
| 80 | BUFFER SORT | | | | | | | | Q3,02 | PCWC | |
| 81 | PX RECEIVE | | 1 | 28 | 3 (0)| 00:00:01 | | | Q3,02 | PCWP | |
| 82 | PX SEND BROADCAST | :TQ30000 | 1 | 28 | 3 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 83 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PRMTN_FDIM | 1 | 28 | 3 (0)| 00:00:01 | ROWID | ROWID | | | |
|* 84 | INDEX RANGE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 2 (0)| 00:00:01 | | | | | |
| 85 | PX PARTITION LIST ALL | | 912 | 16416 | 330 (1)| 00:00:02 | 1 | 16 | Q3,02 | PCWC | |
| 86 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_PRMTN_PROD_FCT | 912 | 16416 | 330 (1)| 00:00:02 | 1 | 16 | Q3,02 | PCWP | |
| 87 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q3,02 | PCWP | |
|* 88 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_PROD_FCT_BX4 | | | | | 1 | 16 | Q3,02 | PCWP | |
| 89 | BUFFER SORT | | | | | | | | Q3,02 | PCWC | |
| 90 | PX RECEIVE | | 3039 | 66858 | 298 (2)| 00:00:01 | | | Q3,02 | PCWP | |
| 91 | PX SEND BROADCAST | :TQ30001 | 3039 | 66858 | 298 (2)| 00:00:01 | | | | S->P | BROADCAST |
| 92 | PARTITION RANGE ALL | | 3039 | 66858 | 298 (2)| 00:00:01 | 1 | 21 | | | |
| 93 | PARTITION LIST ALL | | 3039 | 66858 | 298 (2)| 00:00:01 | 1 | 5 | | | |
| 94 | TABLE ACCESS FULL | OPT_EPOS_FCT | 3039 | 66858 | 298 (2)| 00:00:01 | 1 | 105 | | | |
|* 95 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_MV01 | 1 | 13 | 1 (0)| 00:00:01 | | | Q3,02 | PCWP | |
|* 96 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_MV01_PK | 1 | | 0 (0)| 00:00:01 | | | Q3,02 | PCWP | |
|* 97 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_ASDN_TYPE2_DIM | 1 | 28 | 3 (0)| 00:00:01 | ROWID | ROWID | Q3,02 | PCWP | |
|* 98 | INDEX RANGE SCAN | OPT_ACCT_ASDN_TYPE2_DIM_PK | 1 | | 2 (0)| 00:00:01 | | | Q3,02 | PCWP | |
| 99 | VIEW PUSHED PREDICATE | | 1 | 26 | | | | | Q3,02 | PCWP | |
|*100 | FILTER | | | | | | | | Q3,02 | PCWP | |
| 101 | PARTITION LIST ALL | | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | Q3,02 | PCWP | |
| 102 | BITMAP CONVERSION TO ROWIDS | | 1 | 6 | 17 (0)| 00:00:01 | | | Q3,02 | PCWP | |
|*103 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | | | | | 1 | 16 | Q3,02 | PCWP | |
| 104 | VIEW PUSHED PREDICATE | | 1 | 26 | | | | | | | |
|*105 | FILTER | | | | | | | | | | |
| 106 | SORT AGGREGATE | | 1 | 58 | | | | | | | |
|*107 | FILTER | | | | | | | | | | |
| 108 | NESTED LOOPS | | | | | | | | | | |
| 109 | NESTED LOOPS | | 1 | 58 | 39 (6)| 00:00:01 | | | | | |
|*110 | HASH JOIN | | 1 | 39 | 38 (6)| 00:00:01 | | | | | |
| 111 | VIEW | | 1 | 13 | 19 (6)| 00:00:01 | | | | | |
| 112 | SORT UNIQUE | | 1 | 6 | 18 (6)| 00:00:01 | | | | | |
|*113 | FILTER | | | | | | | | | | |
| 114 | PARTITION LIST ALL | | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
|*115 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
| 116 | PARTITION LIST ALL | | 3 | 78 | 18 (0)| 00:00:01 | 1 | 16 | | | |
| 117 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_ACTVY_FCT | 3 | 78 | 18 (0)| 00:00:01 | 1 | 16 | | | |
| 118 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | |
|*119 | BITMAP INDEX SINGLE VALUE | OPT_ACTVY_FCT_BX6 | | | | | 1 | 16 | | | |
|*120 | INDEX UNIQUE SCAN | OPT_ACTVY_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | | | | |
| 121 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACTVY_FDIM | 1 | 19 | 1 (0)| 00:00:01 | ROWID | ROWID | | | |
| 122 | VIEW PUSHED PREDICATE | | 1 | 39 | | | | | | | |
|*123 | FILTER | | | | | | | | | | |
| 124 | SORT AGGREGATE | | 1 | 30 | | | | | | | |
|*125 | PX COORDINATOR | | | | | | | | | | |
| 126 | PX SEND QC (RANDOM) | :TQ40001 | 1 | 30 | | | | | Q4,01 | P->S | QC (RAND) |
| 127 | SORT AGGREGATE | | 1 | 30 | | | | | Q4,01 | PCWP | |
|*128 | FILTER | | | | | | | | Q4,01 | PCWC | |
|*129 | HASH JOIN | | 1 | 30 | 350 (1)| 00:00:02 | | | Q4,01 | PCWP | |
| 130 | BUFFER SORT | | | | | | | | Q4,01 | PCWC | |
| 131 | PX RECEIVE | | 1 | 13 | 19 (6)| 00:00:01 | | | Q4,01 | PCWP | |
| 132 | PX SEND BROADCAST | :TQ40000 | 1 | 13 | 19 (6)| 00:00:01 | | | | S->P | BROADCAST |
| 133 | VIEW | | 1 | 13 | 19 (6)| 00:00:01 | | | | | |
| 134 | SORT UNIQUE | | 1 | 6 | 18 (6)| 00:00:01 | | | | | |
|*135 | FILTER | | | | | | | | | | |
| 136 | PARTITION LIST ALL | | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
|*137 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | 1 | 6 | 17 (0)| 00:00:01 | 1 | 16 | | | |
| 138 | PX PARTITION LIST ALL | | 912 | 15504 | 330 (1)| 00:00:02 | 1 | 16 | Q4,01 | PCWC | |
| 139 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_PRMTN_PROD_FCT | 912 | 15504 | 330 (1)| 00:00:02 | 1 | 16 | Q4,01 | PCWP | |
| 140 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q4,01 | PCWP | |
|*141 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_PROD_FCT_BX4 | | | | | 1 | 16 | Q4,01 | PCWP | |
|*142 | INDEX RANGE SCAN | OPT_PRMTN_DIM_PK | 1 | | 1 (0)| 00:00:01 | | | | | |
| 143 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_DIM | 1 | 16 | 2 (0)| 00:00:01 | ROWID | ROWID | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
13 - filter(COUNT(*)>0)
15 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
20 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
22 - access("PFCT"."BASE_PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"<>0)
23 - filter("BFCT"."PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
25 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
32 - access("BFCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("BFCT"."PRMTN_SKID"<>0)
33 - access("BFCT"."ACCT_SKID"="PFCT"."ACCT_PRMTN_SKID")
34 - access("BFCT"."WK_SKID"="D"."CAL_MASTR_SKID")
39 - filter("BRAND_BASLN_FCT"."PRMTN_ACCT_SKID"(+)="PFCT"."ACCT_PRMTN_SKID" AND "BFCT"."BUS_UNIT_SKID"="BRAND_BASLN_FCT"."BUS_UNIT_SKID"(+) AND
"BFCT"."PROD_SKID"="BRAND_BASLN_FCT"."PROD_SKID"(+) AND "BFCT"."ACCT_SKID"="BRAND_BASLN_FCT"."PRMTN_ACCT_SKID"(+) AND "BFCT"."MTH_SKID"="BRAND_BASLN_FCT"."DATE_SKID"(+))
41 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
44 - access("PFCT"."BASE_PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"<>0)
46 - filter(COUNT(*)>0)
52 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
58 - access("EPOS_BFCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("EPOS_BFCT"."PRMTN_SKID"<>0)
59 - access("EPOS_BFCT"."WK_SKID"="D"."CAL_MASTR_SKID")
64 - filter("EPOS_BFCT"."BUS_UNIT_SKID"="BRAND_BASLN_FCT"."BUS_UNIT_SKID"(+) AND "EPOS_BFCT"."PROD_SKID"="BRAND_BASLN_FCT"."PROD_SKID"(+) AND
"EPOS_BFCT"."ACCT_SKID"="BRAND_BASLN_FCT"."PRMTN_ACCT_SKID"(+) AND "EPOS_BFCT"."MTH_SKID"="BRAND_BASLN_FCT"."DATE_SKID"(+))
67 - access("EPOS_BFCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
69 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*),SUM("EPOS_FCT"."VOL_SU")),0))>0)
71 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
74 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
78 - access("EPOS_FCT"."PROD_SKID"="PRMTN_PROD_FCT"."PROD_SKID")
79 - access("PRMTN_PROD_FCT"."PRMTN_SKID"="PRMTN_FDIM"."PRMTN_SKID")
84 - access("PRMTN_FDIM"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PRMTN_FDIM"."PRMTN_SKID"<>0)
88 - access("PRMTN_PROD_FCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PRMTN_PROD_FCT"."PRMTN_SKID"<>0)
95 - filter("CAL_MASTR"."DAY_DATE">="PRMTN_FDIM"."PGM_START_DATE" AND "CAL_MASTR"."DAY_DATE"<="PRMTN_FDIM"."PGM_END_DATE")
96 - access("EPOS_FCT"."DATE_SKID"="CAL_MASTR"."CAL_MASTR_SKID")
97 - filter("CAL_MASTR"."DAY_DATE"<="HIER"."ASDN_EFF_END_DATE")
98 - access("EPOS_FCT"."ACCT_SKID"="HIER"."ACCT_SKID" AND "HIER"."ASSOC_ACCT_SKID"="PRMTN_FDIM"."ACCT_SKID" AND "CAL_MASTR"."DAY_DATE">="HIER"."ASDN_EFF_START_DATE")
100 - filter(0<>"PRMTN_PROD_FCT"."PRMTN_SKID")
103 - access("PFCT"."BASE_PRMTN_SKID"="PRMTN_PROD_FCT"."PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"<>0)
105 - filter(COUNT(*)>0)
107 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
110 - access("OPT_ACTVY_FCT"."PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
113 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
115 - access("PFCT"."BASE_PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"<>0)
119 - access("OPT_ACTVY_FCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("OPT_ACTVY_FCT"."PRMTN_SKID"<>0)
120 - access("OPT_ACTVY_FCT"."ACTVY_SKID"="OPT_ACTVY_FDIM"."ACTVY_SKID")
123 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*),SUM("PRMTN_PROD_FCT"."INCRM_IN_GIV_AMT"*(100-"PRMTN_PROD_FCT"."TRADE_TERM_PCT")/100),SUM("PRMTN_PROD_FCT"."TOT_IN_GIV_AMT"*(
100-"PRMTN_PROD_FCT"."TRADE_TERM_PCT")/100),SUM("PRMTN_PROD_FCT"."ACTL_GIV_AMT"*(100-"PRMTN_PROD_FCT"."TRADE_TERM_PCT")/100)),0))>0)
125 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
128 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
129 - access("PRMTN_PROD_FCT"."PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
135 - filter(0<>"PFCT"."BASE_PRMTN_SKID")
137 - access("PFCT"."BASE_PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"<>0)
141 - access("PRMTN_PROD_FCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PRMTN_PROD_FCT"."PRMTN_SKID"<>0)
142 - access("PDIM"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PDIM"."PRMTN_SKID"<>0)
217 rows selected.
Elapsed: 00:00:08.32
This is the wrong execution plan, if using this execution plan ,SQL will run a long time(16hours)
please notice that CBO use VIEW PUSHED PREDICATE ,this is controlled by hidden parameter _optimizer_extend_jppd_view_types
And it’s a new CBO feature in 11g.
SQL> alter session set "_optimizer_extend_jppd_view_types"=false;
Session altered.
Execution plan the SQL, execution plan as below:
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 85794673
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 168K| 50M| | 274K (11)| 00:14:17 | | | | | |
| 1 | LOAD AS SELECT | ROBINSON | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ20023 | 168K| 50M| | 273K (11)| 00:14:16 | | | Q2,23 | P->S | QC (RAND) |
|* 4 | HASH JOIN | | 168K| 50M| | 273K (11)| 00:14:16 | | | Q2,23 | PCWP | |
| 5 | BUFFER SORT | | | | | | | | | Q2,23 | PCWC | |
| 6 | PX RECEIVE | | 173K| 2706K| | 4292 (2)| 00:00:14 | | | Q2,23 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ20007 | 173K| 2706K| | 4292 (2)| 00:00:14 | | | | S->P | BROADCAST |
| 8 | PARTITION LIST ALL | | 173K| 2706K| | 4292 (2)| 00:00:14 | 1 | 16 | | | |
|* 9 | TABLE ACCESS FULL | OPT_PRMTN_DIM | 173K| 2706K| | 4292 (2)| 00:00:14 | 1 | 16 | | | |
|* 10 | HASH JOIN | | 168K| 47M| | 269K (11)| 00:14:02 | | | Q2,23 | PCWP | |
| 11 | BUFFER SORT | | | | | | | | | Q2,23 | PCWC | |
| 12 | PX RECEIVE | | 168K| 2145K| | 940 (6)| 00:00:03 | | | Q2,23 | PCWP | |
| 13 | PX SEND BROADCAST | :TQ20008 | 168K| 2145K| | 940 (6)| 00:00:03 | | | | S->P | BROADCAST |
| 14 | VIEW | | 168K| 2145K| | 940 (6)| 00:00:03 | | | | | |
| 15 | HASH UNIQUE | | 168K| 990K| 2000K| 891 (6)| 00:00:03 | | | | | |
| 16 | PARTITION LIST ALL | | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
| 17 | BITMAP CONVERSION TO ROWIDS | | 168K| 990K| | 570 (2)| 00:00:02 | | | | | |
|* 18 | BITMAP INDEX FAST FULL SCAN | OPT_PRMTN_FCT_BX2 | | | | | | 1 | 16 | | | |
|* 19 | HASH JOIN RIGHT OUTER | | 168K| 45M| | 268K (11)| 00:13:59 | | | Q2,23 | PCWP | |
| 20 | PX RECEIVE | | 718 | 56004 | | 22203 (8)| 00:01:10 | | | Q2,23 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ20020 | 718 | 56004 | | 22203 (8)| 00:01:10 | | | Q2,20 | P->P | BROADCAST |
| 22 | VIEW | | 718 | 56004 | | 22203 (8)| 00:01:10 | | | Q2,20 | PCWP | |
| 23 | HASH GROUP BY | | 718 | 78262 | | 20827 (8)| 00:01:06 | | | Q2,20 | PCWP | |
| 24 | PX RECEIVE | | 718 | 78262 | | 20827 (8)| 00:01:06 | | | Q2,20 | PCWP | |
| 25 | PX SEND HASH | :TQ20017 | 718 | 78262 | | 20827 (8)| 00:01:06 | | | Q2,17 | P->P | HASH |
| 26 | HASH GROUP BY | | 718 | 78262 | | 20827 (8)| 00:01:06 | | | Q2,17 | PCWP | |
|* 27 | HASH JOIN OUTER | | 1226K| 127M| | 20646 (8)| 00:01:05 | | | Q2,17 | PCWP | |
| 28 | PX RECEIVE | | 1226K| 99M| | 11592 (6)| 00:00:37 | | | Q2,17 | PCWP | |
| 29 | PX SEND PARTITION (KEY) | :TQ20015 | 1226K| 99M| | 11592 (6)| 00:00:37 | | | Q2,15 | P->P | PART (KEY) |
|* 30 | HASH JOIN | | 1226K| 99M| | 11592 (6)| 00:00:37 | | | Q2,15 | PCWP | |
| 31 | BUFFER SORT | | | | | | | | | Q2,15 | PCWC | |
| 32 | PX RECEIVE | | 168K| 990K| | 940 (6)| 00:00:03 | | | Q2,15 | PCWP | |
| 33 | PX SEND BROADCAST | :TQ20004 | 168K| 990K| | 940 (6)| 00:00:03 | | | | S->P | BROADCAST |
| 34 | VIEW | | 168K| 990K| | 940 (6)| 00:00:03 | | | | | |
| 35 | HASH UNIQUE | | 168K| 990K| 2000K| 891 (6)| 00:00:03 | | | | | |
| 36 | PARTITION LIST ALL | | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
|* 37 | BITMAP INDEX FAST FULL SCAN | OPT_PRMTN_FCT_BX2 | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
| 38 | VIEW | | 1849K| 139M| | 10620 (6)| 00:00:34 | | | Q2,15 | PCWP | |
| 39 | HASH GROUP BY | | 1849K| 111M| 156M| 9400 (6)| 00:00:30 | | | Q2,15 | PCWP | |
| 40 | PX RECEIVE | | 1849K| 111M| | 1909 (11)| 00:00:06 | | | Q2,15 | PCWP | |
| 41 | PX SEND HASH | :TQ20013 | 1849K| 111M| | 1909 (11)| 00:00:06 | | | Q2,13 | P->P | HASH |
|* 42 | HASH JOIN | | 1849K| 111M| | 1909 (11)| 00:00:06 | | | Q2,13 | PCWP | |
| 43 | BUFFER SORT | | | | | | | | | Q2,13 | PCWC | |
| 44 | PX RECEIVE | | 37190 | 363K| | 39 (6)| 00:00:01 | | | Q2,13 | PCWP | |
| 45 | PX SEND BROADCAST | :TQ20002 | 37190 | 363K| | 39 (6)| 00:00:01 | | | | S->P | BROADCAST |
| 46 | TABLE ACCESS FULL | OPT_CAL_MASTR_MV01 | 37190 | 363K| | 39 (6)| 00:00:01 | | | | | |
| 47 | PX BLOCK ITERATOR | | 1849K| 93M| | 1840 (10)| 00:00:06 | 1 | 16 | Q2,13 | PCWC | |
|* 48 | TABLE ACCESS FULL | OPT_BASLN_FCT | 1849K| 93M| | 1840 (10)| 00:00:06 | 1 | 16 | Q2,13 | PCWP | |
| 49 | PX PARTITION LIST ALL | | 7927K| 181M| | 8906 (8)| 00:00:28 | 1 | 16 | Q2,17 | PCWC | |
| 50 | TABLE ACCESS FULL | OPT_BRAND_BASLN_FCT | 7927K| 181M| | 8906 (8)| 00:00:28 | 1 | 16 | Q2,17 | PCWP | |
|* 51 | HASH JOIN RIGHT OUTER | | 168K| 33M| | 246K (11)| 00:12:50 | | | Q2,23 | PCWP | |
| 52 | BUFFER SORT | | | | | | | | | Q2,23 | PCWC | |
| 53 | PX RECEIVE | | 1 | 39 | | 570 (8)| 00:00:02 | | | Q2,23 | PCWP | |
| 54 | PX SEND BROADCAST | :TQ20009 | 1 | 39 | | 570 (8)| 00:00:02 | | | | S->P | BROADCAST |
| 55 | VIEW | | 1 | 39 | | 570 (8)| 00:00:02 | | | | | |
| 56 | HASH GROUP BY | | 1 | 113 | | 570 (8)| 00:00:02 | | | | | |
| 57 | NESTED LOOPS SEMI | | 1 | 113 | | 570 (8)| 00:00:02 | | | | | |
| 58 | NESTED LOOPS OUTER | | 1 | 107 | | 561 (8)| 00:00:02 | | | | | |
| 59 | VIEW | | 1 | 83 | | 4 (25)| 00:00:01 | | | | | |
| 60 | HASH GROUP BY | | 1 | 101 | | 3 (34)| 00:00:01 | | | | | |
| 61 | NESTED LOOPS | | | | | | | | | | | |
| 62 | NESTED LOOPS | | 1 | 101 | | 2 (0)| 00:00:01 | | | | | |
| 63 | PARTITION LIST ALL | | 1 | 91 | | 2 (0)| 00:00:01 | 1 | 16 | | | |
|* 64 | TABLE ACCESS FULL | OPT_EPOS_POST_EVENT_BASLN_FCT | 1 | 91 | | 2 (0)| 00:00:01 | 1 | 16 | | | |
|* 65 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_MV01_PK | 1 | | | 0 (0)| 00:00:01 | | | | | |
| 66 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_MV01 | 1 | 10 | | 0 (0)| 00:00:01 | | | | | |
| 67 | PX COORDINATOR | | | | | | | | | | | |
| 68 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 24 | | 557 (8)| 00:00:02 | | | Q1,00 | P->S | QC (RAND) |
| 69 | PX BLOCK ITERATOR | | 1 | 24 | | 557 (8)| 00:00:02 | KEY | KEY | Q1,00 | PCWC | |
|* 70 | TABLE ACCESS FULL | OPT_BRAND_BASLN_FCT | 1 | 24 | | 557 (8)| 00:00:02 | KEY | KEY | Q1,00 | PCWP | |
| 71 | PARTITION LIST ALL | | 1 | 6 | | 570 (8)| 00:00:02 | 1 | 16 | | | |
| 72 | BITMAP CONVERSION TO ROWIDS | | 1 | 6 | | 570 (8)| 00:00:02 | | | | | |
|* 73 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_FCT_BX2 | | | | | | 1 | 16 | | | |
|* 74 | HASH JOIN RIGHT OUTER | | 168K| 26M| | 245K (11)| 00:12:48 | | | Q2,23 | PCWP | |
| 75 | PX RECEIVE | | 1 | 26 | | 110K (7)| 00:05:45 | | | Q2,23 | PCWP | |
| 76 | PX SEND BROADCAST | :TQ20021 | 1 | 26 | | 110K (7)| 00:05:45 | | | Q2,21 | P->P | BROADCAST |
| 77 | VIEW | | 1 | 26 | | 110K (7)| 00:05:45 | | | Q2,21 | PCWP | |
| 78 | HASH GROUP BY | | 1 | 19 | | 110K (7)| 00:05:45 | | | Q2,21 | PCWP | |
| 79 | PX RECEIVE | | 1 | 19 | | 110K (7)| 00:05:45 | | | Q2,21 | PCWP | |
| 80 | PX SEND HASH | :TQ20018 | 1 | 19 | | 110K (7)| 00:05:45 | | | Q2,18 | P->P | HASH |
| 81 | HASH GROUP BY | | 1 | 19 | | 110K (7)| 00:05:45 | | | Q2,18 | PCWP | |
| 82 | VIEW | VM_NWVW_1 | 614 | 11666 | | 110K (7)| 00:05:45 | | | Q2,18 | PCWP | |
| 83 | HASH UNIQUE | | 614 | 101K| | 110K (7)| 00:05:45 | | | Q2,18 | PCWP | |
| 84 | PX RECEIVE | | 614 | 101K| | 110K (7)| 00:05:45 | | | Q2,18 | PCWP | |
| 85 | PX SEND HASH | :TQ20016 | 614 | 101K| | 110K (7)| 00:05:45 | | | Q2,16 | P->P | HASH |
|* 86 | HASH JOIN | | 614 | 101K| | 110K (7)| 00:05:45 | | | Q2,16 | PCWP | |
|* 87 | HASH JOIN | | 614 | 97K| | 109K (7)| 00:05:43 | | | Q2,16 | PCWP | |
| 88 | PX RECEIVE | | 43328 | 5881K| | 9831 (3)| 00:00:31 | | | Q2,16 | PCWP | |
| 89 | PX SEND BROADCAST | :TQ20014 | 43328 | 5881K| | 9831 (3)| 00:00:31 | | | Q2,14 | P->P | BROADCAST |
|* 90 | HASH JOIN | | 43328 | 5881K| | 9831 (3)| 00:00:31 | | | Q2,14 | PCWP | |
| 91 | PX RECEIVE | | 6345 | 613K| | 5544 (3)| 00:00:18 | | | Q2,14 | PCWP | |
| 92 | PX SEND HASH | :TQ20012 | 6345 | 613K| | 5544 (3)| 00:00:18 | | | Q2,12 | P->P | HASH |
|* 93 | HASH JOIN | | 6345 | 613K| | 5544 (3)| 00:00:18 | | | Q2,12 | PCWP | |
|* 94 | HASH JOIN | | 21392 | 1545K| | 5502 (3)| 00:00:18 | | | Q2,12 | PCWP | |
| 95 | BUFFER SORT | | | | | | | | | Q2,12 | PCWC | |
| 96 | PX RECEIVE | | 3039 | 100K| | 298 (2)| 00:00:01 | | | Q2,12 | PCWP | |
| 97 | PX SEND BROADCAST | :TQ20000 | 3039 | 100K| | 298 (2)| 00:00:01 | | | | S->P | BROADCAST |
| 98 | PARTITION RANGE ALL | | 3039 | 100K| | 298 (2)| 00:00:01 | 1 | 21 | | | |
| 99 | PARTITION LIST ALL | | 3039 | 100K| | 298 (2)| 00:00:01 | 1 | 5 | | | |
| 100 | TABLE ACCESS FULL | OPT_EPOS_FCT | 3039 | 100K| | 298 (2)| 00:00:01 | 1 | 105 | | | |
| 101 | PX BLOCK ITERATOR | | 2043K| 77M| | 5173 (2)| 00:00:17 | 1 | 16 | Q2,12 | PCWC | |
| 102 | TABLE ACCESS FULL | OPT_ACCT_ASDN_TYPE2_DIM | 2043K| 77M| | 5173 (2)| 00:00:17 | 1 | 16 | Q2,12 | PCWP | |
| 103 | BUFFER SORT | | | | | | | | | Q2,12 | PCWC | |
| 104 | PX RECEIVE | | 37190 | 907K| | 40 (8)| 00:00:01 | | | Q2,12 | PCWP | |
| 105 | PX SEND BROADCAST | :TQ20001 | 37190 | 907K| | 40 (8)| 00:00:01 | | | | S->P | BROADCAST |
| 106 | TABLE ACCESS FULL | OPT_CAL_MASTR_MV01 | 37190 | 907K| | 40 (8)| 00:00:01 | | | | | |
| 107 | BUFFER SORT | | | | | | | | | Q2,14 | PCWC | |
| 108 | PX RECEIVE | | 173K| 6783K| | 4283 (2)| 00:00:14 | | | Q2,14 | PCWP | |
| 109 | PX SEND HASH | :TQ20003 | 173K| 6783K| | 4283 (2)| 00:00:14 | | | | S->P | HASH |
| 110 | PARTITION LIST ALL | | 173K| 6783K| | 4283 (2)| 00:00:14 | 1 | 16 | | | |
|*111 | TABLE ACCESS FULL | OPT_PRMTN_FDIM | 173K| 6783K| | 4283 (2)| 00:00:14 | 1 | 16 | | | |
| 112 | PX BLOCK ITERATOR | | 55M| 1258M| | 98856 (6)| 00:05:10 | 1 | 16 | Q2,16 | PCWC | |
|*113 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FCT | 55M| 1258M| | 98856 (6)| 00:05:10 | 1 | 16 | Q2,16 | PCWP | |
| 114 | BUFFER SORT | | | | | | | | | Q2,16 | PCWC | |
| 115 | PX RECEIVE | | 168K| 990K| | 570 (2)| 00:00:02 | | | Q2,16 | PCWP | |
| 116 | PX SEND BROADCAST | :TQ20005 | 168K| 990K| | 570 (2)| 00:00:02 | | | | S->P | BROADCAST |
| 117 | PARTITION LIST ALL | | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
| 118 | BITMAP CONVERSION TO ROWIDS | | 168K| 990K| | 570 (2)| 00:00:02 | | | | | |
|*119 | BITMAP INDEX FAST FULL SCAN| OPT_PRMTN_FCT_BX2 | | | | | | 1 | 16 | | | |
|*120 | HASH JOIN RIGHT OUTER | | 168K| 22M| | 135K (15)| 00:07:04 | | | Q2,23 | PCWP | |
| 121 | BUFFER SORT | | | | | | | | | Q2,23 | PCWC | |
| 122 | PX RECEIVE | | 1 | 39 | | 6665 (6)| 00:00:21 | | | Q2,23 | PCWP | |
| 123 | PX SEND BROADCAST | :TQ20010 | 1 | 39 | | 6665 (6)| 00:00:21 | | | | S->P | BROADCAST |
| 124 | VIEW | | 1 | 39 | | 6665 (6)| 00:00:21 | | | | | |
| 125 | HASH GROUP BY | | 1 | 51 | | 6351 (7)| 00:00:20 | | | | | |
|*126 | HASH JOIN | | 291K| 14M| 8832K| 6278 (6)| 00:00:20 | | | | | |
| 127 | PARTITION LIST ALL | | 291K| 5405K| | 3641 (5)| 00:00:12 | 1 | 16 | | | |
| 128 | TABLE ACCESS FULL | OPT_ACTVY_FDIM | 291K| 5405K| | 3641 (5)| 00:00:12 | 1 | 16 | | | |
|*129 | HASH JOIN | | 291K| 9101K| 2976K| 2292 (6)| 00:00:08 | | | | | |
| 130 | VIEW | | 168K| 990K| | 940 (6)| 00:00:03 | | | | | |
| 131 | HASH UNIQUE | | 168K| 990K| 2000K| 891 (6)| 00:00:03 | | | | | |
| 132 | PARTITION LIST ALL | | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
|*133 | BITMAP INDEX FAST FULL SCAN | OPT_PRMTN_FCT_BX2 | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
| 134 | PARTITION LIST ALL | | 291K| 7395K| | 1124 (6)| 00:00:04 | 1 | 16 | | | |
|*135 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 291K| 7395K| | 1124 (6)| 00:00:04 | 1 | 16 | | | |
|*136 | HASH JOIN RIGHT OUTER | | 168K| 16M| | 128K (16)| 00:06:43 | | | Q2,23 | PCWP | |
| 137 | PX RECEIVE | | 1 | 52 | | 127K (16)| 00:06:39 | | | Q2,23 | PCWP | |
| 138 | PX SEND HASH | :TQ20022 | 1 | 52 | | 127K (16)| 00:06:39 | | | Q2,22 | P->P | HASH |
| 139 | VIEW | | 1 | 52 | | 127K (16)| 00:06:39 | | | Q2,22 | PCWP | |
| 140 | HASH GROUP BY | | 1 | 23 | | 113K (18)| 00:05:54 | | | Q2,22 | PCWP | |
| 141 | PX RECEIVE | | 1 | 23 | | 113K (18)| 00:05:54 | | | Q2,22 | PCWP | |
| 142 | PX SEND HASH | :TQ20019 | 1 | 23 | | 113K (18)| 00:05:54 | | | Q2,19 | P->P | HASH |
| 143 | HASH GROUP BY | | 1 | 23 | | 113K (18)| 00:05:54 | | | Q2,19 | PCWP | |
|*144 | HASH JOIN | | 55M| 1206M| | 103K (9)| 00:05:23 | | | Q2,19 | PCWP | |
| 145 | BUFFER SORT | | | | | | | | | Q2,19 | PCWC | |
| 146 | PX RECEIVE | | 168K| 990K| | 940 (6)| 00:00:03 | | | Q2,19 | PCWP | |
| 147 | PX SEND BROADCAST | :TQ20006 | 168K| 990K| | 940 (6)| 00:00:03 | | | | S->P | BROADCAST |
| 148 | VIEW | | 168K| 990K| | 940 (6)| 00:00:03 | | | | | |
| 149 | HASH UNIQUE | | 168K| 990K| 2000K| 891 (6)| 00:00:03 | | | | | |
| 150 | PARTITION LIST ALL | | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
|*151 | BITMAP INDEX FAST FULL SCAN | OPT_PRMTN_FCT_BX2 | 168K| 990K| | 570 (2)| 00:00:02 | 1 | 16 | | | |
| 152 | PX BLOCK ITERATOR | | 55M| 891M| | 101K (9)| 00:05:17 | 1 | 16 | Q2,19 | PCWC | |
|*153 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FCT | 55M| 891M| | 101K (9)| 00:05:17 | 1 | 16 | Q2,19 | PCWP | |
| 154 | BUFFER SORT | | | | | | | | | Q2,23 | PCWC | |
| 155 | PX RECEIVE | | 168K| 8085K| | 1279 (7)| 00:00:05 | | | Q2,23 | PCWP | |
| 156 | PX SEND HASH | :TQ20011 | 168K| 8085K| | 1279 (7)| 00:00:05 | | | | S->P | HASH |
| 157 | PARTITION LIST ALL | | 168K| 8085K| | 1279 (7)| 00:00:05 | 1 | 16 | | | |
|*158 | TABLE ACCESS FULL | OPT_PRMTN_FCT | 168K| 8085K| | 1279 (7)| 00:00:05 | 1 | 16 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PDIM"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
9 - filter("PDIM"."PRMTN_SKID"<>0)
10 - access("PFCT"."BASE_PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
18 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
19 - access("PFCT"."BASE_PRMTN_SKID"="BASLN"."PRMTN_SKID"(+) AND "PFCT"."ACCT_PRMTN_SKID"="BASLN"."ACCT_SKID"(+))
27 - access("BFCT"."BUS_UNIT_SKID"="BRAND_BASLN_FCT"."BUS_UNIT_SKID"(+) AND "BFCT"."PROD_SKID"="BRAND_BASLN_FCT"."PROD_SKID"(+) AND
"BFCT"."ACCT_SKID"="BRAND_BASLN_FCT"."PRMTN_ACCT_SKID"(+) AND "BFCT"."MTH_SKID"="BRAND_BASLN_FCT"."DATE_SKID"(+))
30 - access("BFCT"."PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
37 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
42 - access("BFCT"."WK_SKID"="D"."CAL_MASTR_SKID")
48 - filter("BFCT"."PRMTN_SKID"<>0)
51 - access("PFCT"."BASE_PRMTN_SKID"="EPOS_BASLN"."PRMTN_SKID"(+))
64 - filter("EPOS_BFCT"."PRMTN_SKID"<>0)
65 - access("EPOS_BFCT"."WK_SKID"="D"."CAL_MASTR_SKID")
70 - filter("EPOS_BFCT"."BUS_UNIT_SKID"="BRAND_BASLN_FCT"."BUS_UNIT_SKID"(+) AND "EPOS_BFCT"."PROD_SKID"="BRAND_BASLN_FCT"."PROD_SKID"(+) AND
"EPOS_BFCT"."ACCT_SKID"="BRAND_BASLN_FCT"."PRMTN_ACCT_SKID"(+) AND "EPOS_BFCT"."MTH_SKID"="BRAND_BASLN_FCT"."DATE_SKID"(+))
73 - access("EPOS_BFCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
filter("PFCT"."BASE_PRMTN_SKID"<>0)
74 - access("PFCT"."BASE_PRMTN_SKID"="EPOS_FCT"."PRMTN_SKID"(+))
86 - access("PRMTN_PROD_FCT"."PRMTN_SKID"="PFCT"."BASE_PRMTN_SKID")
87 - access("EPOS_FCT"."PROD_SKID"="PRMTN_PROD_FCT"."PROD_SKID" AND "PRMTN_PROD_FCT"."PRMTN_SKID"="PRMTN_FDIM"."PRMTN_SKID")
90 - access("HIER"."ASSOC_ACCT_SKID"="PRMTN_FDIM"."ACCT_SKID")
filter("CAL_MASTR"."DAY_DATE">="PRMTN_FDIM"."PGM_START_DATE" AND "CAL_MASTR"."DAY_DATE"<="PRMTN_FDIM"."PGM_END_DATE")
93 - access("EPOS_FCT"."DATE_SKID"="CAL_MASTR"."CAL_MASTR_SKID")
filter("CAL_MASTR"."DAY_DATE">="HIER"."ASDN_EFF_START_DATE" AND "CAL_MASTR"."DAY_DATE"<="HIER"."ASDN_EFF_END_DATE")
94 - access("EPOS_FCT"."ACCT_SKID"="HIER"."ACCT_SKID")
111 - filter("PRMTN_FDIM"."PRMTN_SKID"<>0)
113 - filter("PRMTN_PROD_FCT"."PRMTN_SKID"<>0 AND SYS_OP_BLOOM_FILTER(:BF0000,"PRMTN_PROD_FCT"."PROD_SKID","PRMTN_PROD_FCT"."PRMTN_SKID"))
119 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
120 - access("PFCT"."BASE_PRMTN_SKID"="ACTVY"."PRMTN_SKID"(+))
126 - access("OPT_ACTVY_FCT"."ACTVY_SKID"="OPT_ACTVY_FDIM"."ACTVY_SKID")
129 - access("OPT_ACTVY_FCT"."PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
133 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
135 - filter("OPT_ACTVY_FCT"."PRMTN_SKID"<>0)
136 - access("PFCT"."BASE_PRMTN_SKID"="PRMTN_PROD"."PRMTN_SKID"(+))
144 - access("PRMTN_PROD_FCT"."PRMTN_SKID"="PRMTN_FLT"."PRMTN_SKID")
151 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
153 - filter("PRMTN_PROD_FCT"."PRMTN_SKID"<>0)
158 - filter("PFCT"."BASE_PRMTN_SKID"<>0)
208 rows selected.
Elapsed: 00:00:07.27
This is the right execution plan , SQL can be finished within 5minutes. I’ve met this issue many times, last week , during Optima APAC cut over,I also met this issue, so it’s necessary to point it out.
Here is another example for this bug:
SQL> alter session set "_optimizer_extend_jppd_view_types"=false;
Session altered.
SQL> explain plan for MERGE INTO (SELECT *
2 FROM opt_prmtn_fct
3 WHERE base_prmtn_skid <> 0
4 ) trgt
5 USING ( SELECT pd.prmtn_skid AS prmtn_skid
6 , SUM (pd.actl_tot_qty_amt) AS actl_tot_su_amt
7 , SUM ( (CASE
8 WHEN ( (NVL (p.su_factr, 0) * NVL (pd.actl_tot_qty_amt, 0)
9 - NVL (pd.estmt_basln_case_amt, 0)) < 0)
10 THEN
11 0
12 ELSE
13 ( (NVL (p.su_factr, 0) * NVL (pd.actl_tot_qty_amt, 0))
14 - NVL (pd.estmt_basln_case_amt, 0))
15 END
16 + CASE
17 WHEN (NVL (pd.post_prmtn_vol_adjmt_su_amt, 0) = 0)
18 THEN
19 0
20 ELSE
21 (NVL (pd.post_prmtn_vol_adjmt_su_amt, 0)
22 - NVL (pd.estmt_basln_case_amt, 0))
23 END)
24 * pd.giv_conv_factr_amt)
25 AS actl_incrm_niv_amt
26 FROM opt_prmtn_prod_fct pd
27 , opt_prod_dim p
28 WHERE pd.prod_skid = p.prod_skid
29 AND pd.prmtn_skid <> 0
30 GROUP BY pd.prmtn_skid) srce
31 ON (srce.prmtn_skid = trgt.base_prmtn_skid)
32 WHEN MATCHED
33 THEN
34 UPDATE SET trgt.actl_tot_su_amt = srce.actl_tot_su_amt
35 , trgt.actl_incrm_niv_amt = srce.actl_incrm_niv_amt;
Explained.
Elapsed: 00:00:01.65
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4142491988
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 38 | 113K (16)| 00:05:56 | | | | | |
| 1 | MERGE | OPT_PRMTN_FCT | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 244 | 113K (16)| 00:05:56 | | | Q1,04 | P->S | QC (RAND) |
| 4 | VIEW | | | | | | | | Q1,04 | PCWP | |
|* 5 | HASH JOIN | | 1 | 244 | 113K (16)| 00:05:56 | | | Q1,04 | PCWP | |
| 6 | PX RECEIVE | | 1 | 39 | 112K (16)| 00:05:52 | | | Q1,04 | PCWP | |
| 7 | PX SEND HASH | :TQ10003 | 1 | 39 | 112K (16)| 00:05:52 | | | Q1,03 | P->P | HASH |
| 8 | VIEW | | 1 | 39 | 112K (16)| 00:05:52 | | | Q1,03 | PCWP | |
| 9 | SORT GROUP BY | | 1 | 33 | 112K (16)| 00:05:52 | | | Q1,03 | PCWP | |
| 10 | PX RECEIVE | | 1 | 33 | 112K (16)| 00:05:52 | | | Q1,03 | PCWP | |
| 11 | PX SEND HASH | :TQ10002 | 1 | 33 | 112K (16)| 00:05:52 | | | Q1,02 | P->P | HASH |
| 12 | SORT GROUP BY | | 1 | 33 | 112K (16)| 00:05:52 | | | Q1,02 | PCWP | |
|* 13 | HASH JOIN | | 55M| 1744M| 102K (8)| 00:05:20 | | | Q1,02 | PCWP | |
| 14 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | 182K| 1780K| 2159 (4)| 00:00:07 | | | Q1,02 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10000 | 182K| 1780K| 2159 (4)| 00:00:07 | | | | S->P | BROADCAST |
| 17 | PARTITION LIST ALL| | 182K| 1780K| 2159 (4)| 00:00:07 | 1 | 16 | | | |
| 18 | TABLE ACCESS FULL| OPT_PROD_DIM | 182K| 1780K| 2159 (4)| 00:00:07 | 1 | 16 | | | |
| 19 | PX BLOCK ITERATOR | | 54M| 1206M| 99315 (7)| 00:05:11 | 1 | 16 | Q1,02 | PCWC | |
|* 20 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FCT | 54M| 1206M| 99315 (7)| 00:05:11 | 1 | 16 | Q1,02 | PCWP | |
| 21 | BUFFER SORT | | | | | | | | Q1,04 | PCWC | |
| 22 | PX RECEIVE | | 172K| 33M| 1286 (8)| 00:00:05 | | | Q1,04 | PCWP | |
| 23 | PX SEND HASH | :TQ10001 | 172K| 33M| 1286 (8)| 00:00:05 | | | | S->P | HASH |
| 24 | PARTITION LIST ALL | | 172K| 33M| 1286 (8)| 00:00:05 | 1 | 16 | | | |
|* 25 | TABLE ACCESS FULL | OPT_PRMTN_FCT | 172K| 33M| 1286 (8)| 00:00:05 | 1 | 16 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SRCE"."PRMTN_SKID"="OPT_PRMTN_FCT"."BASE_PRMTN_SKID")
13 - access("PD"."PROD_SKID"="P"."PROD_SKID")
20 - filter("PD"."PRMTN_SKID"<>0)
25 - filter("BASE_PRMTN_SKID"<>0)
40 rows selected.
SQL> alter session set "_optimizer_extend_jppd_view_types"=true;
Session altered.
Elapsed: 00:00:01.18
SQL> explain plan for MERGE INTO (SELECT *
2 FROM opt_prmtn_fct
3 WHERE base_prmtn_skid <> 0
4 ) trgt
5 USING ( SELECT pd.prmtn_skid AS prmtn_skid
6 , SUM (pd.actl_tot_qty_amt) AS actl_tot_su_amt
7 , SUM ( (CASE
8 WHEN ( (NVL (p.su_factr, 0) * NVL (pd.actl_tot_qty_amt, 0)
9 - NVL (pd.estmt_basln_case_amt, 0)) < 0)
10 THEN
11 0
12 ELSE
13 ( (NVL (p.su_factr, 0) * NVL (pd.actl_tot_qty_amt, 0))
14 - NVL (pd.estmt_basln_case_amt, 0))
15 END
16 + CASE
17 WHEN (NVL (pd.post_prmtn_vol_adjmt_su_amt, 0) = 0)
18 THEN
19 0
20 ELSE
21 (NVL (pd.post_prmtn_vol_adjmt_su_amt, 0)
22 - NVL (pd.estmt_basln_case_amt, 0))
23 END)
24 * pd.giv_conv_factr_amt)
25 AS actl_incrm_niv_amt
26 FROM opt_prmtn_prod_fct pd
27 , opt_prod_dim p
28 WHERE pd.prod_skid = p.prod_skid
29 AND pd.prmtn_skid <> 0
30 GROUP BY pd.prmtn_skid) srce
31 ON (srce.prmtn_skid = trgt.base_prmtn_skid)
32 WHEN MATCHED
33 THEN
34 UPDATE SET trgt.actl_tot_su_amt = srce.actl_tot_su_amt
35 , trgt.actl_incrm_niv_amt = srce.actl_incrm_niv_amt;
Explained.
Elapsed: 00:00:01.32
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1086075059
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 38 | 89110 (1)| 00:04:39 | | |
| 1 | MERGE | OPT_PRMTN_FCT | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 231 | 89110 (1)| 00:04:39 | | |
| 4 | PARTITION LIST ALL | | 172K| 33M| 1286 (8)| 00:00:05 | 1 | 16 |
|* 5 | TABLE ACCESS FULL | OPT_PRMTN_FCT | 172K| 33M| 1286 (8)| 00:00:05 | 1 | 16 |
| 6 | VIEW PUSHED PREDICATE | | 1 | 26 | | | | |
|* 7 | FILTER | | | | | | | |
| 8 | SORT AGGREGATE | | 1 | 33 | | | | |
|* 9 | PX COORDINATOR | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 33 | | | | |
| 11 | SORT AGGREGATE | | 1 | 33 | | | | |
|* 12 | FILTER | | | | | | | |
| 13 | NESTED LOOPS | | | | | | | |
| 14 | NESTED LOOPS | | 929 | 30657 | 1361 (1)| 00:00:05 | | |
| 15 | PX PARTITION LIST ALL | | 922 | 21206 | 333 (1)| 00:00:02 | 1 | 16 |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_PRMTN_PROD_FCT | 922 | 21206 | 333 (1)| 00:00:02 | 1 | 16 |
| 17 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 18 | BITMAP INDEX SINGLE VALUE | OPT_PRMTN_PROD_FCT_BX4 | | | | | 1 | 16 |
|* 19 | INDEX RANGE SCAN | OPT_PROD_DIM_PKN | 1 | | 1 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PROD_DIM | 1 | 10 | 2 (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------------------------
Hidden parameter “_optimizer_extend_jppd_view_types” in 11g R1 and R2 is set to true, and there is no such parameter below 11g.
This hidden parameter is used to join pred pushdown on group-by ,distinct,semi-anti-joined view
Ok, let’s review our SQL
MERGE INTO (SELECT *
FROM opt_prmtn_fct
WHERE base_prmtn_skid <> 0
) trgt
USING ( SELECT pd.prmtn_skid AS prmtn_skid
, SUM (pd.actl_tot_qty_amt) AS actl_tot_su_amt
, SUM ( (CASE
WHEN ( (NVL (p.su_factr, 0) * NVL (pd.actl_tot_qty_amt, 0)
- NVL (pd.estmt_basln_case_amt, 0)) < 0)
THEN
0
ELSE
( (NVL (p.su_factr, 0) * NVL (pd.actl_tot_qty_amt, 0))
- NVL (pd.estmt_basln_case_amt, 0))
END
+ CASE
WHEN (NVL (pd.post_prmtn_vol_adjmt_su_amt, 0) = 0)
THEN
0
ELSE
(NVL (pd.post_prmtn_vol_adjmt_su_amt, 0)
- NVL (pd.estmt_basln_case_amt, 0))
END)
* pd.giv_conv_factr_amt)
AS actl_incrm_niv_amt
FROM opt_prmtn_prod_fct pd
, opt_prod_dim p
WHERE pd.prod_skid = p.prod_skid
AND pd.prmtn_skid <> 0
GROUP BY pd.prmtn_skid) srce ----there have group by and have a view srce
ON (srce.prmtn_skid = trgt.base_prmtn_skid)
WHEN MATCHED
THEN
UPDATE SET trgt.actl_tot_su_amt = srce.actl_tot_su_amt
, trgt.actl_incrm_niv_amt = srce.actl_incrm_niv_amt;
So set hidden parameter “_optimizer_extend_jppd_view_types” to true, CBO will pushdown GROUP BY
| 6 | VIEW PUSHED PREDICATE | | 1 | 26 | | | | |
VIEW PUSHED PREDICATE means CBO choose PUSHDOWN GROUP BY VIEW
3021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
