Re: bloom filter not used after 19C upgrade

You can enable trace for component 'Bloom_Filter': https://orasql.org/files/events/oradebug-doc-component.php

https://orasql.org/2021/05/20/oracle-diagnostic-events-cheat-sheet/

In fact, the nature of the Bloom filter is to filter out (in your case) those partitions which do not contain values from the join predicates, and bloom filter uses hash functions for that. So from my understanding, the number of distinct hash values of your dataset that you want to join with that partitioned table must be smaller than number of distinct hash values of the partitioning keys.
If Oracle can't find a suitable hash function that returns such small number of distinct hashes for whole dataset, bloom filter will be disabled.

It would be better if you provide RTSM report for both cases (old one with working bloom filter and new one) and ddl with stats how that table was partioned (partitioning keys, number of partition, etc)

On Thu, May 9, 2024, 01:54 Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On Wed, 2024-05-08 at 23:16 +0200, Lothar Flatz wrote:
>
> Hi,
>
> You could try hinting the bloom filter and see what happens.
> If I get the message right decision is made at runtime. (Not sure).
> In such case it can make a difference how well the table is cached.
> Need more facts, e.g. both plans.
>
> Thanks
>
> Lothar
>
>
> Hi Lothar,
> I may be mistaken, but if the decision is made at runtime, then it is not
> possible to know in advance whether Bloom filters will be used or not. The
> only places where it's possible to see it is V$SQL_PLAN and V$SQL_JOIN and
> not explain plan.
> Other than that, Bloom filters were invented by Mr. Bloom, of the
> Bialystock & Bloom fame, from the movie "The Producers". That is what it's
> so hard to catch them. BTW, if you decide to watch the movie, a smart
> decision by any standard, I'd recommend the Gene Wilder version, rather
> than the modern one with Will Ferrell.
> Regards
>
>
> --
>
> Mladen Gogala
> Database SME
> https://dbwhisperer.wordpress.com
>
>

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