merge with APPEND PARALLEL hint running for over 14 hours, without it takes 1 minute - intermittent

Hi,

We have a sql which runs every day and normally runs in about 1 minute but on 2 occasions in the last 3 months when it has ran, it has hung e.e ran for hours without completing.

On those occasions, when it has hung we have simply killed the SID and ran it manually without the /*+ APPEND PARALLEL("<table_name>") */ and runs in about 1 minute.

We can also see the explain plan deviates from normal explain plan on those occasions.

On the 2 occasions it has ran for over 14 hours without completing the number of rows being processed same as normal.

Essentially merging about 5000 rows into a 25 million row table thus strange why such a sql would run fro over 14 hours without completing but then when kill and run without the PARELLEL HINT would complete in about 1 minute.