Oracle 19c Upgrade: Query hanging on PX Deq: Join Ack Part 2

Previously I had blogged about an intermittent problem which was affecting one of our feeds where a parallel query would just hang as the QC would wait forever on a parallel slave which never joined the party.

The theory was the parallel slave was affected by another issue – possibly the slave process died and until PMON cleans it up, the QC assumes it is still alive (ref doc id 250960.1)

Intermittent problems can be a right pain but this issue mainly affected one particular process most days kicking off between 1 and 2 am – same process runs 30-40 times per day but only tended to have the issue once so it seemed somewhat data dependent – but also would affect some other processes in different schemas very occasionally.

Over time, evidence built from trace files suggest that the issue was mostly related to:

ORA-00600: internal error code, arguments: [17182]

or

ORA-00600: internal error code, arguments: [17114]

or both in the same error stack

If the QC received the error, connection would terminate instantly (apart from trace file generation), if the slave received the error then QC would hang.

One patch – 31045929 – suggested by Oracle Support was applied but was not effective.

Second patch – 28907129 – similar.

Ultimately, a workaround, which was tested to validate that the second patch was in the right ballpark, was effective and that was to disable _optimizer_gather_stats_on_load (https://sqlmaria.com/2017/01/03/online-statistics-gathering/)

It’s disappointing to have to turn off yet another “new” feature – this one related to stats gathering for direct path into an empty segment – when we have already had to turn off real-time statistics for conventional dml previously ( link to future post – I thought I had blogged about this but seems not) but hopefully we will still be able to pursue patch fix to success.