Re: bloom filter not used after 19C upgrade

THank you Lothar,

Indeed the client opened an SR but we all know how long is taking some time for resolutions....well see how it goes.

I tried basically everything I even disabled every bug fix for 12.2, 18, 19 , played around with bloom fiter parameters., took outline from resulted plan which is fine with literals ... as of now I`m in MOS hands.

I enabled tracing for bloom filters but I can`t figure out very well why are not used due to huge size of traces and also not very experienced on what to look for exactly in such scenarios

În vin., 10 mai 2024 la 10:42, Lothar Flatz <l.flatz_at_bluewin.ch> a scris:

> Hi Larentiu,
>
> the 10053 seems to indicate that a bloom filter can be used in principle.
> I have to guess for an answer, So please note my caveat. I think the text
> below means that:
> You have an adaptive plan which could be switched. (
> https://oracle-base.com/articles/12c/adaptive-plans-12cr1)
> If that happens the bloom filter might not work any more. E.g. it will not
> work with a nested loop join.
> You might want to test the assumption by setting
>
> optimizer_adaptive_features false; and reparse
>
> I am not suprised that setting OFE to 12.1 does not work for you, since only parsing will be affected, probably not the runtime engine.
> Othen than that I think you should ask MOS.
> The issue looks like it could be a bug, or some new behaviour that could be switeched off by a fix_control.
>
> Thanks
>
> Lothar
>
>
> Am 09.05.2024 um 19:04 schrieb Laurentiu Oprea:
>
> Anyone knows what:
> Bloom Pruning for Join Rejected: Part of a restartable sub-plan)
> means?
>
> I`m not convinced this is the root cause but it seems like this reason is
> present in a lot of places.
>
> THank you
>
> În joi, 9 mai 2024 la 16:04, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> a scris:
>
>> 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
>> still not used
>>
>> If I test in isolation a simple query (2 joins) it works fine, so the
>> functionality itself is working. It must be probably something related with
>> the complexity of the query.
>>
>> Still digging as of now...trying to understand what invalidate the usage
>> of part pruning
>>
>> În joi, 9 mai 2024 la 15:51, Mark W. Farnham <mwf_at_rsiz.com> a scris:
>>
>>> 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