Re: bloom filter not used after 19C upgrade

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