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
