We have a unique problem in production. Every now and then our Oracle 12c database will hang during our batch cycle in the middle of the night. We will have to restart the database to keep going for a few days/weeks. Then it happens again.
After some digging, we have narrowed it down to this SQL:
UPDATE c_bill SET reason_desc = (SELECT description FROM codes
WHERE code_group = 'TRANSACTION_TYPE' AND code = c_bill.reason_code);
My first thought would be to try to gather evidence that would either confirm or refute the theory that you have a bad plan that is doing a Cartesian product. Is
v$sql 1? Or 36308? If it is 1, that would support the Cartesian product theory and I would start looking for the query plan in
dba_hist_sql_plan depending on when this happened and whether you are licensed to use the AWR. If it is 36308, on the other hand, that would imply that the query is updating 36308 rows but something caused it to be called 36308 times because something is trying to loop over every row in the table.