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.<\/p>\n
Firstly, I’ll create two tables with a couple of indexes:<\/p>\n
create table t1 (n1 number, c1 varchar2(255)) ;\n\ncreate table t2 (n1 number, c1 varchar2(255), c2 varchar2(1)) ;\ncreate index t2_idx1 on t2(n1);\ncreate index t2_idx2 on t2(c1);<\/code><\/pre>\nSecondly, I’ll examine the execution plan of the statement, where the OR expansion doesn’t kick in:<\/p>\n
MERGE INTO t2 USING (\n SELECT n1,c1 FROM t1 \n) at1 ON ( \n at1.n1 = t2.n1 or at1.c1 = t2.c1 \n) \nWHEN MATCHED THEN UPDATE SET c2 = 'a'\nWHEN NOT MATCHED THEN INSERT (n1) VALUES (1) ;\n\n------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n------------------------------------------------------------------------------------------\n| 0 | MERGE STATEMENT | | | | 4 (100)| |\n| 1 | MERGE | T2 | | | | |\n| 2 | VIEW | | | | | |\n| 3 | MERGE JOIN OUTER | | 1 | 298 | 4 (0)| 00:00:01 |\n| 4 | TABLE ACCESS FULL | T1 | 1 | 142 | 2 (0)| 00:00:01 |\n| 5 | BUFFER SORT | | 1 | 156 | 2 (0)| 00:00:01 |\n| 6 | VIEW | VW_LAT<\/span>_8626BD41 | 1 | 156 | 2 (0)| 00:00:01 |\n|* 7 | TABLE ACCESS FULL<\/span>| T2 | 1 | 156 | 2 (0)| 00:00:01 |\n------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 7 - filter((\"N1\"=\"T2\".\"N1\" OR \"C1\"=\"T2\".\"C1\")) \n<\/code><\/pre>\nIn the step 7 we can see that the OR expansion was not done.<\/p>\n
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:<\/p>\n
select * from t2 where n1 = 1 or c1 = 'a'\n\n-------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 2 (100)| |\n| 1 | VIEW | VW_ORE<\/span>_AE9E49E8 | 2 | 288 | 2 (0)| 00:00:01 |\n| 2 | UNION-ALL<\/span> | | | | | |\n| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 144 | 1 (0)| 00:00:01 |\n|* 4 | INDEX RANGE SCAN | T2_IDX1 | 1 | | 1 (0)| 00:00:01 |\n|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 144 | 1 (0)| 00:00:01 |\n|* 6 | INDEX RANGE SCAN | T2_IDX2 | 1 | | 1 (0)| 00:00:01 |\n-------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 4 - access(\"N1\"=1)\n 5 - filter(LNNVL(\"N1\"=1))\n 6 - access(\"C1\"='a')<\/code><\/pre>\nWorkaround<\/h1>\n
Luckily, there is a hidden parameter for falling back to the old-style heuristic OR expansion:<\/p>\n
ALTER SESSION SET \"_optimizer_cbqt_or_expansion\"=off;<\/code><\/pre>\nThe plan that is being produced after activating the workaround is identical to the one in the relase 12.1:<\/p>\n
\n-----------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-----------------------------------------------------------------------------------------------------\n| 0 | MERGE STATEMENT | | | | 2 (100)| |\n| 1 | MERGE | T2 | | | | |\n| 2 | VIEW | | | | | |\n| 3 | MERGE JOIN OUTER | | 1 | 298 | 2 (0)| 00:00:01 |\n| 4 | TABLE ACCESS FULL | T1 | 1 | 142 | 2 (0)| 00:00:01 |\n| 5 | BUFFER SORT | | 2 | 312 | 0 (0)| |\n| 6 | VIEW | VW_LAT<\/span>_8626BD41 | 2 | 312 | 0 (0)| |\n| 7 | CONCATENATION<\/span> | | | | | |\n| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 156 | 0 (0)| |\n|* 9 | INDEX RANGE SCAN | T2_IDX2 | 1 | | 0 (0)| |\n|* 10 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 156 | 0 (0)| |\n|* 11 | INDEX RANGE SCAN | T2_IDX1 | 1 | | 0 (0)| |\n-----------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 9 - access(\"C1\"=\"T2\".\"C1\")\n 10 - filter(LNNVL(\"C1\"=\"T2\".\"C1\"))\n 11 - access(\"N1\"=\"T2\".\"N1\")<\/code><\/pre>\nUnfortunately, 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.<\/p>\n
Bug<\/h1>\n
If we dig into the optimizer trace, we’ll indeed find the indication that CBORE was discarded:<\/p>\n
ORE: bypassed - Merge view query block.<\/code><\/pre>\nNext, the search in Metalink based on the line above yields the following documents:<\/p>\n
\n- 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)<\/li>\n
- BUG:26019148 CBQT ORE DOES NOT APPLY TO CORRELATED SCALAR SUBQUERY WITH OE<\/li>\n<\/ul>\n
Therefore, it is an optimizer bug. Surprisingly, the fix is already included in the January PSU:<\/p>\n
select BUGNO, VALUE, DESCRIPTION from V$SYSTEM_FIX_CONTROL where BUGNO='26019148';\n\n eBUGNO VALUE\n---------- ----------\nDESCRIPTION\n----------------------------------------------------------------\n 26019148 1\nAllow ORE in select list subq<\/code><\/pre>\nBut 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.<\/p>\n
In conclusion, currently there doesn’t seem to be any alternative to switching off the Cost Based OR Expansion Transformation.<\/p>\n
Updates<\/h1>\nFebruary 1, 2018 – Workaround with sql_patch<\/h2>\n
Actually, Jonathan Lewis suggested to create the sql patch with the opt_param hint. That works perfectly:<\/p>\n
\nDECLARE\n l VARCHAR2(32767);\nBEGIN\n l := SYS.DBMS_SQLDIAG.create_sql_patch(\n sql_id => 'dy8x4h4bratan',\n hint_text => q'[opt_param('_optimizer_cbqt_or_expansion','off')]',\n name => 'cbqt_ore_off');\nEND;\n\/\n<\/code><\/pre>\nThe 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.<\/p>\n
April 3, 2018 – Bug Raised<\/h2>\n
The following bug was raised due to this issue: BUG 27786168 – CBQT ORE TRANSFORMATION NOT TAKING PLACE FOR MERGE STATEMENTS<\/p>\n
September 26, 2018 – Oracle 18c<\/h2>\n
The problem hasn’t been fixed in 18.3.0.0.180717.<\/p>\n
February 7, 2021 – patch<\/h2>\n
In the meantime, Oracle released the patch 27786168 for the versions 12c 18c and 19c. I tested it on 19.9.0.0.201020.
\nInterestingly, the optimizer performs the old-style, i.e. non-cost based ORE for merge statements:<\/p>\n
MERGE INTO t2 USING ( SELECT n1,c1 FROM t1 ) at1 ON ( at1.n1 =\nt2.n1 or at1.c1 = t2.c1 ) WHEN MATCHED THEN UPDATE SET c2 = 'a' WHEN\nNOT MATCHED THEN INSERT (n1) VALUES (1)\n\nPlan hash value: 2960188956\n\n-------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-------------------------------------------------------------------------------------------------------------\n| 0 | MERGE STATEMENT | | | | 3 (100)| |\n| 1 | MERGE | T2 | | | | |\n| 2 | VIEW | | | | | |\n| 3 | MERGE JOIN OUTER | | 1 | 298 | 3 (0)| 00:00:01 |\n| 4 | TABLE ACCESS FULL | T1 | 1 | 142 | 3 (0)| 00:00:01 |\n| 5 | BUFFER SORT | | 2 | 312 | 0 (0)| |\n| 6 | VIEW | VW_LAT_8626BD41 | 2 | 312 | 0 (0)| |\n| 7 | CONCATENATION<\/span> | | | | | |\n| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 156 | 0 (0)| |\n|* 9 | INDEX RANGE SCAN | T2_IDX2 | 1 | | 0 (0)| |\n|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 156 | 0 (0)| |\n|* 11 | INDEX RANGE SCAN | T2_IDX1 | 1 | | 0 (0)| |\n-------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nFor other statements, ORE remains cost based:<\/p>\n
select * from t2 where n1 = 1 or c1 = 'a'\n\nPlan hash value: 2528924309\n\n---------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n---------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | | | 2 (100)| |\n| 1 | VIEW | VW_ORE<\/span>_AE9E49E8 | 2 | 288 | 2 (0)| 00:00:01 |\n| 2 | UNION-ALL<\/span> | | | | | |\n| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 144 | 1 (0)| 00:00:01 |\n|* 4 | INDEX RANGE SCAN | T2_IDX1 | 1 | | 1 (0)| 00:00:01 |\n|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 144 | 1 (0)| 00:00:01 |\n|* 6 | INDEX RANGE SCAN | T2_IDX2 | 1 | | 1 (0)| 00:00:01 |\n---------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 4 - access(\"N1\"=1)\n 5 - filter(LNNVL(\"N1\"=1))\n 6 - access(\"C1\"='a')<\/code><\/pre>\nReferences<\/h1>\n\n- Oracle White Paper: Optimizer with Oracle Database 12c Release 2<\/a><\/li>\n
- Mohamed Houri: 12cR2 OR-Expansion<\/a><\/li>\n
- Nigel Bayliss: Optimizer Transformations: OR Expansion<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
OR expansion not working properly in Oracle 12.2 Continue Reading