RE: Query Transformation
Date: Tue, 26 Jan 2021 17:26:07 -0500
Message-ID: <269201d6f432$3e6be990$bb43bcb0$_at_rsiz.com>
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
- creating an extended statistic for column group GMOBJ, GMSUB (which were correlated)
- 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-lReceived on Tue Jan 26 2021 - 23:26:07 CET