Re: bloom filter not used after 19C upgrade

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