Re: Query Transformation

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Mon, 25 Jan 2021 12:38:00 +0000
Message-ID: <CABx0cSUhiwNYYqzUL6GDbJEg_RRXmc=-4Qr2PCowD1Tw5iwZcQ_at_mail.gmail.com>

Sorry, below grep of 10053 is probably more relevant. The 2 chosen LORE transformations are in bold

LORE - Legacy OR-Expansion

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:
LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdceff00)
LORE: or-expansion-subheap (delete addr=0x7fb0bdceff00, in-use=119328,

alloc=126408)

LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdceff00)
LORE: or-expansion-subheap (delete addr=0x7fb0bdceff00, in-use=101256,

alloc=106432)
*LORE: or-expansion is better cost:12859.869164*

LORE: ******** Same chain after expansion ********
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:
LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdd57dc8)
LORE: or-expansion-subheap (delete addr=0x7fb0bdd57dc8, in-use=111976,

alloc=122512)

LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdd57dc8)
LORE: or-expansion-subheap (delete addr=0x7fb0bdd57dc8, in-use=116080,

alloc=124800)

LORE: or-expansion is worse cost:20.005612
LORE: Trying or-Expansion on query block SEL$1 (#0)
LORE: Or-expansion bypassed: No index driver found in OR chain:
LORE: ******** Next OR predicate ********
LORE: Trying or-Expansion on query block SEL$1 (#0) on OR chain:
LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdd7dab8)
LORE: or-expansion-subheap (delete addr=0x7fb0bdd7dab8, in-use=91224,

alloc=96000)

LORE: ******** OR-branching ********
LORE: or-expansion-subheap (create addr=0x7fb0bdd83d20)
LORE: or-expansion-subheap (delete addr=0x7fb0bdd83d20, in-use=118944,

alloc=123768)
*LORE: or-expansion is better cost:18.003667*

LORE: ******** Same chain after expansion ********
LORE: Trying or-Expansion on query block SEL$1 (#0)
LORE: Or-expansion bypassed: No index driver found in OR chain:
LORE: Trying or-Expansion on query block SEL$1 (#0)
LORE: Or-expansion bypassed: No index driver found in OR chain:
LORE: Or-expansion bypassed: No index driver found in OR chain:
LORE: ******** Final costing  ********
LORE: Predicate chain for or-expanded branch SEL$1_1 is:
LORE: Predicate chain for or-expanded branch SEL$1_2 is:
LORE: Predicate chain for or-expanded branch SEL$1_3 is:

On Mon, 25 Jan 2021 at 11:57, 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

Received on Mon Jan 25 2021 - 13:38:00 CET