Here is the explain plan for the same:-
1) First, we lock a distinct EDW_PR_WORK_DB."pseudo table" for read
on a RowHash to prevent global deadlock for
EDW_PR_WORK_DB.SWM_SLS_AGG_NEW.
2) Next, we lock a distinct EDW_PR_WORK_DB."pseudo table" for write
on a RowHash to prevent global deadlock for
EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS.
3) We lock EDW_PR_WORK_DB.SWM_SLS_AGG_NEW for read, and we lock
EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS for write.
4) We do an all-AMPs merge with matched updates into
EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS from
EDW_PR_WORK_DB.SWM_SLS_AGG_NEW with a condition of (
"(EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS.LOC_ID =
EDW_PR_WORK_DB.SWM_SLS_AGG_NEW.LOC_ID) AND
((EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS.GNRC_ITEM_ID =
EDW_PR_WORK_DB.SWM_SLS_AGG_NEW.GNRC_ITEM_ID) AND
(EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS.GNRC_ITEM_LVL_CD =
EDW_PR_WORK_DB.SWM_SLS_AGG_NEW.GNRC_ITEM_LVL_CD ))"). The number
of rows merged is estimated with low confidence to be 95,074,664
rows.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
Hi
As per the explain plan its merging the data with low confidence. you are using PI on multile columns.
Please collect the stats on join columns individually it will solve your performance problem.
ON TGT.LOC_ID = SRC.LOC_ID
AND TGT.GNRC_ITEM_ID = SRC.GNRC_ITEM_ID
AND TGT.GNRC_ITEM_LVL_CD = SRC.GNRC_ITEM_LVL_CD
Thanks,
Venkat
Hello Experts,
Can you please help me in tuning the below MERGE statement where both the tables have same PPI (loc_id, gnrc_item_id, gnrc_item_lvl_cd) having ~95 millions of records:-
MERGE INTO EDW_PR_WORK_DB.F_MOBILE_APP_TD_SLS AS TGT
USING EDW_PR_WORK_DB.SWM_SLS_AGG_NEW AS SRC
ON TGT.LOC_ID = SRC.LOC_ID
AND TGT.GNRC_ITEM_ID = SRC.GNRC_ITEM_ID
AND TGT.GNRC_ITEM_LVL_CD = SRC.GNRC_ITEM_LVL_CD
WHEN MATCHED THEN
UPDATE SET
TYWTD_SC_NSLS_AMT = SRC.TYWTD_SC_NSLS_AMT
,PYWTD_SC_NSLS_AMT = SRC.PYWTD_SC_NSLS_AMT
,TYMTD_SC_NSLS_AMT = SRC.TYMTD_SC_NSLS_AMT
,PYMTD_SC_NSLS_AMT = SRC.PYMTD_SC_NSLS_AMT
,TYQTD_SC_NSLS_AMT = SRC.TYQTD_SC_NSLS_AMT
,PYQTD_SC_NSLS_AMT = SRC.PYQTD_SC_NSLS_AMT
,TYYTD_SC_NSLS_AMT = SRC.TYYTD_SC_NSLS_AMT
,PYYTD_SC_NSLS_AMT = SRC.PYYTD_SC_NSLS_AMT
,TYPWK_SC_NSLS_AMT = SRC.TYPWK_SC_NSLS_AMT
,PYPWK_SC_NSLS_AMT = SRC.PYPWK_SC_NSLS_AMT
,TYPMO_SC_NSLS_AMT = SRC.TYPMO_SC_NSLS_AMT
,PYPMO_SC_NSLS_AMT = SRC.PYPMO_SC_NSLS_AMT
,L12_SC_NET_SLS_AMT = SRC.L12_SC_NET_SLS_AMT
,PYL12_SC_NET_SLS_AMT = SRC.PYL12_SC_NET_SLS_AMT
,TYWTD_CLASNSLS_AMT = SRC.TYWTD_CLASNSLS_AMT
,PYWTD_CLASNSLS_AMT = SRC.PYWTD_CLASNSLS_AMT
,TYMTD_CLASNSLS_AMT = SRC.TYMTD_CLASNSLS_AMT
,PYMTD_CLASNSLS_AMT = SRC.PYMTD_CLASNSLS_AMT
,TYQTD_CLASNSLS_AMT = SRC.TYQTD_CLASNSLS_AMT
,PYQTD_CLASNSLS_AMT = SRC.PYQTD_CLASNSLS_AMT
,TYYTD_CLASNSLS_AMT = SRC.TYYTD_CLASNSLS_AMT
,PYYTD_CLASNSLS_AMT = SRC.PYYTD_CLASNSLS_AMT
,TYPWK_CLASNSLS_AMT = SRC.TYPWK_CLASNSLS_AMT
,PYPWK_CLASNSLS_AMT = SRC.PYPWK_CLASNSLS_AMT
,TYPMO_CLASNSLS_AMT = SRC.TYPMO_CLASNSLS_AMT
,PYPMO_CLASNSLS_AMT = SRC.PYPMO_CLASNSLS_AMT
,L12_CLASS_NET_SLS_AMT = SRC.L12_CLASS_NET_SLS_AMT
,PYL12_CLASS_NET_SLS_AMT = SRC.PYL12_CLASS_NET_SLS_AMT
,TYWTD_DEPTNSLS_AMT = SRC.TYWTD_DEPTNSLS_AMT
,PYWTD_DEPTNSLS_AMT = SRC.PYWTD_DEPTNSLS_AMT
,TYPTD_DEPTNSLS_AMT = SRC.TYPTD_DEPTNSLS_AMT
,PYPTD_DEPTNSLS_AMT = SRC.PYPTD_DEPTNSLS_AMT
,TYQTD_DEPTNSLS_AMT = SRC.TYQTD_DEPTNSLS_AMT
,PYQTD_DEPTNSLS_AMT = SRC.PYQTD_DEPTNSLS_AMT
,TYYTD_DEPTNSLS_AMT = SRC.TYYTD_DEPTNSLS_AMT
,PYYTD_DEPTNSLS_AMT = SRC.PYYTD_DEPTNSLS_AMT
,TYPWK_DEPTNSLS_AMT = SRC.TYPWK_DEPTNSLS_AMT
,PYPWK_DEPTNSLS_AMT = SRC.PYPWK_DEPTNSLS_AMT
,TYPMO_DEPTNSLS_AMT = SRC.TYPMO_DEPTNSLS_AMT
,PYPMO_DEPTNSLS_AMT = SRC.PYPMO_DEPTNSLS_AMT
,L12_DEPT_NET_SLS_AMT = SRC.L12_DEPT_NET_SLS_AMT
,PYL12_DEPT_NET_SLS_AMT = SRC.PYL12_DEPT_NET_SLS_AMT
,TYWTD_STR_NSLS_AMT = SRC.TYWTD_STR_NSLS_AMT
,PYWTD_STR_NSLS_AMT = SRC.PYWTD_STR_NSLS_AMT
,TYPTD_STR_NSLS_AMT = SRC.TYPTD_STR_NSLS_AMT
,PYPTD_STR_NSLS_AMT = SRC.PYPTD_STR_NSLS_AMT
,TYQTD_STR_NSLS_AMT = SRC.TYQTD_STR_NSLS_AMT
,PYQTD_STR_NSLS_AMT = SRC.PYQTD_STR_NSLS_AMT
,TYYTD_STR_NSLS_AMT = SRC.TYYTD_STR_NSLS_AMT
,PYYTD_STR_NSLS_AMT = SRC.PYYTD_STR_NSLS_AMT
,TYPWK_STR_NSLS_AMT = SRC.TYPWK_STR_NSLS_AMT
,PYPWK_STR_NSLS_AMT = SRC.PYPWK_STR_NSLS_AMT
,TYPMO_STR_NSLS_AMT = SRC.TYPMO_STR_NSLS_AMT
,PYPMO_STR_NSLS_AMT = SRC.PYPMO_STR_NSLS_AMT
,L12_STR_NET_SLS_AMT = SRC.L12_STR_NET_SLS_AMT
,PYL12_STR_NET_SLS_AMT = SRC.PYL12_STR_NET_SLS_AMT
,TYWTD_MKNTCO_AMT = SRC.TYWTD_MKNTCO_AMT
,PYWTD_MKNTCO_AMT = SRC.PYWTD_MKNTCO_AMT
,TYPTD_MKNTCO_AMT = SRC.TYPTD_MKNTCO_AMT
,PYPTD_MKNTCO_AMT = SRC.PYPTD_MKNTCO_AMT
,TYQTD_MKNTCO_AMT = SRC.TYQTD_MKNTCO_AMT
,PYQTD_MKNTCO_AMT = SRC.PYQTD_MKNTCO_AMT
,TYYTD_MKNTCO_AMT = SRC.TYYTD_MKNTCO_AMT
,PYYTD_MKNTCO_AMT = SRC.PYYTD_MKNTCO_AMT
,TYPR1WK_MKNTCO_AMT = SRC.TYPR1WK_MKNTCO_AMT
,PYPR1WK_MKNTCO_AMT = SRC.PYPR1WK_MKNTCO_AMT
,TYPR2WK_MKNTCO_AMT = SRC.TYPR2WK_MKNTCO_AMT
,PYPR2WK_MKNTCO_AMT = SRC.PYPR2WK_MKNTCO_AMT
,TYPR3WK_MKNTCO_AMT = SRC.TYPR3WK_MKNTCO_AMT
,PYPR3WK_MKNTCO_AMT = SRC.PYPR3WK_MKNTCO_AMT
,TYPR4WK_MKNTCO_AMT = SRC.TYPR4WK_MKNTCO_AMT
,PYPR4WK_MKNTCO_AMT = SRC.PYPR4WK_MKNTCO_AMT
,TYPR5WK_MKNTCO_AMT = SRC.TYPR5WK_MKNTCO_AMT
,PYPR5WK_MKNTCO_AMT = SRC.PYPR5WK_MKNTCO_AMT
,TYPR6WK_MKNTCO_AMT = SRC.TYPR6WK_MKNTCO_AMT
,PYPR6WK_MKNTCO_AMT = SRC.PYPR6WK_MKNTCO_AMT
,TYPR1PD_MKNTCO_AMT = SRC.TYPR1PD_MKNTCO_AMT
,PYPR1PD_MKNTCO_AMT = SRC.PYPR1PD_MKNTCO_AMT
,TYPR2PD_MKNTCO_AMT = SRC.TYPR2PD_MKNTCO_AMT
,PYPR2PD_MKNTCO_AMT = SRC.PYPR2PD_MKNTCO_AMT
,TYPR3PD_MKNTCO_AMT = SRC.TYPR3PD_MKNTCO_AMT
,PYPR3PD_MKNTCO_AMT = SRC.PYPR3PD_MKNTCO_AMT
,TYPR4PD_MKNTCO_AMT = SRC.TYPR4PD_MKNTCO_AMT
,PYPR4PD_MKNTCO_AMT = SRC.PYPR4PD_MKNTCO_AMT
,TYPR5PD_MKNTCO_AMT = SRC.TYPR5PD_MKNTCO_AMT
,PYPR5PD_MKNTCO_AMT = SRC.PYPR5PD_MKNTCO_AMT
,TYPR6PD_MKNTCO_AMT = SRC.TYPR6PD_MKNTCO_AMT
,PYPR6PD_MKNTCO_AMT = SRC.PYPR6PD_MKNTCO_AMT
,L12_MKT_NET_COMP_AMT = SRC.L12_MKT_NET_COMP_AMT
,PYL12_MKT_NET_COMP_AMT = SRC.PYL12_MKT_NET_COMP_AMT
,TYWTD_DSTCO_AMT = SRC.TYWTD_DSTCO_AMT
,PYWTD_DSTCO_AMT = SRC.PYWTD_DSTCO_AMT
,TYPTD_DSTCO_AMT = SRC.TYPTD_DSTCO_AMT
,PYPTD_DSTCO_AMT = SRC.PYPTD_DSTCO_AMT
,TYQTD_DSTCO_AMT = SRC.TYQTD_DSTCO_AMT
,PYQTD_DSTCO_AMT = SRC.PYQTD_DSTCO_AMT
,TYYTD_DSTCO_AMT = SRC.TYYTD_DSTCO_AMT
,PYYTD_DSTCO_AMT = SRC.PYYTD_DSTCO_AMT
,TYPR1WK_DSTCO_AMT = SRC.TYPR1WK_DSTCO_AMT
,PYPR1WK_DSTCO_AMT = SRC.PYPR1WK_DSTCO_AMT
,TYPR2WK_DSTCO_AMT = SRC.TYPR2WK_DSTCO_AMT
,PYPR2WK_DSTCO_AMT = SRC.PYPR2WK_DSTCO_AMT
,TYPR3WK_DSTCO_AMT = SRC.TYPR3WK_DSTCO_AMT
,PYPR3WK_DSTCO_AMT = SRC.PYPR3WK_DSTCO_AMT
,TYPR4WK_DSTCO_AMT = SRC.TYPR4WK_DSTCO_AMT
,PYPR4WK_DSTCO_AMT = SRC.PYPR4WK_DSTCO_AMT
,TYPR5WK_DSTCO_AMT = SRC.TYPR5WK_DSTCO_AMT
,PYPR5WK_DSTCO_AMT = SRC.PYPR5WK_DSTCO_AMT
,TYPR6WK_DSTCO_AMT = SRC.TYPR6WK_DSTCO_AMT
,PYPR6WK_DSTCO_AMT = SRC.PYPR6WK_DSTCO_AMT
,TYPR1PD_DSTCO_AMT = SRC.TYPR1PD_DSTCO_AMT
,PYPR1PD_DSTCO_AMT = SRC.PYPR1PD_DSTCO_AMT
,TYPR2PD_DSTCO_AMT = SRC.TYPR2PD_DSTCO_AMT
,PYPR2PD_DSTCO_AMT = SRC.PYPR2PD_DSTCO_AMT
,TYPR3PD_DSTCO_AMT = SRC.TYPR3PD_DSTCO_AMT
,PYPR3PD_DSTCO_AMT = SRC.PYPR3PD_DSTCO_AMT
,TYPR4PD_DSTCO_AMT = SRC.TYPR4PD_DSTCO_AMT
,PYPR4PD_DSTCO_AMT = SRC.PYPR4PD_DSTCO_AMT
,TYPR5PD_DSTCO_AMT = SRC.TYPR5PD_DSTCO_AMT
,PYPR5PD_DSTCO_AMT = SRC.PYPR5PD_DSTCO_AMT
,TYPR6PD_DSTCO_AMT = SRC.TYPR6PD_DSTCO_AMT
,PYPR6PD_DSTCO_AMT = SRC.PYPR6PD_DSTCO_AMT
,L12_DST_NET_COMP_AMT = SRC.L12_DST_NET_COMP_AMT
,PYL12_DST_NET_COMP_AMT = SRC.PYL12_DST_NET_COMP_AMT
,WTD_BSPN_PCT = SRC.WTD_BSPN_PCT
,MTD_BSPN_PCT = SRC.MTD_BSPN_PCT
,QTD_BSPN_PCT = SRC.QTD_BSPN_PCT
,YTD_BSPN_PCT = SRC.YTD_BSPN_PCT
,PW_BSPN_PCT = SRC.PW_BSPN_PCT
,PM_BSPN_PCT = SRC.PM_BSPN_PCT
,L12_BSPN_PCT = SRC.L12_BSPN_PCT
,PYL12_BSPN_PCT = SRC.PYL12_BSPN_PCT
,WTD_ASPN_PCT = SRC.WTD_ASPN_PCT
,MTD_ASPN_PCT = SRC.MTD_ASPN_PCT
,QTD_ASPN_PCT = SRC.QTD_ASPN_PCT
,YTD_ASPN_PCT = SRC.YTD_ASPN_PCT
,PW_ASPN_PCT = SRC.PW_ASPN_PCT
,PM_ASPN_PCT = SRC.PM_ASPN_PCT
,L12_ASPN_PCT = SRC.L12_ASPN_PCT
,PYL12_ASPN_PCT = SRC.PYL12_ASPN_PCT
;
The merge is taking close to 43K cpu. Is there any wany to minimize it?
Thanks,
Srila