ORA-00600 [kkqtSetOp.1] – Join Factorization
April 30, 2012 2 Comments
Just a quick note about an ORA-00600 that recently occurred following an upgrade to 220.127.116.11.
I’ve not been able to distill an isolated test case from the specific production code generating this error so I’ll skip the application-specific example.
The error message reported was:
Unusually, this ORA-600 [kkqtSetOp.1] was not fatal to the session it which it occurred and this was because it was happening withing the SQL_Transform component of the CBO. i.e. during the query transformation stage of optimization.
Because I wasn’t able to reproduce the bug outside of the application process which was generating the error, I set a system-level optimizer trace for the specific sql id as follows:
alter system set events 'trace [RDBMS.SQL_OPTIMIZER.*] [sql:2b1y41hau5qhr]';
This trace showed that the ORA-00600 was happening in the SQL_Transform component and that, despite the errors, the optimizer was able to continue the optimization process and, ultimately, the execution of this sql was successful.
There are a couple of possible matching bugs but, in this case, disabling join factorisation for at the statement level using the opt_param hint was the simplest solution (hinting no_query_transformation might have been another option):
/*+ opt_param('_optimizer_join_factorization','false') */
If the scope of the problem was wider than a single sql statement then it might have been more appropriate to use ALTER SESSION or ALTER SYSTEM to disable join factorisation.
This is also a prime example of where the SQL Patch functionality might have been used, injecting the opt_param hint and avoiding the ORA-00600 without changing any source code.
Note: in general, there’s probably little value in blogging about ORA-00600s and ORA-07445s – they’re par for the course these days – although I do get a steady trickle of hits on similar articles so they’re helpful to some. First port of call should always be Oracle Support. This one was slightly interesting because unusually it was non-fatal to the executing process and because it was related to one of the newer optimizer features.