Re: Query Transformation

No need to apologize, appreciate the effort. I'm spending the time to reproduce and build a test-case on my lab environment. Did anybody (JL?) say that the hardest part of the problem is building the simplest possible test-case, once you've done that the rest is plain sailing. (If not I'll claim it).
More motivated now I've got confirmation that the plan is crazy, I was thinking it was my brain that was at fault for not understanding it. I'll update if I get anywhere
Thanks
Patrick

On Tue, 26 Jan 2021 at 22:26, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> so sorry. since you already have the 10053, that tells you how the CBO got
> the plan.
>
>
>
> I thought you wanted a useful plan. Reducing the pieces of the Cartesian
> product inputs is an odds on favorite to do that, but if it is already
> plenty fast for you, never mind.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Patrick Jolliffe
> *Sent:* Tuesday, January 26, 2021 3:40 AM
> *To:* oracle-l
> *Cc:* Mark W. Farnham
> *Subject:* Re: Query Transformation
>
>
>
> Thanks Mark, but as detailed, concern is not about optimizing SQL which I
> have already done, just understanding how and why the optimizer came up
> with the plan for the unchanged SQL.
>
>
>
> On Mon, 25 Jan 2021 at 17:31, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> First: Just made it so I could read it
>
>
>
> 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
>
>
>
> Second: Made your claimed correction to a single bind with :nc1 = :nc2, by
> the way presuming they are not allowed to be null, since
>
> they appear in an equals.
>
>
>
> SELECT /*+gather_plan_statistics Y */ null FROM P, C1, C2
>
> WHERE
>
> (
>
> (C1.RZVR01 = :nc1 AND C2.GMOBJ BETWEEN '2201' AND '2299')
>
> OR (C1.RZVR01 = :nc1 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT')
>
> )
>
> AND P.RYPYID = C1.RZPYID
>
> AND P.RYGLBA = C2.GMAID
>
>
>
> Third: reformed with C1 and C2 pruned prior to the join.
>
>
>
> with c1_pruned as (select c1.rzpyid from c1 where c1.rzvr01 = :nc1)
>
> ,c2_pruned as (select c2.gmaid from c2
>
> where c2.gmobj between '2201' and '2299'
>
> or (c2.gmobj = '2019' and c2.gmsub = 'IDT')
>
> )
>
> select /* */ null from p, c1_pruned, c2_pruned
>
> where p.rypyid = c1_pruned.rzpyid
>
> and p.ryglba = c2_pruned.gmaid
>
>
>
> Now, IF the single table predicate selectivity is good (meaning highly
> selective) on c1 and/or c2, then they should appear as relatively
>
> smaller objects for their respective joins with p. IF nc1 and nc2 must be
> identical, notice that these single table prunings don't
>
> involve a join at all. You possibly need no merge hints on the with clause
> objects in case Oracle evaluates the cost is better pruning
>
> on the join clauses. IF nc1 and nc2 actually can be different, then you
> can factor out c1_pruned_nc1 and c1_pruned_nc2 and produced with results
> for each half of the or on c2.
>
>
>
> Good luck.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Patrick Jolliffe
> *Sent:* Monday, January 25, 2021 4:40 AM
> *To:* oracle-l
> *Subject:* Query Transformation
>
>
>
> 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.
>
> I've resolved the problem itself by
>
> 1) creating an extended statistic for column group GMOBJ, GMSUB (which
> were correlated)
>
> 2) getting developers to replace the 2 binds :nc1 and :nc2 with a single
> bind, as the values are actually the same although the optimizer doesn't
> know this, so this limits it's options.
>
> However I've got myself bogged down in trying to understand the
> transformation that has taken place, really for intellectual curiosity.
>
> I think it's some variation on "cost based or expansion", but how has it
> transformed into a union of 3 different parts?
>
> Any help greatly appreciated
>
> Patrick
>
>
>
> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS
> LAST'));
>
>
> PLAN_TABLE_OUTPUT
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 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-l