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'; BUGNO 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.

Update 1 February 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.

Update 3 April 2018 – Bug Raised

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

Update 26 September 2018 – Oracle 18c

The problem hasn’t been fixed in 18.3.0.0.180717.

References