ORA-00600 [kkqtSetOp.1] – Join Factorization

Just a quick note about an ORA-00600 that recently occurred following an upgrade to

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.

4 Responses to ORA-00600 [kkqtSetOp.1] – Join Factorization

  1. Pingback: Why did that report run slow? ASH says log file sequential read « OraStory

  2. Dom Brooks says:

    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.

    • kailas says:


      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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 86 other followers

%d bloggers like this: