Re: bloom filter not used after 19C upgrade

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