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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *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'))))
