Re: _rollback_segment_count side effects

Thanks a lot Jonathan for your anwer. Your answer is actually exactly what I was searching for, because to be honest I don't have a full understanding of the issue I face.

In short: I have an insert base on select that is executed around 150 times per in a window of about 3-4 hours and just one or 2 executions sometimes are taking a few good hours, although all other executions are under 3 minutes. All the waits are mainly cell single blocks wait events (when doing a range scan) on UNDO tablespace. I know for sure data is loaded on the source tables all the time, and is very strange why one or maximum 2 executions take so much time.

I opened an SR and the recommendation was to set _rollback_segment_count to a value like 50/100. At this point I`m in the process of understanding the issue from a business perspective because the described waits are on 3 different indexes sometimes (on 3 different tables - again I know for sure data is loaded all the time in those tables) and maybe I can arrange the order of these jobs. Even so the impact of what I believe is a read consistency issue is just too dramatic.

If this is expected behavior and _rollback_segment_count can mitigate it is clear to me I might need to learn more about how UNDO works.

Thanks again.

În mie., 21 apr. 2021 la 13:40, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

> That's not a good question.
>
> It doesn't matter what side effects anyone has seen unless you also know
> what sort of activity their database goes through - so you're asking other
> people to tell you things about their databases that you should be telling
> them about your database before you ask the question.
>
> I can think of a couple of *possible *side effects - but they may be
> completely irrelevant to you, and if I don't give you a context your next
> question will probably be to ask for an explanation of how or why the side
> effects night appear.
>
> a) You waste buffer cache unnecessarily
> b) you increase the risk of ORA-01628 max # extents (32765) reached for
> rollback segment
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Tue, 20 Apr 2021 at 05:58, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
>> Hello,
>>
>> DB Version: 12.1
>>
>> Is someone played around with parameter _rollback_segment_count ? I`m
>> most interested in what are the side effects of increasing this parameter.
>>
>> Thank you.
>>
>

--
http://www.freelists.org/webpage/oracle-l