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:

• Bug 14347248  ORA-600 [kkqjpdGetContext:1] parsing SQL with subquery

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 11.2.0.4 or 12c and job’s a good’un! 🙂

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer. View all posts by Tim...