Portal

job stuck in a request involving DL_BATCH 

All integration batches are stuck, and RDS CPU utilization in reaching 100%.

Pasting TOP SQL:
-- Job: CREATE_ACCOUNTS_1
-- Task /Validate Source Authoring Data/AddressMapping/Change ErrorStatus of reintegrated SD records from ERROR to OBSOLETE_ERROR
update SA_MDM_ADRS_MAP as T
set
  B_ERROR_STATUS = 'OBSOLETE_ERROR'
from (
  select
    PREV_SA.B_LOADID, PREV_SA.ADRS_MAP_ID
  from SA_MDM_ADRS_MAP PREV_SA
   inner join DL_BATCH PREV_B
     on (
       PREV_SA.B_LOADID = PREV_B.B_LOADID
       and PREV_B.B_BATCHID is not null and PREV_B.B_BATCHID < 432004
    )
   inner join SA_MDM_ADRS_MAP CUR_SA
     on (<span class="fr-marker" data-id="0" data-type="false" style="display: none; line-height: 0;"></span><span class="fr-marker" data-id="0" data-type="true" style="display: none; line-height: 0;"></span>
       PREV_SA.ADRS_MAP_ID = CUR_SA.ADRS_MAP_ID
       and CUR_SA.B_LOADID = 434050
     )
  where
    PREV_SA.B_ERROR_STATUS is not null and PREV_SA.B_ERROR_STATUS = 'ERROR'
) as S
where
  S.B_LOADID = T.B_LOADID
  and S.ADRS_MAP_ID = T.ADRS_MAP_ID

run full vacuum in dl_batch table : -

explanation : there are good chances that the explain plan of the query does not correspond to the current actual records in the tables, which makes it inefficient. Running full vacuum generates a better explain plan