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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx>
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.
