Re: Query Transformation
From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Mon, 25 Jan 2021 10:45:25 +0000
Message-ID: <CABx0cSUuQnJDWcrsPG__w_Tymop_iyo0ryW3wBYffaSdWFZJNA_at_mail.gmail.com>
SQL_ID dkdkdpmc1r96j, child number 1
SELECT /*+gather_plan_statistics z */ 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
| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 |
24 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
Date: Mon, 25 Jan 2021 10:45:25 +0000
Message-ID: <CABx0cSUuQnJDWcrsPG__w_Tymop_iyo0ryW3wBYffaSdWFZJNA_at_mail.gmail.com>
Good point Jonathan, yes, cost-based or-expansion was disabled. With default settings Optimizer comes up with slightly more understandable plan as below:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST +adaptive'));
PLAN_TABLE_OUTPUT
SQL_ID dkdkdpmc1r96j, child number 1
SELECT /*+gather_plan_statistics z */ 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: 3520672463
| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 |
| 0 |00:00:00.01 | 265 | | | |
| 1 | VIEW | VW_ORE_DC6A637B | 1 |
2 | 0 |00:00:00.01 | 265 | | | |
| 2 | UNION-ALL | | 1 |
| 0 |00:00:00.01 | 265 | | | |
| * 3 | HASH JOIN | | 1 |
1 | 0 |00:00:00.01 | 4 | 1953K| 1953K| 577K (0)|
|- * 4 | HASH JOIN | | 1 |
2 | 0 |00:00:00.01 | 4 | 1942K| 1942K| |
| 5 | NESTED LOOPS | | 1 |
2 | 0 |00:00:00.01 | 4 | | | |
| 6 | NESTED LOOPS | | 1 |
2 | 0 |00:00:00.01 | 4 | | | |
|- 7 | STATISTICS COLLECTOR | | 1 |
| 0 |00:00:00.01 | 4 | | | |
| * 8 | INDEX RANGE SCAN | C1_I | 1 |
2 | 0 |00:00:00.01 | 4 | 1025K| 1025K| |
| * 9 | INDEX UNIQUE SCAN | P_PK | 0 |
1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 10 | TABLE ACCESS BY INDEX ROWID | P | 0 |
1 | 0 |00:00:00.01 | 0 | | | |
|- 11 | TABLE ACCESS STORAGE FULL | P | 0 |
1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| * 12 | INDEX RANGE SCAN | C21I | 0 |
494K| 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| * 13 | HASH JOIN | | 1 |
1 | 0 |00:00:00.01 | 261 | 2058K| 2058K| 1770K (0)|
|- 14 | NESTED LOOPS | | 1 |
1 | 3 |00:00:00.01 | 15 | | | |
|- 15 | STATISTICS COLLECTOR | | 1 |
| 3 |00:00:00.01 | 15 | | | |
|- * 16 | HASH JOIN | | 1 |
2 | 3 |00:00:00.01 | 15 | 1932K| 1932K| |
| 17 | NESTED LOOPS | | 1 |
2 | 3 |00:00:00.01 | 15 | | | |
|- 18 | STATISTICS COLLECTOR | | 1 |
| 3 |00:00:00.01 | 4 | | | |
| * 19 | INDEX RANGE SCAN | C1_I | 1 |
2 | 3 |00:00:00.01 | 4 | 1025K| 1025K| |
| 20 | TABLE ACCESS BY INDEX ROWID| P | 3 |
1 | 3 |00:00:00.01 | 11 | | | |
| * 21 | INDEX UNIQUE SCAN | P_PK | 3 |
1 | 3 |00:00:00.01 | 8 | 1025K| 1025K| |
|- 22 | TABLE ACCESS STORAGE FULL | P | 0 |
1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|- * 23 | INDEX RANGE SCAN | C21I | 0 |
1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| * 24 | INDEX RANGE SCAN | C21I | 1 |
1 | 29705 |00:00:00.01 | 246 | 1025K| 1025K| |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P"."RYGLBA"="C2"."GMAID")
4 - access("P"."RYPYID"="C1"."RZPYID")
8 - access("C1"."RZVR01"=:NC1)
9 - access("P"."RYPYID"="C1"."RZPYID")
12 - access("C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ"<=U'2299')
13 - access("P"."RYGLBA"="C2"."GMAID")
filter((LNNVL("C1"."RZVR01"=:NC1) OR LNNVL("C2"."GMOBJ">=U'2201') OR
LNNVL("C2"."GMOBJ"<=U'2299')))
16 - access("P"."RYPYID"="C1"."RZPYID")
19 - access("C1"."RZVR01"=:NC2)
21 - access("P"."RYPYID"="C1"."RZPYID")
23 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT' AND
"P"."RYGLBA"="C2"."GMAID")
filter((LNNVL("C1"."RZVR01"=:NC1) OR LNNVL("C2"."GMOBJ">=U'2201') OR
LNNVL("C2"."GMOBJ"<=U'2299')))
24 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
Note
- statistics feedback used for this statement
- this is an adaptive plan (rows marked '-' are inactive)
On Mon, 25 Jan 2021 at 10:30, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
>
> 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_at_gmail.com> 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'))))
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 25 2021 - 11:45:25 CET