ORA-00600 [kkqtSetOp.1] – Join Factorization
April 30, 2012 4 Comments
Just a quick note about an ORA-00600 that recently occurred following an upgrade to 11.2.0.3.
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:
ORA-600 [kkqtSetOp.1]
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.
OraStory 
Pingback: Why did that report run slow? ASH says log file sequential read « OraStory
Note that although this particular incident of an ORA-00600 was not fatal to the executing process, it had a very serious knock-on effect.
The session which received the ORA-00600 during parse time then proceeded to do a dump of online redo buffers in pin history into the associated ORA-00600 trace file, the principal symptom of which was a session waiting heavily on log file sequential read whilst blocking other sessions.
Hi,
I am facing ORA-00600 [kkqtSetOp.1] in my production database alert log file. We have observed that when we have run some specific queries, that time we are getting this error in alert log file. We have got some alternative option for same to avoid ORA-00600 [kkqtSetOp.1] error in alert log file and i.e before running the query, execute the following cmd:
alter session set “_optimizer_join_factorization” = FALSE;
But my concern is whter this is the right way to do?
or can we avoid these ORA-00600 [kkqtSetOp.1] errors which are getting in our alert log file?
It’s a bug. Raise it with Oracle Support. Ultimately you want to patch it.
Do you want to turn off join factorisation across the board – ideally not.
For a particular query, turning it off is not an issue.
But you still want the bug patched if there is a patch.
In the short term , you might want to track down where it’s occurring and whether it was an issue.
For me it was in the parse phase of a query and the error was not fatal to the query execution but there was also a significant knock-on effect described here: