Re: Query Transformation

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Tue, 16 Feb 2021 14:14:56 +0000
Message-ID: <CABx0cSViNpz+vHq2rU9z=-voejd9xpt1HOtdKW4YezX4buOJ-A_at_mail.gmail.com>

Been looking at this on and off for the last few weeks. Below is my simplest testcase, it reproduces on my plain 19c Docker Image. Getting a bit bogged down in the exact species of OR expansion it is making and why.
I'm going to keep looking, but thought I'd share my (small) progress. Patrick

drop table l purge;

create table l nologging
as
select rownum id1,

       rownum id2
from dual
connect by level <= 10;

alter table l add constraint l_id1 primary key(id1);

exec dbms_stats.gather_table_stats(null, 'l');

drop table t1 purge;

create table t1 nologging
as
select ROWNUM id1,

        MOD(ROWNUM, 5) t1f
from dual connect by rownum <= 10;
create index t1_t1f_id1 ON t1 (t1f, id1);

exec dbms_stats.gather_table_stats(null, 't1');

drop table t2 purge;

create table t2 nologging
as

select ROWNUM  id2,
        MOD(ROWNUM, 8) t2f1,
        MOD(ROWNUM, 8) t2f2

from dual
connect by level <= 10000;

alter table t2 add constraint t2_id2 primary key (id2);

create index t2_t2f1_t2f2_id2 on t2(t2f1, t2f2, id2);

exec dbms_stats.gather_table_stats(null, 't2')

explain plan for
select /*+ OPT_PARAM('_optimizer_cbqt_or_expansion' 'off') */ null   from t1
  join l on l.id1 = t1.id1

  join t2 on t2.id2 = l.id2
  where  ( ( t1.t1f = 0 AND t2.t2f1 BETWEEN 9 AND 10)       OR
           ( t1.t1f = 1 AND t2.t2f1 = 14 AND t2.t2f2 = 13  )   );

select * from dbms_xplan.display();

---

| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 3 | 66
| 10 (0)| 00:00:01 |
| 1 | CONCATENATION | | |
| | |
| 2 | NESTED LOOPS | | 1 | 22
| 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 22
| 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 16
| 3 (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN           | T2_T2F1_T2F2_ID2 |     1 |    10


|     2   (0)| 00:00:01 |

|   6 |      INLIST ITERATOR            |                  |       |


|            |          |


|*  7 |       INDEX RANGE SCAN          | T1_T1F_ID1       |     1 |     6


|     1   (0)| 00:00:01 |


|*  8 |     INDEX UNIQUE SCAN           | L_ID1            |     1 |


|     0   (0)| 00:00:01 |


|*  9 |    TABLE ACCESS BY INDEX ROWID  | L                |     1 |     6


|     1   (0)| 00:00:01 |

|  10 |   NESTED LOOPS                  |                  |     1 |    22

|     3   (0)| 00:00:01 |

|  11 |    NESTED LOOPS                 |                  |     1 |    12


|     2   (0)| 00:00:01 |


|* 12 |     INDEX RANGE SCAN            | T1_T1F_ID1       |     1 |     6


|     1   (0)| 00:00:01 |

|  13 |     TABLE ACCESS BY INDEX ROWID | L                |     1 |     6


|     1   (0)| 00:00:01 |


|* 14 |      INDEX UNIQUE SCAN          | L_ID1            |     1 |


|     0   (0)| 00:00:01 |


|* 15 |    INDEX RANGE SCAN             | T2_T2F1_T2F2_ID2 |     1 |    10


|     1   (0)| 00:00:01 |

|  16 |   NESTED LOOPS                  |                  |     1 |    22

|     3   (0)| 00:00:01 |

|  17 |    NESTED LOOPS                 |                  |     1 |    22

|     3   (0)| 00:00:01 |

|  18 |     NESTED LOOPS                |                  |     1 |    12


|     2   (0)| 00:00:01 |


|* 19 |      INDEX RANGE SCAN           | T1_T1F_ID1       |     1 |     6


|     1   (0)| 00:00:01 |

|  20 |      TABLE ACCESS BY INDEX ROWID| L                |     1 |     6


|     1   (0)| 00:00:01 |


|* 21 |       INDEX UNIQUE SCAN         | L_ID1            |     1 |


|     0   (0)| 00:00:01 |


|* 22 |     INDEX UNIQUE SCAN           | T2_ID2           |     1 |


|     0   (0)| 00:00:01 |


|* 23 |    TABLE ACCESS BY INDEX ROWID  | T2               |     1 |    10


|     1   (0)| 00:00:01 |


----------------------------------------------------------------------------------------------------

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

   5 - access("T2"."T2F1"=14 AND "T2"."T2F2"=13)
   7 - access("T1"."T1F"=0 OR "T1"."T1F"=1)
       filter("T1"."T1F"=0 AND "T2"."T2F1">=9 AND "T2"."T2F1"<=10 OR
"T1"."T1F"=1 AND
              "T2"."T2F1"=14 AND "T2"."T2F2"=13)
   8 - access("L"."ID1"="T1"."ID1")
   9 - filter("T2"."ID2"="L"."ID2")
  12 - access("T1"."T1F"=1)
       filter("T1"."T1F"=0 OR "T1"."T1F"=1)
  14 - access("L"."ID1"="T1"."ID1")
  15 - access("T2"."T2F1"=14 AND "T2"."T2F2"=13 AND "T2"."ID2"="L"."ID2")
       filter("T2"."T2F1">=9 AND "T2"."T2F1"<=10 AND (LNNVL("T2"."T2F1"=14)
OR
              LNNVL("T2"."T2F2"=13)))
  19 - access("T1"."T1F"=0)
       filter("T1"."T1F"=0 OR "T1"."T1F"=1)
  21 - access("L"."ID1"="T1"."ID1")
  22 - access("T2"."ID2"="L"."ID2")
  23 - filter("T2"."T2F1">=9 AND "T2"."T2F1">=9 AND "T2"."T2F1"<=10 AND
"T2"."T2F1"<=10 AND
              (LNNVL("T1"."T1F"=1) OR LNNVL("T2"."T2F1"=14) OR
LNNVL("T2"."T2F2"=13)) AND
              (LNNVL("T2"."T2F1"=14) OR LNNVL("T2"."T2F2"=13)))

On Sat, 30 Jan 2021 at 18:52, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> I know I'm going to have to delve back into the 10053 trace at some point,
> but keep postponing that by working on test-case.
> Below is progress so far if anyone is interested.
> Continuing tomorrow
> Patrick
>
>
> drop table l purge;
>
> create table l nologging
> as
> with generator as (
> select --+ materialize
> rownum id
> from dual
> connect by
> level <= 1e4
> )
> select ROWNUM id,
> to_char(MOD(ROWNUM, 10000), '99999999') id2 from
> generator g1,
> generator g2
> where rownum <= 400000;
>
> alter table l add constraint l_pk primary key(id);
> exec dbms_stats.gather_table_stats(null, 'l');
>
> drop table t1 purge;
>
> create table t1 nologging
> as
> with generator as (
> select --+ materialize
> rownum id
> from dual
> connect by
> level <= 1e4
> )
> select
> to_char(MOD(ROWNUM, 10000000), '99999999') c1,
> MOD(ROWNUM, 1000000) l_id
> from
> generator v1,
> generator v2
> where
> rownum <= 16000000;
>
> CREATE INDEX t1_I ON t1 (c1, l_id);
>
> exec dbms_stats.gather_table_stats(null, 't1');
>
> drop table t2 purge;
>
> create table t2 nologging
> as
> with generator as (
> select --+ materialize
> rownum id
> from dual
> connect by
> level <= 1e4
> )
> select CAST(ROWNUM AS CHAR(16)) id,
> to_char(MOD(ROWNUM, 1000), '9999') m1000,
> to_char(MOD(ROWNUM, 2000), '9999') m2000
> from
> generator v1,
> generator v2
> where rownum <= 1000000;
>
> alter table t2 add constraint t2_pk primary key (id);
> create index t2_i on t2(m1000, m2000, id);
> exec dbms_stats.gather_table_stats(null, 't2')
>
> var v1 char(30)
> var v2 char(30)
>
> exec :v1 := 'A';
> exec :v2 := 'A';
>
> explain plan for SELECT /*+ OPT_PARAM('_optimizer_cbqt_or_expansion'
> 'off') */ null FROM l, t1, t2 WHERE
> ( ( t1.c1 = :v1 AND t2.m1000 BETWEEN 'X' AND 'Y') OR
> ( t1.c1 = :v2 AND t2.m1000 = 'Z' AND t2.m2000 = 'A' ) ) AND
> ( l.id = t1.l_id AND l.id2 = t2.id );
>
> select * from dbms_xplan.display();
>
>
> On Thu, 28 Jan 2021 at 15:00, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:
>
>> You are correct in that it has nothing to do with NCHAR/NVARCHAR2 -
>> replacing those in testcase still results in the  'crazy' plan
>>
>> On Thu, 28 Jan 2021 at 14:50, Patrick Jolliffe <jolliffe_at_gmail.com>
>> wrote:
>>
>>> My bad, you need to stick the following lines at the beginning:
>>>
>>> var nc1  nchar(30)
>>>
>>> var nc2  nchar(30)
>>>
>>>
>>> exec :nc1 := 'A';
>>>
>>> exec :nc2 := 'A';
>>>
>>>
>>> On Thu, 28 Jan 2021 at 14:41, Patrick Jolliffe <jolliffe_at_gmail.com>
>>> wrote:
>>>
>>>> Thanks Jonathan,
>>>> (Hope I have ) Managed to reproduce it from scratch on a pristine 19.3
>>>> environment ('standard' Oracle docker image in this case).
>>>> Not hugely pleased about the SET_TABLE_STATS step, but we do what we
>>>> can.
>>>> SQL for testcase and my output attached.
>>>> Going to keep on digging, just glad to have it confirmed that the plan
>>>> is indeed crazy and it's not (only) my ability to understand it lacking.
>>>> Best Regards
>>>> Patrick
>>>>
>>>> On Thu, 28 Jan 2021 at 14:09, Jonathan Lewis <jlewisoracle_at_gmail.com>
>>>> wrote:
>>>>
>>>>>
>>>>> I should have said that in my model both the varchar2() and
>>>>> nvarchar2() plans produced exactly the same 4-branch concatenation.
>>>>> The indications were then that the generation of the branch code is
>>>>> purely formulaic and "unthinking" because in my case two of the branches
>>>>> had an access predicate of
>>>>>     access("T1"."NV1"=U'0050')
>>>>>
>>>>> followed in one case by the silly filter predicate of
>>>>>     filter(("T1"."NV1"<=U'0200' AND "T1"."NV1">=U'0100')
>>>>>
>>>>> and in the other case by the even sillier filter predicate:
>>>>>     filter(("T1"."NV1"<=U'0200' AND "T1"."NV1">=U'0100') AND
>>>>> LNNVL("T1"."NV1"=U'0050')))
>>>>>
>>>>> Really the optimizer should have spotted the contradiction and
>>>>> filtered out these two branches
>>>>>
>>>>> This was all running 19.3.
>>>>>
>>>>> Regards
>>>>> Jonathan Lewis
>>>>>
>>>>>
>>>>>
>>>>> On Wed, 27 Jan 2021 at 12:32, Patrick Jolliffe <jolliffe_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> I *think* it's related to national characterset (NCHAR/NVARCHAR2).
>>>>>> As shown below (I hope) current testcase does not reproduce if I switch to
>>>>>> CHAR/VARCHAR2.
>>>>>> Investigation continues, trying to build complete reproducable
>>>>>> testcase from scratch.
>>>>>>
>>>>>>
>>>>>>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 16 2021 - 15:14:56 CET