Mohamed Houri’s Oracle Notes

June 17, 2017

12cR2 OR-Expansion

Filed under: CBO — hourim @ 7:47 am

In contrast to what the title seems suggesting, the OR-Expansion is not a new 12cR2 transformation. It has existed before and has been nicely explained in this article by the Oracle optimizer group. However, let me invite you to observe with me the following simple demonstration:

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);

explain plan for
select * 
from t1
where (n1 =1 or n2 = 42);

select * from table(dbms_xplan.display);

The above query has two different execution plans in 12cR1 and 12cR2 as shown below respectively:

-- 12cR1 : 12.1.0.2.0
---------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | 
----------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     2 |
|   1 |  CONCATENATION                       |         |       | 
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  3 |    INDEX RANGE SCAN                  | T1_IDX2 |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  5 |    INDEX RANGE SCAN                  | T1_IDX1 |     1 | 
----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N2"=42)
   5 - access("N1"=1)
       filter(LNNVL("N2"=42))
-- 12cR2 : 12.2.0.1.0
------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | 
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     2 | 
|   1 |  VIEW                                 | VW_ORE_BA8ECEFB |     2 |   
|   2 |   UNION-ALL                           |                 |       |   
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |   
|*  4 |     INDEX RANGE SCAN                  | T1_IDX1         |     1 | 
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 | 
|*  6 |     INDEX RANGE SCAN                  | T1_IDX2         |     1 | 
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("N1"=1)
   5 - filter(LNNVL("N1"=1))
   6 – access("N2"=42)

As you can see starting from Oracle 12cR2 Oracle has enhanced the OR expansion from a simple concatenation operator to a slightly more sophisticated union-all operation. The new OR-Expansion has by now been given a new name recognizable in the execution plan via the word: VW_ORE_###.

But what performance added value this transformation is supposed to bring?

Hopefully one of my real life queries gave me the answer to the above question: in contrast to its old implementation, the new enhanced 12cR2 OR-Expansion can be combined with other transformations opening, as such, a new path to the Oracle optimizer that is simply impossible to happen with the pre-12cR2 OR-Expansion.

A picture being worth a thousand of words here’s below a simple demonstration of how such a double transformation can be combined (the model comes from the previous article):

--12cR1
explain plan for
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14949 |
|*  1 |  HASH JOIN OUTER    |                 | 14949 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_C83A7ED5 |  9950 |
|*  4 |    TABLE ACCESS FULL| T2              |  9950 |
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

If you have read my previous article you will recognize in the above execution plan the Decorrelated Lateral view transformation via its name at line 3 (VW_DCL_C83A7ED5). At my client site this transformation was systematically triggering a full table scan so that I was obliged to cancel it as explained in the corresponding article. I would have instead loved if Oracle had gone a step forward and or-expanded this VW_DCL_C83A7ED5 lateral view in order to eliminate the OR disjunctive predicate to open the index access path possibility. Unfortunately combining these two transformation seems to be impossible by then.

That was until the arrival of the 12cR2 and its new enhanced OR-Expansion.

-- 12cR2 : 12.2.0.1.0
explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
---------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14950 |
|*  1 |  HASH JOIN OUTER      |                 | 14950 | 
|   2 |   TABLE ACCESS FULL   | T1              | 10000 | 
|   3 |   VIEW                | VW_DCL_C83A7ED5 |  9951 |
|   4 |    VIEW               | VW_ORE_37EAC9F1 |  9951 | 
|   5 |     UNION-ALL         |                 |       | 
|*  6 |      TABLE ACCESS FULL| T2              |  9901 | 
|*  7 |      TABLE ACCESS FULL| T2              |    50 | 
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("T1"."ID1"="ITEM_2"(+))
 6 - filter("T2"."ID1">100)
 7 - filter("T2"."START_DATE"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd 
    hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND LNNVL("T2"."ID1">100))

Spot now how the 12cR2 Or-Expansion(VW_ORE_37EAC9F1) has been combined with the 12cR1 Decorrelated Lateral view(VW_DCL_C83A7ED5) opening, as such, the possibility to visit t2 table via an index access path since the disjunctive or predicate has been removed from the refactored query (the index has not been used in my case but that’s only a question of cost).

Since the 12cR2 Or-Expansion is a cost based transformation (see below in the 10053 trace file) it happens that Oracle has decided to do not use it. This is why I used the hint OR_EXPAND in order to force it kicking in for the pedagogic purpose of this article.

In order to produce the above execution plan, Oracle has gone through the following SQL refactoring steps:

 --first it has OR-Expanded the t2 table query block and named it VW_ORE_37EAC9F1

(select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)  VW_ORE_37EAC9F1
 --it have then Decorrelated the OR-Expanded query block and named it VW_DCL_C83A7ED5 

((select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)
) VW_ORE_37EAC9F1
) VW_DCL_C83A7ED5

--and finally it joined the Decorrelated OR-Expanded t2 table query with t1 table
select
  t1.id1   id1,
  t1.flag1 flag1,
  t1.flag2 flag2,
  t1.n1    n1,
  t1.v1    v1,
  vw_dcl_c83a7Ed5.item_1_0 id1,
  vw_dcl_c83a7Ed5.item_2_1 product_t1,
  vw_dcl_c83a7Ed5.item_3_2 start_date,
  vw_dcl_c83a7Ed5.item_4_3 end_date,
  vw_dcl_c83a7Ed5.item_5_4 padding
from t1 t1,
     (select
        vw_ore_37eac9f1.item_1 item_1_0,
        vw_ore_37eac9f1.item_2 item_2_1,
        vw_ore_37eac9f1.item_3 item_3_2,
        vw_ore_37eac9f1.item_4 item_4_3,
        vw_ore_37eac9f1.item_4 item_5_4
      from
        (select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
            t2 t2
         where t2.id1 >100
        union all
         select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
           t2 t2
         where 
           t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and lnnvl(t2.id1 >100)
      )  vw_ore_37eac9f1
     ) vw_dcl_c83a7Ed5
where t1.id1 = vw_dcl_c83a7ed5.item_2_1(+);

The OR-Expansion appears first at the Legend section of the CBO 10053 trace file:

Legend
 The following abbreviations are used by optimizer trace
 CBQT  – cost-based query transformation
 ORE   - CBQT OR-Expansion
 

It can be disabled locally using the hint NO_OR_EXPAND:

explain plan for
select /*+ NO_OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14999 | 
|*  1 |  HASH JOIN OUTER    |                 | 14999 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |  
|   3 |   VIEW              | VW_DCL_C83A7ED5 | 10000 | 
|*  4 |    TABLE ACCESS FULL| T2              | 10000 | 
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

And globally via the hidden param _optimizer_cbqt_or_expansion which defaults to on:

SQL> alter system set "_optimizer_cbqt_or_expansion"= off;

explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
  
  select * from table(dbms_xplan.display);
 --------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14999 |
|*  1 |  HASH JOIN RIGHT OUTER|                 | 14999 |
|   2 |   VIEW                | VW_DCL_C83A7ED5 | 10000 |
|*  3 |    TABLE ACCESS FULL  | T2              | 10000 |
|   4 |   TABLE ACCESS FULL   | T1              | 10000 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   3 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SUMMARY

Starting from Oracle 12cR2 the OR-Expansion has been enhanced from the classical concatenation to a more flexible union-all operation. This article shows that one of the advantages brought by this enhancement resides in the possibility it offers to the CBO to combine the new OR-Expansion with other transformations like with the Decorrelated Lateral view. This might increase the chance to have optimal execution plan in queries using disjunctive predicates.

June 14, 2017

Optimiser non sharing reasons

Filed under: CBO,cursor sharing — hourim @ 4:28 pm

Whenever Oracle is prevented from sharing an execution plan of an already seen parent cursor, it hard parses a new plan and externalizes the corresponding non-sharing reason in the gv$sql_shared_cursor dedicated view. As of Oracle 12cR2 there are as many as 66 non-sharing reasons of which I have already explained 10 in the following series of articles I wrote for Toad World:

June 2, 2017

_optimizer_ansi_join_lateral_enhance

Filed under: CBO — hourim @ 1:51 pm

There was a question raised at my client site shorter after I troubleshooted a performance issue due to the new 12cR1 Decorrelated Lateral View transformation: Is there a way to disable this CBO transformation without sending back the optimizer to its previous version?”

It ‘s an interesting question which has triggered a brief discussion and this article.

My short answer was: I have to test and check

And the long answer is:

10053 to the rescue

As long as I was able to engineer a reproducible case where this CBO transformation is used, I decided to generate its corresponding 10053 trace file and look for the word “lateral” in this file. I found 14 hits of this word of which one seems to correlate very well with my aim:

 *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 12.1.0.1
_optimizer_ansi_join_lateral_enhance = true --> this one

Although there is nothing in the name of the above parameter suggesting a decorrelation of a lateral view, it is nevertheless, the unique CBO parameter whith a name including the word lateral. This suggests that setting it off will cancel this transformation. Let’s then implement this transformation and try cancelling it with setting this parameter off.

SQL> select banner from v$version where rownum =1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

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 
create index idx_t2_usr_1 on t2(product_t1, start_date);

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)
     )
  );
select * from table(dbms_xplan.display(format=>'advanced'));

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14925 |
|*  1 |  HASH JOIN OUTER    |                 | 14925 |
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_1B0973D4 |  9926 |
|*  4 |    TABLE ACCESS FULL| T2              |  9926 |
-------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6226B99A" "T2"@"SEL$1")
      USE_HASH(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$A7519627" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$A7519627" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE(@"SEL$6226B99A")
      MERGE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$0E991E55")
      OUTLINE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      OUTLINE_LEAF(@"SEL$A7519627")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE_LEAF(@"SEL$6226B99A")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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)

Notice the apparition of view operation (VW_DCL_1B0973D4) at line n°3 in the above execution plan. This is Oracle way to signal the presence of a decorrelated lateral view transformation. If you want to learn more about this new transformation then you can read this US patent

DECORRELATE and NO_DECORRELATE hint

Before we go on, I want to show you very quickly how we can locally disable this new transformation using the following hint:

NO_DECORRELATE(@"SEL$BCD4421C")
explain plan for  
select /*+ NO_DECORRELATE(@"SEL$BCD4421C") */
   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)
     )
  );

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                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| 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")

As you can see using the NO_DECORRELATE(@”SEL$BCD4421C”) hint we cancelled the VW_DCL_1B0973D4 decorrelated lateral view since we have now a simple VW_LAT_1B0973D4 lateral view

In passing it is important to note that the outline presented above contains three hints with the word DECORRELATE:

Outline Data
-------------
  /*+
      DECORRELATE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      DECORRELATE(@"SEL$BCD4421C")
  */

Of these three hints it is only the hint referencing the query block named @”SEL$BCD4421C” that will succeed to correlated/decorrelate the lateral view. I don’t know why this hint with that query block named is repeated two times. I don’t know also why using the other hint with the query block named @”SEL$6226B99A” doesn’t decorrelate the lateral view.

Looking back into the 10053 trace file I found the following lines for query blocks SEL$BCD4421C and SEL$6226B99A respectively:

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 0x8e4a5b58 (VIEW DECORRELATED SEL$BCD4421C; SEL$BCD4421C)

This tends to suggest that SEL$6226B99A represents the query block name of the lateral view and that SEL$BCD4421C represents the query block name of the decorrelated lateral view. Simply put I think that when confronted to such a kind of situation where you want to locally cancel the lateral decorrelation view than use the hint NO_DECORRELATE with the query block name that exists in the outline and doesn’t exist in the query block name given by the dbms_xplan.display_cursor:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1

Don’t ask me why not simply using the table aliases as they appear in the original query to cancel this transformation. All my attempts have been unsuccessful.

Hidden parameter

What exactly is the parameter driving this transformation?

alter session set "_optimizer_ansi_join_lateral_enhance"=false;

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)
     )
  );

select * from table(dbms_xplan.display(format =>'+outline'));
-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$BCD4421C" "T2"@"SEL$1")
      INDEX_RS_ASC(@"SEL$BCD4421C" "T2"@"SEL$1" ("T2"."PRODUCT_T1" "T2"."START_DATE"))
      USE_NL(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$0E991E55" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$0E991E55" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      MERGE(@"SEL$9EC647DD")
      OUTLINE_LEAF(@"SEL$0E991E55")
      OUTLINE_LEAF(@"SEL$BCD4421C")
      ALL_ROWS
      OPT_PARAM('_optimizer_ansi_join_lateral_enhance' 'false')
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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")

Notice again how we have succeeded to cancel the decorrelated lateral view transformation at a global level by setting off the hidden parameter _optimizer_ansi_join_lateral_enhance.

SUMMARY
In summary, does a decorrelated lateral view causes always a performance issue? No, not necessarily always. However if you fill in need of disabling it then you can:

  • Use the corresponding no_decorrelate hint with the correct query block name for a local effect
  • Set off the _optimizer_ansi_join_lateral_enhance parameter for a global effect with the usual warning when changing a hidden parameter.

October 23, 2013

CBO and unusable unique index

Filed under: CBO — hourim @ 6:50 am

Very recently a question came up on oracle-list where the original poster was wondering about two things (a) how could a refresh on materialized view allows duplicate key to be possible in the presence of a unique index and (b) he was struggling about a particular select which is giving a wrong results.

The answer to the first question is easy and I have already blogged about it. He was refreshing the materialized view using a FALSE value for the parameter atomic refresh. With this particular refresh parameter, the materialized view is refreshed  using a rapid truncate table followed by a direct path insert. Direct path load as shown in my blog, will silently disable the unique index allowing duplicate keys to be accepted. Thought that this seems to be true (in this context of materialized view refresh) only in 11gR2. The preceding release (10gR2) is not allowing duplicate keys during this kind of refresh as I’ve shown in my answer in the oracle-list forum. Does this mean that 10gR2 is not direct path loading when atomic refresh is set to FALSE? I have to check.

But what motivated the current blog article is the second question. See with me

SQL> create table a(id int,val number);
Table created.

SQL> insert into a select 1, 1 from dual;
1 row created.

SQL> create table b(id int);
Table created.

SQL> create unique index uq_b on b(id);
Index created.

Then I will use a sqlloader to load data into table b using a direct path load in order to silently disable the unique index. The control file(c.ctl) I will be using resembles to:

LOAD DATA
INFILE *
REPLACE
INTO TABLE B
(id position(1:1) TERMINATED BY ",")
BEGINDATA
1;
1;

And now I will launch the sqlloader

C:\>sqlldr user/paswd@database control=c.ctl direct=true

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Oct 22 16:46:06 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Load completed - logical record count 3.

What do you think it happens to the unique index and to the table b after this direct path load?


SQL> select index_name, status from user_indexes where index_name ='UQ_B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           UNUSABLE

SQL> select count(1) from b;

COUNT(1)
----------
2

The unique index has been disabled and there are duplicate keys in table b.

So far so good.

Let’s now start exploring the Original Poster queries problem

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    15 (100)|          |
|   1 |  TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2) 

The CBO knows that there is a unique index on b(id). And, as far as there is one id in table a, the CBO, obviously, assumes that there will be only one record for the corresponding id in table b. This is why table b is not present in the execution plan by the way. Unfortunately the unique index has been disabled by the direct path load and has permitted the presence of duplicate record in table b. This is the reason why the query is producing a wrong result.

If we force the CBO to access the table b the result is however correct

SQL>  select a.*,b.id
from a, b
where a.id = b.id(+);

ID        VAL         ID
---------- ---------- ----------
1          1          1
1          1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    78 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    26 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Note
-----
- dynamic sampling used for this statement (level=2)

It is clear that the CBO is not looking the unique index status during the optimization (plan generation) phase.

But, what if instead of the unusable unique index, we have a disabled unique constraint? Will the CBO consider the status of the unique constraint in this case?

SQL> alter table b add constraint b_uk unique (id) disable;
Table altered.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1

Oups. The CBO is still wrong. What if we drop the culprit index?

SQL> drop index uq_b;
Index dropped.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    18 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |     6 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |     6 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Which finally gives a correct result.

Bottom line: always make sure your unique indexes are usable.

UPDATE 27-12-2013 :  following a discussion here,Ted from the Oracle Global Customer Support, pointed me to the bug number 17533502 which seems very close the problem exposed here. Ted also pointedme to a work arround solution as shown below:

SQL> select * from v$version where rownum=1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- -------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID   VAL
--- ----
 1   1

SQL> ALTER SESSION SET "_optimizer_join_elimination_enabled" = false;

Session altered.

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID  VAL
--- -----
 1  1
 1  1

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)