Re: bloom filter not used after 19C upgrade

Hi Laurentiu,

did you try optimizer_adaptive_features = false too?

As for the SR, maybe it helps if you let Nigel Bayliss know.

Thanks

Lothar

Am 10.05.2024 um 10:51 schrieb Laurentiu Oprea:
> THank you Lothar,
>
> Indeed the client opened an SR but we all know how long is taking some
> time for resolutions....well see how it goes.
>
> I tried basically everything I even disabled every bug fix for 12.2,
> 18, 19 , played around with bloom fiter parameters.,  took outline
> from resulted plan which is fine with literals ... as of now I`m in
> MOS hands.
>
> I enabled tracing for bloom filters but I can`t figure out very well
> why are not used due to huge size of traces and also not very
> experienced on what to look for exactly in such scenarios
>
> În vin., 10 mai 2024 la 10:42, Lothar Flatz <l.flatz_at_bluewin.ch> a scris:
>
> Hi Larentiu,
>
> the 10053 seems to indicate that a bloom filter can be used in
> principle.
> I have to guess for an answer, So please note my caveat. I think
> the text below means that:
> You have an adaptive plan which could be switched.
> (https://oracle-base.com/articles/12c/adaptive-plans-12cr1)
> If that happens the bloom filter might not work any more. E.g. it
> will not work with a nested loop join.
> You might want to test the assumption by setting
>
> optimizer_adaptive_features false; and reparse
>
> I am not suprised that setting OFE to 12.1 does not work for you, since only parsing will be affected, probably not the runtime engine.
> Othen than that I think you should ask MOS.
> The issue looks like it could be a bug, or some new behaviour that could be switeched off by a fix_control.
>
> Thanks
>
> Lothar
>
>
> Am 09.05.2024 um 19:04 schrieb Laurentiu Oprea:
>> Anyone knows what:
>> Bloom Pruning for Join Rejected: Part of a restartable sub-plan)
>> means?
>>
>> I`m not convinced this is the root cause but it seems like this
>> reason is present in a lot of places.
>>
>> THank you
>>
>> În joi, 9 mai 2024 la 16:04, Laurentiu Oprea
>> <laurentiu.oprea06_at_gmail.com> a scris:
>>
>> THis is a pre-prod environment, was on 12.1 Oct and was
>> upgraded to 19.22. After upgrade some complex reports started
>> to take longer time. The common cause for all of them is that
>> for the large interval partitioned tables (which are around 5
>> to 10 TB) bloom filter for partition pruning is no longer
>> created.
>>
>> What I tried but without success :
>> -> setting OFE to 12.1
>> -> creating a baseline with the 12c plan or pusing hints via
>> sql profile
>> -> I tried to mess around with cardinality hints to fool
>> optimizer but still not used
>>
>> If I test in isolation a simple query (2 joins) it works
>> fine, so the functionality itself is working. It must be
>> probably something related with the complexity of the query.
>>
>> Still digging as of now...trying to understand what
>> invalidate the usage of part pruning
>>
>> În joi, 9 mai 2024 la 15:51, Mark W. Farnham <mwf_at_rsiz.com> a
>> scris:
>>
>> You wrote:
>>
>> “In 10053 KkoBloomFIlter messages are: accepted, but
>> final decision will be made in code generation. But in
>> the final execution plan it is using partition range all..”
>>
>> I believe this means that the CBO has determined that a
>> bloom filter plan is valid and possibly is cheaper.
>>
>> Unfortunately, and this is a pet peeve of mine, there is
>> no way to influence Oracle to make the “opposite” choice
>> at code generation. (Code generation could probably be
>> better described. If someone has a good doc, please advise.)
>>
>> This is sad, because it robs both you and Oracle of
>> feedback whether the decision that was made resulted in a
>> better elapse time and amount of resource consumption.
>> Even a “do it both ways” flag with instructions where to
>> fork the results would be superior (for diagnostics, no,
>> I’m not suggesting you do it both ways forever.)
>>
>> Did you notice because the only reason you notices it
>> stopped actually using bloom filters was because it ran
>> significantly slower, or were you examining this query
>> for some other reason?
>>
>> Good luck,
>>
>> mwf
>>
>> *From:*oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of
>> *Laurentiu Oprea
>> *Sent:* Wednesday, May 08, 2024 3:43 PM
>> *To:* ORACLE-L (oracle-l_at_freelists.org)
>> *Subject:* bloom filter not used after 19C upgrade
>>
>> Dear all,
>>
>> I`m trying to figure out why a query is not using part
>> join bloom filter after the 19C upgrade.
>>
>> In 10053 KkoBloomFIlter messages are: accepted, but final
>> decision will be made in code generation. But in the
>> final execution plan it is using partition range all..
>>
>> WHere should I look for hints to understand what exactly
>> went wrong?
>>
>> Appreciate your help.
>>
>

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