Analysis of ORA-00600: internal error code, arguments: [kxfxsStatus: nojoin but already sent joinOK]

Hi all,

In the alert log we found the below ORA-600 internal error. 

Alert log Error is below:

ORA-00600: internal error code, arguments: [kxfxsStatus: nojoin but already sent joinOK], [197888], [], [], [], [], [], [], [], [], [], []

We had to report this error with our analysis to our manager. We had searched the related document for this error in Oracle support website and found the following document.

Bug 9785632 - Parallel query fails with OERI[kxfxsstatus: nojoin but already sent joinok] (Doc ID 9785632.8)

As per above Doc ID 9785632.8,

Affects::

Versions confirmed as being affected

·         11.2.0.1

·         11.1.0.7

Fixed:

This issue is fixed in        

·         12.1.0.1 (Base Release)

·         11.2.0.2 (Server Patch Set)

·         11.2.0.1 Bundle Patch 8 for Exadata Database

Symptoms:

·         Internal Error May Occur (ORA-600)

·         ORA-600 [kxfxsStatus: nojoin but already sent joinOK]

Description:

ORA-600:[kxfxsStatus: nojoin but already sent joinOK]. can occur attempting to use parallel query.

We sent these details with the error to our manager to inform customer. But We got a reply mail from our manager stating that "But I don't see the parallel hint used. Are you sure, can you verify?"

Now i'm in a situation to relate the description mentioned in the document to our issue. Hence i had decided to take explain plan of the SQL statement in which we could see whether the query is running in parallel or not. 

I used below query to get explain plan of the SQL statement which caused this ORA-600 internal error.

select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id',null,null,'ALL'));

From the output, i was able to see some parallel query keywords like PX COORDINATOR,PX SEND QC (RANDOM),PX BLOCK ITERATOR,PX RECEIVE,PX SEND HASH,PX SEND BROADCAST in it. 

Then I sent the explain plan to my manager to confirm that SQL statement is trying to run in parallel only. 

This is how i made it on that day. Hope you'll also find it is helpful.