Re: bloom filter not used after 19C upgrade

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

> 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