Current long running query:
UPDATE /*+ full(b) parallel(b,8) */ SRV B SET
SRV_STS_CD =
'CE' WHERE PARTITION_ID = 8 AND (SRV_STS_CD = 'CS' OR
SRV_STS_CD =
'HA') AND SYS_CREATION_DATE > SYSDATE - 730 AND
SRV_TRX_S_NO IN (SELECT
SRV_TRX_S_NO FROM DVC WHERE PARTITION_ID = 8 AND
DVC_TRX_STS_CD != 'CS');
Modified query:
UPDATE /*+ full(b) parallel(b,4)
use_hash(DVC) leading(DVC_) unnest */ SRV B SET SRV_STS_CD =
'CE' WHERE PARTITION_ID = 8 AND (SRV_STS_CD = 'CS' OR
SRV_STS_CD =
'HA') AND SYS_CREATION_DATE > SYSDATE - 730 AND
SRV_TRX_S_NO IN (SELECT /*+ use_hash(DVC)
full(DVC) parallel(DVC,4) */
SRV_TRX_S_NO FROM DVC_TRX_REPOS WHERE PARTITION_ID = 8 AND
DVC_TRX_STS_CD != 'CS')
;