OPT_PARAM Hint saves the day…

骑猪看日落 2022-03-02 13:10 116阅读 0赞

一个工程师使用嵌套子查询执行删除操作，抛出[ORA-00600][]错误。在MOS的[查阅工具][Link 1]上显示这个错误是由此Bug引起的：

[Bug 16347248  ORA-600 \[kkqjpdGetContext:1\] parsing SQL with subquery][Bug 16347248_ ORA-600 _kkqjpdGetContext_1_ parsing SQL with subquery]

这个Bug提供的解决办法是执行ALTER SESSION命令。

alter session set "_optimizer_unnest_disjunctive_subq"= FALSE;

相比改变全局会话，我们使用[OPT\_PARAM][OPT_PARAM]Hint，编写像下述的子查询：

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

发表评论提醒我们在11.2.0.4或者12c上删除Hint，工作愉快。

加油。

> One of the developers was performing a delete using a nested subquery, which was throwing out[ORA-00600 ][ORA-00600]errors. The[ ORA-00600 Lookup][Link 1]Tool on MOS suggested it was caused by this bug:  
> [Bug 14347248 ORA-600 \[kkqjpdGetContext:1\] parsing SQL with subquery][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 ][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…

[OPT\_PARAM Hint saves the day…][OPT_PARAM Hint saves the day]

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp; <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"> <img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[ORA-00600]: http://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm#sthref395
[Link 1]: https://support.oracle.com/epmos/faces/DocContentDisplay?id=153788.1
[Bug 16347248_ ORA-600 _kkqjpdGetContext_1_ parsing SQL with subquery]: https://support.oracle.com/epmos/faces/DocContentDisplay?id=16347248.8
[OPT_PARAM]: http://www.oracle-base.com/articles/misc/granular-control-of-optimizer-features-using-fix-control.php#opt_param-hint
[Bug 14347248 ORA-600 _kkqjpdGetContext_1_ parsing SQL with subquery]: https://support.oracle.com/epmos/faces/DocContentDisplay?id=14347248.8
[OPT_PARAM Hint saves the day]: http://www.oaktable.net/content/optparam-hint-saves-day%E2%80%A6