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@xxxxxxxxx> 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@xxxxxxxxx>
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.
