De-Correlated Lateral view: VW_DCL_mmm

Oracle Cost Based Optimizer has a number of query rewrites and transformations both simple and complex, old and new. Wether you know very few of them or only the most common ones, I believe it is worth keeping yourself well updated with the new ones as it might introduce strange performance issue like the one I have encountered very recently. This new 12c transformation I want to explain here is known as De-Correlated Lateral view. It appears in execution plans as VW_DCL_mmm. This article examines this transformation in the context of a real life production query.

A critical query, executed several hundred of thousands of times within a two hours batch job consumes 67K of Logical I/O per execution. It made the job systematically fail with a time out error. The average execution time of this query being less than one second, I ruled out the possibility of using SQL monitoring report to spot where most of these Logical I/O are coming from. Hopefully the high number of times this query is launched during the batch job makes its activity sampled into ASH. As such, via the following simple query I knew exactly at which operations of the corresponding execution plan this query is spending most of its time and resource:

select
   sql_plan_line_id
  ,count(1)
from
   gv$active_session_history
where
 sample_time between to_date('22032017 23:00:18', 'ddmmyyyy hh24:mi:ss')
             and     to_date('22032017 23:50:18', 'ddmmyyyy hh24:mi:ss')
and
 sql_id = '94jkz74mvwmbj'
group by
   sql_plan_line_id
order by 2 desc;

SQL_PLAN_LINE_ID   COUNT(1)
---------------- ----------
              48         41
              47         9
	          11         8
              30         1

SQL> select * from table(dbms_xplan.display_curosr('94jkz74mvwmbj'));                                                                              
----------------------------------------------------------------          
| Id  | Operation                     | Name            | Rows  |                
-----------------------------------------------------------------                                                                                   
|*  11|  HASH JOIN OUTER              |                 |      3|     
|   12|  NESTED LOOP OUTER            |                 |      3|                                           
|   33|   TABLE ACCESS BY INDEX ROWID | T1              |      1|  
|*  34|   INDEX UNIQUE SCAN           | T1_PK           |      1|           
|   47|  VIEW                         | VW_DCL_52812513 |  5340K|                                           
|*  48|    TABLE ACCESS FULL          | T2              |  5340K|                                           
-----------------------------------------------------------------         

Reduced to the bare minimum, the above execution plan is the result of a left outer join between table T1 and table T2 using a primary-foreign key relationship (predicate n°11) and a mix of an OR and an AND predicate applied on table T2 (operation n° 48). We will see later in this article that the two bolded words above represent the key words of a Lateral View.

As per regards to the real life query I was practically sure that a NESTED LOOP with table T1 as the outer row source probing the inner row source via an existing foreign key index will end up by filtering table T2 using the OR and the AND predicate but only on the rows that survived the primary-foreign key join.

But what the heck is this VW_DCL_52812513 view? It is useless to say that I didn’t find any related information both using Google and MyOracle Support.

After a couple of hour of investigation at the client site I ended up finding a work around and pushed an urgent fix into PRODUCTION. Notice with me how the fix has transformed the statistics of this query and its batch job:

SQL> @sqlstats.sql 
Enter value for sql_id:  94jkz74mvwmbj

CHILD_NUMBER SQL_PROFILE                   PLAN_HASH_VALUE  AVG_GETS  EXECS
----------- ------------------------------ --------------- ---------- ------
4                                           1521027974       66288     1976  → old execution plan
6           PROFILE_94jkz74mvwmbj_MANUAL     553415384          90    10092  → new execution plan

In order to definitely understand this new transformation and share it with you I’ve engineered the following model:

Setting the Scenes

create table t1
as
 with generator as (
   select --+ materialize
      rownum id
   from dual
   connect by level <= 1000
)
select
    rownum id1,
    mod(rownum-1,2) flag1,
    mod(rownum-1,3) flag2,
    rownum          n1,
    lpad(rownum,30) v1
from
   generator v1,
   generator v2
where
   rownum <= 1e4;
   
alter table t1 add constraint t1_pk primary key (id1);

create table t2
as 
 select
    level id1
   ,trunc((rownum+2)/2) product_t1
   ,date '2012-06-07' + mod((level-1)*5,10) + interval '5' minute start_date
   ,date '2012-06-08' + mod((level-1)*5,10) + interval '5' minute end_date
   ,rpad('xx',10) padding
from
   dual
connect by level <=1e4;

alter table t2 add constraint t2_pk primary key (id1);
alter table t2 add constraint t2_t1_fk foreign key (product_t1) references t1(id1);

-- creating an index covering the FK deadlock threat and other business requirements
create index idx_t2_usr_1 on t2(product_t1, start_date);

Here’s below the query (and its execution plan) with which I have reproduced the interesting part of the client’s real life query:

explain plan for
select
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

SQL> select * from table(dbms_xplan.display);                                                                                                  
-------------------------------------------------------                                           
| Id  | Operation           | Name            | Rows  |                                           
------------------------------------------------------                                           
|   0 | SELECT STATEMENT    |                 | 14925 |                                           
|*  1 |  HASH JOIN OUTER    |                 | 14925 |                                             
|   2 |   TABLE ACCESS FULL | T1              | 10000 |                                           
|   3 |   VIEW              | VW_DCL_C83A7ED5 |  9926 |                                           
|*  4 |    TABLE ACCESS FULL| T2              |  9926 |                                           
------------------------------------------------------                                           
                                                                                                                                  
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                                                                                                                                         
   1 - access("T1"."ID1"="ITEM_2"(+))                                                                                             
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 
         AND "T2"."END_DATE">=:2) 

Spot again how the predicate part can be so crucial to indicate the way you should pursue when troubleshooting query performance issues:

Predicate Information (identified by operation id):                                                                               
--------------------------------------                                                                                                                                                                                        
   1 - access("T1"."ID1"="ITEM_2"(+))                

While I recognize the (+) symbol as the Oracle way of re-architecting and ANSI outer join, I admit that I have no idea what the heck is this ITEM_2 in predicate n°1?

When all else fails then a 10053 trace file might help

alter session set tracefile_identifier='VW_DCL_MHO_XXXX';
alter session set events '10053 trace name context forever, level 1';
explain plan for
select
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );
alter session set events '10053 trace name context off';           

Digging a little bit into the generated trace file I found that Oracle has transformed the original query into the following one:

SELECT 
  t1.id1 id1,
  t1.flag1 flag1,
  t1.flag2 flag2,
  t1.n1 n1,
  t1.v1 v1,
  vw_dcl_1b0973d4.item_1 id1,
  vw_dcl_1b0973d4.item_2 product_t1,
  vw_dcl_1b0973d4.item_3 start_date,
  vw_dcl_1b0973d4.item_4 end_date,
  vw_dcl_1b0973d4.item_5 padding
FROM c##mhouri.t1 t1,
  (SELECT 
    t2.id1 item_1_0,
    t2.product_t1 item_2_1,
    t2.start_date item_3_2,
    t2.end_date item_4_3,
    t2.padding item_5_4
  FROM c##mhouri.t2 t2
  WHERE t2.start_date<=:b1 AND t2.end_date >=:b2
  OR t2.id1           >100
  ) VW_DCL_1B0973D4
WHERE t1.id1=VW_DCL_1B0973D4.item_2(+) 

Simply put Oracle did two things:
1. Created a Lateral view
2. De-Correlated this lateral view by excluding the join predicate with table T1 from the Lateral view

This is clearly backed up by the following lines in the same trace file

DCL: Checking validity of lateral view decorrelation SEL$BCD4421C (#1)
DCL: Passed decorrelation validity for lateral view block SEL$BCD4421C (#1)
DCL: Decorrelation of lateral view query block SEL$BCD4421C (#1).
Registered qb: SEL$6226B99A 0x693ad4d0 (VIEW DECORRELATED SEL$BCD4421C; SEL$BCD4421C)

Since I have finally succeeded to understand what this transformation is I knew what else I have to do:

explain plan for
select /*+ optimizer_features_enable('11.2.0.4') */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

SQL> select * from table(dbms_xplan.display);                                                                                                                                                                                                       
--------------------------------------------------------------                       
| Id  | Operation                     | Name         | Rows  |                                   
--------------------------------------------------------------                                   
|   0 | SELECT STATEMENT              |              | 10000 |                                   
|   1 |  NESTED LOOPS OUTER           |              | 10000 |                                   
|   2 |   TABLE ACCESS FULL           | T1           | 10000 |                                   
|   3 |   VIEW                        |              |     1 |                                   
|*  4 |    TABLE ACCESS BY INDEX ROWID| T2           |     1 |                                   
|*  5 |     INDEX RANGE SCAN          | IDX_T2_USR_1 |     1 |                                   
--------------------------------------------------------------                                   
                                                                                                                                  
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                                                                                                                                                           
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)                                                    
   5 - access("T1"."ID1"="T2"."PRODUCT_T1")                   

This exactly what I was expecting at the beginning of my investigations. Annihilating the effect of the de-correlated lateral view will open a much better path at least for my real life query case.

Summary

As of now I hope that this article can help identifying what a VW_DCL_mmm transformation is. I hope as well that google will hist this article when asked about this particular transformation.