Re: bloom filter not used after 19C upgrade
Date: Thu, 9 May 2024 09:55:59 +0200
Message-ID: <2bb47220-e270-4545-a748-4d5c82f62080_at_bluewin.ch>
Hi Laurentiu ,
here is an older text that refers to the topic I have in mind: https://tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/
Thanks
Lothar
Am 09.05.2024 um 09:28 schrieb Laurentiu Oprea:
> 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-lReceived on Thu May 09 2024 - 09:55:59 CEST