OR-Expansion by-passed: top query block of a DML

Here’s an interesting OR-Expansion limit which reared its head a little bit after a 12cR1-to-19c Oracle upgrade. It happens during a Create Table As Select (CTAS) which was using a CONCATENATION operation under 12cR1 completing in a couple of seconds. However, under 19c version the same CTAS uses a dramatic NESTED LOOPS to the detriment of the more powerful OR-Expansion and took more than 6 hours.

Here’re below the 12cR1 and 19c execution plan respectively

------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  |Time     |
------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |            |       |         |
|   1 |  LOAD AS SELECT                  | TARGET_T   |       |         |
|   2 |   OPTIMIZER STATISTICS GATHERING |            | 71227 |00:00:05 |
|   3 |    HASH UNIQUE                   |            | 71227 |00:00:05 |
|   4 |     CONCATENATION                |            |       |         |
|   5 |      HASH JOIN                   |            | 38426 |00:00:03 |
|   6 |       TABLE ACCESS STORAGE FULL  | SOURCE_T2  | 48067 |00:00:01 |
|   7 |       TABLE ACCESS STORAGE FULL  | SOURCE_T1  |    16M|00:00:03 |
|   8 |      HASH JOIN                   |            | 32801 |00:00:03 |
|   9 |       TABLE ACCESS STORAGE FULL  | SOURCE_T2  | 48067 |00:00:01 |
|  10 |       TABLE ACCESS STORAGE FULL  | SOURCE_T1  |    11M|00:00:03 |
-------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Time     |
-------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |            |       |          |
|   1 |  LOAD AS SELECT                  | TARGET_T   |       |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |            | 71227 | 08:53:39 |
|   3 |    HASH UNIQUE                   |            | 71227 | 08:53:39 |
|   4 |     NESTED LOOPS                 |            | 71227 | 08:53:39 |
|   5 |      TABLE ACCESS STORAGE FULL   | SOURCE_T1  |    24M| 00:00:03 |
|   6 |      TABLE ACCESS STORAGE FULL   | SOURCE_T2  |     1 | 00:00:01 |
-------------------------------------------------------------------------

There is a simple root cause for this performance deterioration: OR-Expansion can’t be used by Oracle during a DDL operation.

You can locate the root cause of this transformation limit by using the or_expand hint and observing the generated Hint Report at the bottom of the corresponding execution plan:

create table t1 (n1 number, n2 number, c1 varchar2(10));
create index t1_idx1 on t1(n1, n2);
create index t1_idx2 on t1(n2);

create table t2 as
select
   rownum id
  ,trunc((rownum-1/6)) n2
  ,trunc((rownum-1/8)) n3
  ,mod(rownum,5) n4
  ,lpad('z', 4) vc
from
 dual
connect by level <=1e3;

  
exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2');

SQL>  explain plan for
	create table t1_bis
	 as
	   select /*+ or_expand */
		t1.*
	   from
		t1, t2
	   where
		t1.n1 = t2.id
	  or
	  t1.n1 = t2.n2;

Explained.

-----------------------------------------------------------
| Id  | Operation                        | Name   | Rows  |
-----------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |        |     1 |
|   1 |  LOAD AS SELECT                  | T1_BIS |       |
|   2 |   OPTIMIZER STATISTICS GATHERING |        |     1 |
|   3 |    NESTED LOOPS                  |        |     1 |
|   4 |     TABLE ACCESS FULL            | T1     |     1 |
|*  5 |     TABLE ACCESS FULL            | T2     |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("T1"."N1"="T2"."ID" OR "T1"."N1"="T2"."N2")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         U -  or_expand / Top query block of a DML  -------> Note the reason for not using the hint

You can also get the reason for which Oracle by-passed the or-expansion for the CTAS case via the corresponding 10053

**************************
Query transformations (QT)
**************************
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: Top query block of a DML.
ORE: Checking validity of OR Expansion for query block SEL$1 (#0)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."N1"="T2"."ID" OR "T1"."N1"="T2"."N2"
ORE: Predicate chain after QB validity check - SEL$1
"T1"."N1"="T2"."ID" OR "T1"."N1"="T2"."N2"
ORE: bypassed - Top query block of a DML. ---> Note the by-pass reason

While the OR-expansion transformation is by passed during the CTAS it is, however, used for the select part in 19c:

explain plan for
   select /*+ or_expand */
       t1.*
  from
    t1, t2
  where
    t1.n1 = t2.id
  or
    t1.n1 = t2.n2;

Explained.

--------------------------------------------------------
| Id  | Operation            | Name            | Rows  |
--------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     2 |
|   1 |  VIEW                | VW_ORE_F79C84EE |     2 |
|   2 |   UNION-ALL          |                 |       |
|*  3 |    HASH JOIN         |                 |     1 |
|   4 |     TABLE ACCESS FULL| T1              |     1 |
|   5 |     TABLE ACCESS FULL| T2              |  1000 |
|*  6 |    HASH JOIN         |                 |     1 |
|   7 |     TABLE ACCESS FULL| T1              |     1 |
|   8 |     TABLE ACCESS FULL| T2              |  1000 |
--------------------------------------------------------
/*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SET$2A13AF86_2" "T2"@"SET$2A13AF86_2")
      LEADING(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2" "T2"@"SET$2A13AF86_2")
      FULL(@"SET$2A13AF86_2" "T2"@"SET$2A13AF86_2")
      FULL(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2")
      USE_HASH(@"SET$2A13AF86_1" "T2"@"SET$2A13AF86_1")
      LEADING(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1" "T2"@"SET$2A13AF86_1")
      FULL(@"SET$2A13AF86_1" "T2"@"SET$2A13AF86_1")
      FULL(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1")
      NO_ACCESS(@"SEL$9162BF3C" "VW_ORE_F79C84EE"@"SEL$F79C84EE")
      OUTLINE(@"SEL$1")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE_LEAF(@"SEL$9162BF3C")
      OUTLINE_LEAF(@"SET$2A13AF86")
      OUTLINE_LEAF(@"SET$2A13AF86_1")
      OUTLINE_LEAF(@"SET$2A13AF86_2")
      ALL_ROWS
      OPT_PARAM('_optimizer_use_feedback' 'false')
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N1"="T2"."ID")
   6 - access("T1"."N1"="T2"."N2")
       filter(LNNVL("T1"."N1"="T2"."ID")) 

Workaround

To work around this limit, you can set back the optimizer features enable (OFE) to the previous version (12cR1 or less) where the OR-Expansion was not yet implemented so that your CTAS might be able to use the CONCATENATION operation. Or, you can opt for the following workaround provided by Mauro Pagano(@Mautro)

SQL> explain plan for
   create table t1_bis
   as
   select *
   from
   ( select /*+ no_merge
               or_expand
            */
        t1.*
   from
     t1, t2
   where
     t1.n1 = t2.id
   or
     t1.n1 = t2.n2
     );

Explained.

----------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes |
----------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |                 |     2 |    66 |
|   1 |  LOAD AS SELECT                  | T1_BIS          |       |       |
|   2 |   OPTIMIZER STATISTICS GATHERING |                 |     2 |    66 |
|   3 |    VIEW                          |                 |     2 |    66 |
|   4 |     VIEW                         | VW_ORE_4C1C5B2C |     2 |    66 |
|   5 |      UNION-ALL                   |                 |       |       |
|*  6 |       HASH JOIN                  |                 |     1 |    37 |
|   7 |        TABLE ACCESS FULL         | T1              |     1 |    33 |
|   8 |        TABLE ACCESS FULL         | T2              |  1000 |  4000 |
|*  9 |       HASH JOIN                  |                 |     1 |    41 |
|  10 |        TABLE ACCESS FULL         | T1              |     1 |    33 |
|  11 |        TABLE ACCESS FULL         | T2              |  1000 |  8000 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("T1"."N1"="T2"."ID")
   9 - access("T1"."N1"="T2"."N2")
       filter(LNNVL("T1"."N1"="T2"."ID"))

It consists of wrapping the select part into an inline view and pre-empting this inline view from being merged with the DDL part of the CTAS. As such the SELECT part becomes a valid candidate for the OR-Expansion transformation.

Oddity

When I tried to put the or_expand hint at the create table level here’s what I got

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> explain plan for
create /*+ or_expand */ table t1_bis
as
  select
     t1.*
from
  t1, t2
where
  t1.n1 = t2.id
or
  t1.n1 = t2.n2;  2    3    4    5    6    7    8    9   10   11
explain plan for
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8658
Session ID: 198 Serial number: 5465

With the following error in the alert log

ORA-07445: exception encountered: core dump [qkshtQBStore()+219] [SIGSEGV] [ADDR:0x258] [PC:0x2628B8B] [Address not mapped to object] []

Null-Aware anti-join, parsing and _optimizer_squ_bottomup

I originally wrote about Null-Aware anti join in 2017 just as something I’d keep seeing several times at client sites. But it turned out that it was rather to explain Null-Accepting Semi-Join. For the sake of clarity let me show, below, two execution plans where these two CBO transformations are in action respectively:

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN ANTI NA |      |     1 |     6 | -- Null-Aware ANTI Join
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 – access("T1"."N1"="N1")

----------------------------------------------------
----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 | --Null Accepting SEMI-join
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1") 

A simple way to don’t get confused is to remember this:

• Null-Aware Anti Join is for rows that don’t join (ANTI)
• Null-Accepting Semi join is for rows that (SEMI) join.

In this blog post, I will try to show how fixing a really weird real life parsing issue by changing the value of the _optimizer_squ_bottomup parameter has created a performance issue in another query. I will demonstrate that this bad side effect occurred because the Null-Aware anti-join transformation is by-passed by Oracle under a non-default value of this hidden parameter

SQL> select banner_full from gv$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

create table t1 as select rownum n1, trunc((rownum -1/5)) n2 from dual connect by level <= 1e6; 
create table t2 as select rownum n1, trunc((rownum -1/3)) n2 from dual connect by level <= 1e6;
 
update t1 set n1 = null where n1<=1e3;
 
exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

SELECT 
   count(1)
FROM t1
   WHERE t1.n1 NOT IN (select n1 from t2);
   
Elapsed: 00:00:00.31 -----> only 31 ms

Execution Plan
----------------------------------------------------------
Plan hash value: 1650861019
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc|
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    10 |       |
|   1 |  SORT AGGREGATE     |      |     1 |    10 |       |
|*  2 |   HASH JOIN ANTI NA |      |  1000K|  9765K|    16M|
|   3 |    TABLE ACCESS FULL| T1   |  1000K|  4882K|       |
|   4 |    TABLE ACCESS FULL| T2   |  1000K|  4882K|       |
------------------------------------------------------------

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

   2 - access("T1"."N1"="N1")

Statistics
------------------------------------------
          0  recursive calls
          0  db block gets
       4154  consistent gets
          0  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, Oracle has used a Null-Aware anti join (HASH JOIN ANTI NA) and executed the query in a couple of milliseconds with 4k of consistent gets

But, if you come to encounter a parsing issue (mainly in 12cR2) and you workaround it by changing the following parameter:

SQL> alter session set "_optimizer_squ_bottomup" = false;

Then see how your original query will behave:

SELECT count(1) FROM t1 WHERE t1.n1 NOT IN (select n1 from t2)

Global Information
------------------------------
 Status              :  EXECUTING             
 Instance ID         :  1                     
 Session             :  C##MHOURI (274:59197) 
 SQL ID              :  7009s3j53bdgv         
 SQL Execution ID    :  16777226              
 Execution Started   :  11/14/2020 14:43:59   
 First Refresh Time  :  11/14/2020 14:44:05   
 Last Refresh Time   :  11/14/2020 15:00:43   
 Duration            :  1004s     ------> still in execution phase after 1004s            
 Module/Action       :  SQL*Plus/-            
 Service             :  orcl19c               
 Program             :  sqlplus.exe           

Global Stats
=========================================
| Elapsed |   Cpu   |  Other   | Buffer |
| Time(s) | Time(s) | Waits(s) |  Gets  |
=========================================
|    1003 |     982 |       21 |    50M |
========================================= 
SQL Plan Monitoring Details (Plan Hash Value=59119136)

=========================================================================================
| Id   |       Operation       | Name |  Rows   |   Time    | Start  | Execs |   Rows   |
|      |                       |      | (Estim) | Active(s) | Active |       | (Actual) |
=========================================================================================
|    0 | SELECT STATEMENT      |      |         |           |        |     1 |          |
|    1 |   SORT AGGREGATE      |      |       1 |           |        |     1 |          |
| -> 2 |    FILTER             |      |         |       999 |     +6 |     1 |        0 |
| -> 3 |     TABLE ACCESS FULL | T1   |      1M |       999 |     +6 |     1 |     219K |
| -> 4 |     TABLE ACCESS FULL | T2   |       1 |      1004 |     +1 |  218K |     218K |
=========================================================================================

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( IS NULL)
   4 - filter(LNNVL("N1"<>:B1))

It used a dramatic execution plan.

And this is a perfect representation of what can happen in a real-life production system. The FILTER operation acts as a NESTED LOOPS operation would have done: the inner row source (operation id n°4) has been started 218K times. Moreover, the performance pain would have been even worse if your real-life query runs under an Exadata machine where the LNNVL function (used to overcome the always threatening null) will impeach smart scan from kicking in.

Here’s below a snippet of the 10053 when this transformation is used:

CBQT: Validity checks passed for 51rdr10tfdcb1.

Subquery removal for query block SEL$2 (#2)
RSW: Not valid for subquery removal SEL$2 (#2)
Subquery unchanged.
SU:   Transform ALL subquery to a null-aware antijoin.
SJC: Considering set-join conversion in query block SEL$5DA710D3 (#1)

AP: Adaptive joins bypassed for query block SEL$5DA710D3 due to null-aware anti-join

***************
Now joining: T2[T2]#1
***************
NL Join

Best:: JoinMethod: HashNullAwareAnti
       Cost: 2809.074624  Degree: 1  Resp: 2809.074624  Card: 1000000.000000 Bytes:

And here’s a snippet of the same trace file when this transformation is not used:

*************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_pga_max_size                       = 333600 KB
_optimizer_squ_bottomup             = false
_optimizer_use_feedback             = false
Bug Fix Control Environment 

Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): Disabled by parameter.
CBQT: Validity checks failed for cw6z7z8cajdbq.

Can we infer that that changing the default value of _optimizer_squ_bottomup will cancel the Cost-Based Query Transformation?

Summary

Changing the _optimizer_squ_bottomup value at a global level will fix several parsing issues occurring because of a not yet published bug Bug 26661798: HIGH PARSE TIME FOR COMPLEX QUERY 12C but, bear in mind that it might also introduce a performance deterioration for queries using NOT IN predicate where the Null-Aware anti join transformation is invalidated by the non-default value of this hidden parameter .