Patrick,
This looks like a different query from the original - it's showing a
predicate "C2"."GMOBJ" = '2299' when the query was originally C2.GMOBJ
between '2201' and '2299'.
I've taken another look at the original query and plan and only just
realised how crazy the plan was. There's no join condition between c1 and
c2, yet the first branch of the union all shows a nested loop between them
- of course the predicted numbers involved were small, but I think it still
should have been reported as a Cartesian merge join.
Regards
Jonathan Lewis
On Mon, 25 Jan 2021 at 11:56, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:
Jonathan,
Back to your initial guess, yes it does appear that LORE has been applied
twice, per extracts from 10053 below.
Patrick
(newjo-save) [1 2 0 ]
LORE: Trying or-Expansion on query block SEL$1 (#0)
LORE: ******** Next OR predicate ********
LORE: Trying or-Expansion on query block SEL$1 (#0) on OR chain:
id=0 predicate=("C2"."GMOBJ"=U'2299' OR "C2"."GMSUB"=U'IDT' AND
"C2"."GMOBJ"=U'2019') AND ("C1"."RZVR01"=:B1 AND "C2"."GMOBJ"=U'2299' OR
"C1"."RZVR01"=:B2 AND "C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT') AND
"P"."RYPYID"="C1"."RZPYID" AND "P"."RYGLBA"="C2"."GMAID"
LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdceff00)
****************
QUERY BLOCK TEXT
****************
SELECT null FROM P, C1, C2 WHERE
( ( C1.RZVR01 = :nc1 AND c2.GMOBJ = '2299') OR
( C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' ) )
AND
( P.RYPYID = C1.RZPYID AND P.RYGLBA = C2.GMAID )
---
LOTS OF STUFF OMITTED
---
LORE: or-expansion-subheap (delete addr=0x7fb0bdceff00, in-use=119328,
alloc=126408)
LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdceff00)
****************
QUERY BLOCK TEXT
****************
SELECT null FROM P, C1, C2 WHERE
( ( C1.RZVR01 = :nc1 AND c2.GMOBJ = '2299') OR
( C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' ) )
AND
( P.RYPYID = C1.RZPYID AND P.RYGLBA = C2.GMAID )
On Mon, 25 Jan 2021 at 11:46, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
"Doesn't work at all" might be a little bit of hyperbole, but I've just
done a quick search on MOS for "_optimizer_cbqt_or_expansion" and it
reported a significant number of documents and bugs which suggested it be
set to OFF.
Regards
Jonathan Lewis
On Mon, 25 Jan 2021 at 11:13, Moustafa Ahmed <moustafa_dba@xxxxxxxxxxx>
wrote:
I heard from someone in oracle real world performance team that
_optimizer_cbqt_or_expansion
Must be disabled at 19c
As it has a bug and it does not work at all!
On Jan 25, 2021, at 5:44 AM, Patrick Jolliffe <jolliffe@xxxxxxxxx>
wrote:
cost-based or-expansion
