OPT_PARAM Hint saves the day…

One of the developers was performing a delete using a nested subquery, which was throwing out ORA-00600 errors. The ORA-00600 Lookup Tool on MOS suggested it was caused by this bug:

The workaround in the bug suggested running this ALTER SESSION command.

alter session set "_optimizer_unnest_disjunctive_subq"= FALSE;

Rather than alter the whole session, we used the OPT_PARAM hint, making the subquery look something like this.

(SELECT /*+ OPT_PARAM('_optimizer_unnest_disjunctive_subq' 'FALSE') */ ... FROM ...)

Slap in a comment reminding us to remove the hint when we move to or 12c and job’s a good’un! :)



Author: Tim...

I DBA, Developer, Author, Trainer.