After Upgrade to 12.2 OR Expansion is Unexpectedly Bypassed for Some SQL Queries Which Can Result in Sub-optimal Execution Plans (Doc ID 2279072.1)

Last updated on JUNE 22, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

After upgrading from the Oracle 12.1 release to Oracle 12.2 you observe a change in execution plan for some SQL queries.

When comparing choices made by the Cost Based Optimizer (CBO) in 10053 trace files that lead to the "good" and "sub-optimal" execution plans used, you notice that in the "Query Block Registry" section an OR expansion has been leveraged in the "good" plan that has not been used in the sub-optimal plan:

10053 "Query Block Registry" comparison
From 12.1 10053 trace file: From 12.2 10053 trace file:

Query Block Registry:
SEL$2 0x6928a4d0 (PARSER)
SEL$2_2 0x6928a4d0 (OR EXPANSION SEL$2)
SEL$2_1 0x6928a4d0 (OR EXPANSION SEL$2; SEL$2; 8 LIST LIST)
SEL$2_1 0x6928a4d0 (OR EXPANSION SEL$2; SEL$2; 8 LIST LIST)
SEL$1 0x64998fd0 (PARSER)
INS$1 0x68681630 (PARSER) [FINAL]

Query Block Registry:
SEL$2 0xd1748d10 (PARSER)
SEL$1 0xaa3ab098 (PARSER)
INS$1 0xaa3b6778 (PARSER) [FINAL]






 

 

 

 

 

 

In addition, within the 10053 trace file for the 12.2 environment, you see the message "ORE: bypassed - query block has sub-query correlated to non-parent" listed during the CBO's evaluation of the predicate for which an OR expansion was used in the 12.1 execution plan:

 

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