ORA-00600 [kkqtSetOp.1] – Join Factorization

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.

About these ads

2 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.

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

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: