That looks broken.
My first question, though, is why it's used Concatenation (Legacy
Or-expansion) rather than Cost-based OR-expansion - has the CBORE been
disabled by parameter setting or hint?
This looks like Oracle has applied a generic mechanism for or-expansion
that has produced redundant branches. I'm wondering it has applied LORE
twice and then applied a view-factoring collapse (I've forgotten the proper
name of the transformation) once to end up with three branches out of 4.
The "obvious" point where the plan is displaying buggy behaviour is in the
predicate information for operation 7
7 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
filter((("C1"."RZVR01"=:NC1 AND "C2"."GMOBJ">=U'2201' AND
"C2"."GMOBJ"<=U'2299') OR
("C1"."RZVR01"=:NC2 AND "C2"."GMOBJ"=U'2019' AND
"C2"."GMSUB"=U'IDT')))
The access predicate says: "gmobj = 2019"
One part of the filter predicate says: "gmobj between '2201' and '2299'
That branch of the filter preidcate is clearly never going to be true, and
should have been eliminated. This is why I think that the optimizer may
have got to this point by expanding to 4 branches then collapsing to 3.
Whatever it has done, there's clearly a predicate here that is badly formed
and that suggests a defect in the optimizer code - and once you hit a
defect there's (usually) no way of understanding what happens next.
The 10053 might show you how Oracle got to this point - but it you would
probably have to hunt a very messy trail.
Regards
Jonathan Lewis
On Mon, 25 Jan 2021 at 09:39, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:
Dear List,
On Oracle 19c been looking at a poorly performing plan for a little while
now. I've simplified it somewhat as per the details below.
SQL_ID 5f67d104uv8ht, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics Y */ null FROM P, C1, C2 WHERE ( (
C1.RZVR01 = :nc1 AND C2.GMOBJ BETWEEN '2201' AND '2299') OR (
C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' ) ) AND
( P.RYPYID = C1.RZPYID AND P.RYGLBA = C2.GMAID )
Plan hash value: 1470685083
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:00.18 | 89916 |
| 1 | CONCATENATION | | 1 | | 0
|00:00:00.18 | 89916 |
| 2 | NESTED LOOPS | | 1 | 1 | 0
|00:00:00.18 | 89886 |
| 3 | NESTED LOOPS | | 1 | 1 | 89115
|00:00:00.13 | 771 |
| 4 | NESTED LOOPS | | 1 | 1 | 89115
|00:00:00.06 | 745 |
| 5 | INLIST ITERATOR | | 1 | | 3
|00:00:00.01 | 8 |
|* 6 | INDEX RANGE SCAN | C1_I | 2 | 3 | 3
|00:00:00.01 | 8 |
|* 7 | INDEX RANGE SCAN | C21I | 3 | 1 | 89115
|00:00:00.05 | 737 |
|* 8 | INDEX UNIQUE SCAN | P_PK | 89115 | 1 | 89115
|00:00:00.04 | 26 |
|* 9 | TABLE ACCESS BY INDEX ROWID | P | 89115 | 1 | 0
|00:00:00.05 | 89115 |
| 10 | NESTED LOOPS | | 1 | 1 | 0
|00:00:00.01 | 26 |
| 11 | NESTED LOOPS | | 1 | 1 | 3
|00:00:00.01 | 15 |
|* 12 | INDEX RANGE SCAN | C1_I | 1 | 1 | 3
|00:00:00.01 | 4 |
| 13 | TABLE ACCESS BY INDEX ROWID | P | 3 | 1 | 3
|00:00:00.01 | 11 |
|* 14 | INDEX UNIQUE SCAN | P_PK | 3 | 1 | 3
|00:00:00.01 | 8 |
|* 15 | INDEX RANGE SCAN | C21I | 3 | 1 | 0
|00:00:00.01 | 11 |
| 16 | NESTED LOOPS | | 1 | 1 | 0
|00:00:00.01 | 4 |
| 17 | NESTED LOOPS | | 1 | 1 | 0
|00:00:00.01 | 4 |
| 18 | NESTED LOOPS | | 1 | 1 | 0
|00:00:00.01 | 4 |
|* 19 | INDEX RANGE SCAN | C1_I | 1 | 1 | 0
|00:00:00.01 | 4 |
| 20 | TABLE ACCESS BY INDEX ROWID| P | 0 | 1 | 0
|00:00:00.01 | 0 |
|* 21 | INDEX UNIQUE SCAN | P_PK | 0 | 1 | 0
|00:00:00.01 | 0 |
|* 22 | INDEX UNIQUE SCAN | C2_PK | 0 | 1 | 0
|00:00:00.01 | 0 |
|* 23 | TABLE ACCESS BY INDEX ROWID | C2 | 0 | 1 | 0
|00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(("C1"."RZVR01"=:NC1 OR "C1"."RZVR01"=:NC2))
7 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
filter((("C1"."RZVR01"=:NC1 AND "C2"."GMOBJ">=U'2201' AND
"C2"."GMOBJ"<=U'2299') OR
("C1"."RZVR01"=:NC2 AND "C2"."GMOBJ"=U'2019' AND
"C2"."GMSUB"=U'IDT')))
8 - access("P"."RYPYID"="C1"."RZPYID")
9 - filter("P"."RYGLBA"="C2"."GMAID")
12 - access("C1"."RZVR01"=:NC2)
filter(("C1"."RZVR01"=:NC1 OR "C1"."RZVR01"=:NC2))
14 - access("P"."RYPYID"="C1"."RZPYID")
15 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT' AND
"P"."RYGLBA"="C2"."GMAID")
filter(("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ">=U'2201' AND
(LNNVL("C2"."GMSUB"=U'IDT') OR LNNVL("C2"."GMOBJ"=U'2019'))))
19 - access("C1"."RZVR01"=:NC1)
filter(("C1"."RZVR01"=:NC1 OR "C1"."RZVR01"=:NC2))
21 - access("P"."RYPYID"="C1"."RZPYID")
22 - access("P"."RYGLBA"="C2"."GMAID")
23 - filter(("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ"<=U'2299' AND
"C2"."GMOBJ">=U'2201'
AND "C2"."GMOBJ">=U'2201' AND (LNNVL("C1"."RZVR01"=:NC2) OR
LNNVL("C2"."GMOBJ"=U'2019') OR
LNNVL("C2"."GMSUB"=U'IDT')) AND (LNNVL("C2"."GMSUB"=U'IDT')
OR
LNNVL("C2"."GMOBJ"=U'2019'))))
