Re: Query Transformation

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_at_gmail.com> 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_at_gmail.com>
> 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_at_hotmail.com>
>> 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_at_gmail.com>
>>> wrote:
>>>
>>> cost-based or-expansion
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l