Re: bloom filter not used after 19C upgrade

My previous note assumed that that the plans for the older version and 19c were essentially the same apart from the appearance of a "Join Filter Create" operation (and the change to pstart/pstop , and "partition range" option), but that may not be true. This means that your message about "accepted ... final decision ..." may have appeared in a path/join order that was computed but not used.

An aspect of the literal / bind behaviour is that it could make the optimizer change cardinalities all the way through the plan, which could be enough to explain why the Bloom filter is not used.

Is the table range only, on interval partitioned. Is it composite partitioned range/something else.
Is the table structure under 19c exactly the same as it was before the upgrade or did you use the upgrade as a point to restructure the table in any way?

Regards
Jonathan Lewis

On Thu, 9 May 2024 at 08:29, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Thanks all :)
>
> Hinting is not working, bloom filter still not used.
>
> I will try to trace sql code generator and bloom filter and figure out,
> query is very complex, the resulting 10053 is 10GB.
>
> An interesting aspect is that if I eliminate all binds in the code and
> replace with the literal values bloom filters are correctly applied for
> partition pruning. If I use even a single bind at some point in code
> completely not related with the tables I`m interested to have partition
> rage filter applied the bloom filter is not applied and all partitions are
> scanned.
>
> Regarding your comment Sayan, is this something which oracle should figure
> out during optimization ? I seen indeed a lot of places where bloom filter
> are rejected due to various reasons, including distinct values in
> optimization phase but for the part I`m interested comment in 10053 is that
> bloom filter is accepted...but final decision will be made in code generator
>
> În joi, 9 mai 2024 la 09:33, Lothar Flatz <l.flatz_at_bluewin.ch> a scris:
>
>> V$SQL_JOIN? What the hack?
>>
>> Am 09.05.2024 um 02:53 schrieb Mladen Gogala:
>>
>> 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