I have this query that was running forever, stuck on product join:
IMSI ,SUBSCR_ACC_METHOD_START_DTTM ,SUBSCR_ACC_METHOD_END_DTTM ,case when Subscr_Acc_Method_Start_Dttm > Extraction_Dttm then 'E'
when Subscr_Acc_Method_End_Dttm between Extraction_Dttm and current_timestamp
or Subscr_Acc_Method_End_Dttm < Extraction_Dttm then 'D'
else 'I' end as Status_Flag ,par.Party_Id,Subscription_Id
FROM VDW_SUBSCRIPTION_ACC_METHOD_HI ACM
JOIN VDW_SUBSCRIPTION SUB ON ACM.SUBSCRIPTION_ID = SUB.SUBSCRIPTION_ID
JOIN VDW_SUBSCRIPTION_TYPE SUT ON SUB.SUBSCRIPTION_TYPE_ID = SUT.SUBSCRIPTION_TYPE_ID
WHERE SUT.SUBSCRIPTION_TYPE_GROUP_ID = 2
AND ACM.CHANGE_REASON_TYPE_ID IN (1,3)
AND ACM.ACCESS_METHOD LIKE '41%'
AND ACM.IMSI IS NOT NULL
JOIN VDW_PARTY_ACCOUNT_HIST PAC ON BASE.ACCOUNT_ID = PAC.ACCOUNT_ID
INNER JOIN vDW_Party PAR ON PAC.PARTY_ID=PAR.PARTY_ID
INNER JOIN Ntr_Extraction_Control ctl ON 1=1 and ctl.Extraction_Type='FUL' and ctl.Process_Mode_Flag=-1
SELECT CONDITION_VALUE FROM VCT_ETL_CONDITION_CONTROL
WHERE ATTRIBUTE_NAME = 'IMSI_BUS_STEER_ORG_FULL_INCL'
before i excute the same arroun 60 000 rows were inserted into table VCT_ETL_CONDITION_CONTROL for ATTRIBUTE_NAME = 'IMSI_BUS_STEER_ORG_FULL_INCL' . This caused to query to last forever. It was stuck on product join with table VDW_SUBSCRIPTION_ACC_METHOD_HI and some other spool (step 18 from explain plan)
After multiple tries, the issue was resolved by drop the stats for table VCT_ETL_CONDITION_CONTROL (stats for column CONDITION_VALUE as well ) and collecting only summury one. then the flow finished within minutes, and the step on which it was stuck was changed, and no more product join was showed.
Below is the execution plan for this step, when running explain :
18) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an
all-rows scan, which is joined to
prd_dwh.Subscription_Acc_Method_Hist in view
PRD_STG.PM_VTUIB4V7GVKH3HTYW7W4AGQEDCM by way of an all-rows scan
with a condition of ("(NOT (prd_dwh.Subscription_Acc_Method_Hist
in view PRD_STG.PM_VTUIB4V7GVKH3HTYW7W4AGQEDCM.Imsi IS NULL )) AND
((prd_dwh.Subscription_Acc_Method_Hist in view
PRD_STG.PM_VTUIB4V7GVKH3HTYW7W4AGQEDCM.Change_Reason_Type_Id IN (1
3 )) AND ((prd_dwh.Subscription_Acc_Method_Hist in view
PRD_STG.PM_VTUIB4V7GVKH3HTYW7W4AGQEDCM.Main_Access_Method_Ind = 1)
AND (prd_dwh.Subscription_Acc_Method_Hist in view
PRD_STG.PM_VTUIB4V7GVKH3HTYW7W4AGQEDCM.Access_Method LIKE '41%')))")
locking prd_dwh.Subscription_Acc_Method_Hist for access. Spool 19
and prd_dwh.Subscription_Acc_Method_Hist are joined using a
product join, with a join condition of (
Subscription_Id"). The result goes into Spool 21 (one-amp), which
is redistributed by the hash code of ('FUL') to all AMPs. The
size of Spool 21 is estimated with low confidence to be 2 rows (
106 bytes). The estimated time for this step is 0.85 seconds.
I am not sure why this resolved the issue. Can someone explain this to ne. How i can recognize issues like this in future.
Thanks in advance.
Afterward it finished within minutes
After inserting your 60.000 rows to VCT_ETL_CONDITION_CONTROL, did you recollect the statistics ?
If you didn't, the optimizer could think of a 0-1 row join and adapt his plan accordingly.
While dropping the stats, the optimizer would fetch the table and extrapolate the statistics, which some times is OK and some times not.
With fresh stats you should have an efficient plan :
collect statistics column (<PI>), column (ATTRIBUTE_NAME) on VCT_ETL_CONDITION_CONTROL;