Re: bloom filter not used after 19C upgrade

Yes, tried with optimizer_adaptive_plans on false as well, other adaptive options are on false by default.

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

> Hi Laurentiu,
>
> did you try optimizer_adaptive_features = false too?
>
> As for the SR, maybe it helps if you let Nigel Bayliss know.
>
> Thanks
>
> Lothar
>
> Am 10.05.2024 um 10:51 schrieb Laurentiu Oprea:
>
> 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