SamV SamV - 1 year ago 143
SQL Question

Mysterious Cartesian product in Oracle UPDATE

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);

I've renamed the tables - but the codes table have code and description. The developer is trying to copy description from this to the c_bill table.

This SQL is part of a stored procedure that's run nightly as part of a batch job. The developer does another update just before this, that passes through fine, but this SQL takes a long time.

During a particular run, the table had 36308 rows. When I check the Production DB (I look at the SQLs in v$sql table), I see the following:

Rows_processed for the 1st update 36308
Rows_processed for the above update 1318270864, and that happpens to be = 36308 * 36308!! (Cartesian product?)

We do not have this problem in TEST. When I tried the UPDATE and explain planned it in TEST, no problem - it shows exactly 36308 rows in both UPDATE and v$sql.

This is puzzling. Does anyone see a chance of a cartesian product in that SQL? Or are you aware of any bug in Oracle 12c optimizer that might turn it into a Cartesian product (We just applied some patches to fix bugs with Group by!).

I have fair amount of experience with Oracle - i tune queries here. I've suggested to the developer to add a where condition to be sure. We are yet to test that. In the mean time, I wanted to pass by the Oracle experts to get your take on it. Any comments/suggestions much appreciated.

UPDATE: for anyone looking at this post in the future -
The problem is not Cartesian product, but the problem UPDATE sql running in a cursor loop on th same table, hence looking like Cartesian. See Justin's answer.

Answer Source

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 executions in 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 v$sql_plan or 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download