Loading....

Recently one my clients encountered an issue with a SQL query which returned no rows in the 12c database which had been upgraded, but was returning rows in any of the 11g databases which had not been upgraded as yet.

The query was


SELECT *
  FROM STORAGE t0
  WHERE ( ( ( ( ( ( (ROWNUM <= 30) AND (t0.BUSINESS_UNIT_ID = 2))   AND (t0.PLCODE = 1001))
                  AND (t0.SM_SERIALNUM = '5500100000149000994'))
                  AND ( (t0.SM_MODDATE IS NULL) OR (t0.SM_MODDATE <= SYSDATE)))
                AND   ( 
                        (t0.DEALER_ID IS NULL)
                         OR 
                        EXISTS   (SELECT t1.CUSTOMER_ID  FROM CUSTOMER_ALL t1 WHERE ( (t1.CUSTOMER_ID = t0.DEALER_ID) AND (t1.CSTYPE <> 'd')))
                        )
        )
        AND (t0.SM_STATUS <> 'b'));

If we added the hint /*+ optimizer_features_enable(‘11.2.0.4’) */ to the query it worked fine.

After a bit of investigation we found that we could possibly be hitting this bug

Bug 18650065 : WRONG RESULTS ON QUERY WITH SUBQUERY USING OR EXISTS

The solution was either to enable this hidden parameter at the session or database level or to apply the patch 18650065 which is now available for download from MOS.

ALTER SESSION SET “_optimizer_null_accepting_semijoin”=FALSE;

The patch 18650065 can be applied online in both a non-RAC as well as RAC environment

For Non-RAC Environments 

$ opatch apply online -connectString orcl:SYS:SYS_PASSWORD

For RAC Environments

2 node RAC example:

$ opatch apply online -connectString orcl1:SYS:SYS_PASSWORD:node1, orcl2:SYS:SYS_PASSWORD:node2


Please follow and like us:

Last Update: June 22, 2020  

September 1, 2015 30 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?