Below is the DML statement and we can see what happens during update in this DML as it updates few column values.
MERGE /*+ parallel (rq, 2) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ parallel(a, 2) */
FROM PRD_RERATE_CHG_QUE a
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
WHERE status = 'Cancelled'
AND rerate_batch_key = 163)) rr
ON (rq.rowid = rr.rated_que_rowid)
WHEN MATCHED THEN
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL)
Also I collected the AWR & noticed that CPU time for this DML is 1,162.73(s) and elapsed time is 14,521.51(s). So can we say there are so much of wait time here that is why DML is so slow?