Re: Query Transformation
Date: Tue, 16 Feb 2021 15:40:37 +0100
Message-ID: <482ff03b-a304-f0b5-3183-271657ec98f9_at_bluewin.ch>
What does your outline say?
Am 16.02.2021 um 15:14 schrieb Patrick Jolliffe:
(display_cursor(...,format=>'BASIC +OUTLINE'));
> 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
>
>
> droptable l purge;
> createtable l nologging
> as
> select rownum id1,
> rownum id2
> from dual
> connectbylevel <= 10;
> altertable l addconstraint l_id1 primarykey(id1);
> exec dbms_stats.gather_table_stats(null, 'l');
> droptable t1 purge;
> createtable t1 nologging
> as
> select ROWNUM id1,
> MOD(ROWNUM, 5) t1f
> from dual connectby rownum <= 10;
> createindex t1_t1f_id1 ON t1 (t1f, id1);
> exec dbms_stats.gather_table_stats(null, 't1');
> droptable t2 purge;
> createtable t2 nologging
> as
> select ROWNUM id2,
> MOD(ROWNUM, 8) t2f1,
> MOD(ROWNUM, 8) t2f2
> from dual
> connectbylevel <= 10000;
> altertable t2 addconstraint t2_id2 primarykey (id2);
> createindex 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 = 0AND t2.t2f1 BETWEEN9AND10) OR
> ( t1.t1f = 1AND t2.t2f1 = 14AND 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 |
> | 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
> <mailto: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 <http://l.id> = t1.l_id AND l.id2 = t2.id <http://t2.id> );
> select * from dbms_xplan.display();
>
> On Thu, 28 Jan 2021 at 15:00, Patrick Jolliffe <jolliffe_at_gmail.com
> <mailto: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 <mailto: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 <mailto: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
> <mailto: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 <mailto: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-lReceived on Tue Feb 16 2021 - 15:40:37 CET