Wrong Results When Parallelism Is Used and ORA-600 [15570] listed in the alert.log. (Doc ID 1472485.1)

Last updated on AUGUST 03, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 12cBETA2 [Release 10.2 to 12.1]
Information in this document applies to any platform.

Symptoms

This issue has been observed on 10.2.0.4 to 11.2.0.3 versions,

When attempting to execute a specific query in parallel wrong results are seen:

alter session enable parallel query;

SELECT 1
 FROM   te_late_umr_ver a, te_late_umr_ver b, te_mastman c
 WHERE  b.umr = '0M00094D1AL64I' 
   AND  b.special_stmt = '2' 
   AND  b.umr = a.umr
   AND  b.version_no = a.version_no 
   AND  b.special_stmt = a.special_stmt    AND  c.umr = b.umr
   AND  c.version_no = b.version_no
   AND  c.special_stmt = b.special_stmt
   AND  c.eman_create_dt >= TO_DATE ('20100118', 'yyyyMMdd')
   AND  c.eman_create_dt < TO_DATE ('20100119', 'yyyyMMdd')
   AND  EXISTS (
                SELECT 'x'
                FROM   te_csgmt g
                WHERE  g.umr = a.umr
                  AND  g.version_no = b.version_no
                  AND  g.special_stmt = b.special_stmt
                  AND  g.mast_man_item_no = c.mast_man_item_no)

no rows selected

Execution Plan (Wrong Results) from 10.2.0.4:
 =============================================
 -----------------------------------------------------------------------------
 | Id |Operation                       |Name           |Rows|  TQ |PQ Distrib|
 -----------------------------------------------------------------------------
 |   0|SELECT STATEMENT                |               |   1|     |          |
 |   1| PX COORDINATOR                 |               |    |     |          |
 |   2|  PX SEND QC (RANDOM)           |:TQ10002       |   1|Q1,02|QC (RAND) |
 |   3|   NESTED LOOPS                 |               |   1|Q1,02|          |
 |   4|    NESTED LOOPS                |               |   1|Q1,02|          |
 |   5|     NESTED LOOPS               |               |   1|Q1,02|          |
 |   6|      BUFFER SORT               |               |    |Q1,02|          |
 |   7|       PX RECEIVE               |               |    |Q1,02|          |
 |   8|        PX SEND BROADCAST       |:TQ10000       |    |     |BROADCAST |
 |*  9|         INDEX RANGE SCAN       |PK_LATE_UMR_VER|   1|     |          |
 |  10|      SORT UNIQUE               |               |   1|Q1,02|          |
 |  11|       BUFFER SORT              |               |    |Q1,02|          |
 |  12|        PX RECEIVE              |               |   1|Q1,02|          |
 |  13|         PX SEND HASH           |:TQ10001       |   1|Q1,01|HASH      |
 |  14|          PX PARTITION RANGE ALL|               |   1|Q1,01|          |
 |* 15|           INDEX RANGE SCAN     |IEMN_CSGMT1    |   1|Q1,01|          |
 |* 16|     INDEX UNIQUE SCAN          |PK_LATE_UMR_VER|   1|Q1,02|          |
 |  17|    PARTITION RANGE SINGLE      |               |   1|Q1,02|          |
 |* 18|     INDEX RANGE SCAN           |IE_MASTMAN     |   1|Q1,02|          |
 -----------------------------------------------------------------------------

 

When parallelism is disabled the correct lines are seen.

alter session disable parallel query;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms