In the relase 12.2 Oracle improved the OR expansion by implementing it as a cost based transformation. As a consequence, this enhancement opened up new possibilities for finding better execution plans. In fact, there are already couple of excellent articles listed in References which explain how this transformation works. So, there is no point in repeating what was already said. What I’m going to do instead is, describe boundary conditions under which the optimizer decides to discard the OR Expansion entirely. This omission, in turn, can lead to a severe decline in performance after upgrade to 12.2.

Firstly, I’ll create two tables with a couple of indexes:

create table t1 (n1 number, c1 varchar2(255)) ;

create table t2 (n1 number, c1 varchar2(255), c2 varchar2(1)) ;
create index t2_idx1 on t2(n1);
create index t2_idx2 on t2(c1);

Secondly, I’ll examine the execution plan of the statement, where the OR expansion doesn’t kick in:

MERGE INTO t2 USING (
  SELECT  n1,c1 FROM t1 
) at1 ON (  
  at1.n1 = t2.n1 or at1.c1 = t2.c1 
) 
WHEN MATCHED THEN  UPDATE SET c2 = 'a'
WHEN NOT MATCHED THEN INSERT (n1) VALUES (1) ;

------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |                 |       |       |     4 (100)|          |
|   1 |  MERGE                 | T2              |       |       |            |          |
|   2 |   VIEW                 |                 |       |       |            |          |
|   3 |    MERGE JOIN OUTER    |                 |     1 |   298 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | T1              |     1 |   142 |     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT        |                 |     1 |   156 |     2   (0)| 00:00:01 |
|   6 |      VIEW              | VW_LAT_8626BD41 |     1 |   156 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL| T2              |     1 |   156 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter(("N1"="T2"."N1" OR "C1"="T2"."C1")) 

In the step 7 we can see that the OR expansion was not done.

Note that this is the problem specific to the merge statement above. In contrast, the new cost based transformation works perfectly on a simple select with the same OR predicates:

select * from t2 where n1 = 1 or c1 = 'a'

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |       |       |     2 (100)|          |
|   1 |  VIEW                         | VW_ORE_AE9E49E8 |     2 |   288 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL                   |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2              |     1 |   144 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX1         |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| T2              |     1 |   144 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | T2_IDX2         |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("N1"=1)
   5 - filter(LNNVL("N1"=1))
   6 - access("C1"='a')

Workaround

Luckily, there is a hidden parameter for falling back to the old-style heuristic OR expansion:

ALTER SESSION SET "_optimizer_cbqt_or_expansion"=off;

The plan that is being produced after activating the workaround is identical to the one in the relase 12.1:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |                 |       |       |     2 (100)|          |
|   1 |  MERGE                            | T2              |       |       |            |          |
|   2 |   VIEW                            |                 |       |       |            |          |
|   3 |    MERGE JOIN OUTER               |                 |     1 |   298 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL             | T1              |     1 |   142 |     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT                   |                 |     2 |   312 |     0   (0)|          |
|   6 |      VIEW                         | VW_LAT_8626BD41 |     2 |   312 |     0   (0)|          |
|   7 |       CONCATENATION               |                 |       |       |            |          |
|   8 |        TABLE ACCESS BY INDEX ROWID| T2              |     1 |   156 |     0   (0)|          |
|*  9 |         INDEX RANGE SCAN          | T2_IDX2         |     1 |       |     0   (0)|          |
|* 10 |        TABLE ACCESS BY INDEX ROWID| T2              |     1 |   156 |     0   (0)|          |
|* 11 |         INDEX RANGE SCAN          | T2_IDX1         |     1 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - access("C1"="T2"."C1")
  10 - filter(LNNVL("C1"="T2"."C1"))
  11 - access("N1"="T2"."N1")

Unfortunately, the workaround switches off the Cost Base OR Expansion Transformation (CBORE) completely. This means that we are trading the opportunity to find better execution plans in some cases for avoiding a potentially catastrophic plan in this particular example.

Bug

If we dig into the optimizer trace, we’ll indeed find the indication that CBORE was discarded:

ORE: bypassed - Merge view query block.

Next, the search in Metalink based on the line above yields the following documents:

• 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)
• BUG:26019148 CBQT ORE DOES NOT APPLY TO CORRELATED SCALAR SUBQUERY WITH OE

Therefore, it is an optimizer bug. Surprisingly, the fix is already included in the January PSU:

select BUGNO, VALUE, DESCRIPTION from V$SYSTEM_FIX_CONTROL where BUGNO='26019148';

     eBUGNO      VALUE
---------- ----------
DESCRIPTION
----------------------------------------------------------------
  26019148          1
Allow ORE in select list subq

But sadly, the problem is not really fixed, at least not for our merge statement. What I mean by that is, its plan didn’t change at all after applying the January PSU.

In conclusion, currently there doesn’t seem to be any alternative to switching off the Cost Based OR Expansion Transformation.

Updates

February 1, 2018 – Workaround with sql_patch

Actually, Jonathan Lewis suggested to create the sql patch with the opt_param hint. That works perfectly:

DECLARE
  l  VARCHAR2(32767);
BEGIN
  l := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_id    => 'dy8x4h4bratan',
    hint_text => q'[opt_param('_optimizer_cbqt_or_expansion','off')]',
    name      => 'cbqt_ore_off');
END;
/

The upside of this approach is that you don’t need to turn off the feature globally. By doing so, the CBORE transformation is deactivated just for the SQL statements where the optimizer is making a wrong decision. On the downside, this might become awkward if you’re dealing with multiple SQLs embedded in a volatile application code outside your control. In such case, it might be more convenient to turn off CBORE on the session or the database level.

April 3, 2018 – Bug Raised

The following bug was raised due to this issue: BUG 27786168 – CBQT ORE TRANSFORMATION NOT TAKING PLACE FOR MERGE STATEMENTS

September 26, 2018 – Oracle 18c

The problem hasn’t been fixed in 18.3.0.0.180717.

February 7, 2021 – patch

In the meantime, Oracle released the patch 27786168 for the versions 12c 18c and 19c. I tested it on 19.9.0.0.201020.
Interestingly, the optimizer performs the old-style, i.e. non-cost based ORE for merge statements:

MERGE INTO t2 USING (   SELECT  n1,c1 FROM t1 ) at1 ON (   at1.n1 =
t2.n1 or at1.c1 = t2.c1 ) WHEN MATCHED THEN  UPDATE SET c2 = 'a' WHEN
NOT MATCHED THEN INSERT (n1) VALUES (1)

Plan hash value: 2960188956

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                           |                 |       |       |     3 (100)|          |
|   1 |  MERGE                                    | T2              |       |       |            |          |
|   2 |   VIEW                                    |                 |       |       |            |          |
|   3 |    MERGE JOIN OUTER                       |                 |     1 |   298 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                     | T1              |     1 |   142 |     3   (0)| 00:00:01 |
|   5 |     BUFFER SORT                           |                 |     2 |   312 |     0   (0)|          |
|   6 |      VIEW                                 | VW_LAT_8626BD41 |     2 |   312 |     0   (0)|          |
|   7 |       CONCATENATION                       |                 |       |       |            |          |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |   156 |     0   (0)|          |
|*  9 |         INDEX RANGE SCAN                  | T2_IDX2         |     1 |       |     0   (0)|          |
|* 10 |        TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |   156 |     0   (0)|          |
|* 11 |         INDEX RANGE SCAN                  | T2_IDX1         |     1 |       |     0   (0)|          |
-------------------------------------------------------------------------------------------------------------

For other statements, ORE remains cost based:

select * from t2 where n1 = 1 or c1 = 'a'

Plan hash value: 2528924309

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |     2 (100)|          |
|   1 |  VIEW                                 | VW_ORE_AE9E49E8 |     2 |   288 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |   144 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T2_IDX1         |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |   144 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | T2_IDX2         |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("N1"=1)
   5 - filter(LNNVL("N1"=1))
   6 - access("C1"='a')

References

• Oracle White Paper: Optimizer with Oracle Database 12c Release 2
• Mohamed Houri: 12cR2 OR-Expansion
• Nigel Bayliss: Optimizer Transformations: OR Expansion