If you do decide to learn more about how undo works then you should get a
copy of my book "Oracle Core".
When you saw the large numbers of single block reads did you also check the
session (or system) activity statistics (v$sesstat/v$sysstat) for
"transaction tables consistent read rollbacks" and "transaction tables
consistent reads - undo reacords applied", The former is the number of
times you had to take an undo segment header block into the past to check a
commit SCN, and the latter is the number of undo records (which may be far
more than physical block reads) that you needed to use to get to the
correct point in the past.
If the average undo records per rollback here is large then that would
explain why Oracle Support suggested setting this parameter.
The suggestion of 50 to 100 is not a threat. It's when you get to values in
the region of a couple of thousand that increase the risk of ORA-01628.
In fact there's an easy guideline for setting this value if you are having
the problem that Oracle thinks you have.
select * from V$resource_limit where resource_name = 'transactions";
The *maximum_utilization* column will tell you the largest number of
concurrent transactions that has appeared since database startup. That's
the largest number of rollback segments you would have needed - so a good
guideline for that hidden parameter. (Technically if you also had incoming
XA transactions I think you might also need to consider maximum_utilization
for "branches" and add that as well - but that's speculation that I haven't
tested.)
I wrote a short note about this view recently at
https://jonathanlewis.wordpress.com/2021/03/25/vresource_limit/
The threat that this fix addresses is the case where Oracle has only got a
small number of undo segments online to meet what it sees as a low level of
concurrent transactions, and you suddenly get a sustained burst of much
higher concurrency. Generally Oracle tries to keep one undo segment per
transaction and one transaction per undo segment - but the delay between
concurrency going up and more segments coming online may be too long, so
you end up with lots of transactions per rollback segment at the same time.
This has two effects: each undo segment transaction table slot gets
overwritten many times, and (b) if the concurreny level goes over 5
transactions per rollback segment you start "wasting" space in undo blocks
so you can end up using the undo segment much more rapidly than normal.
I wrote a blog note a few years ago that describes and models the
particular situation that you may be seeing. The model does some bizarre
things that won't look anything like your production application, but I had
to do that to reproduce the production symptoms from a very small model:
https://jonathanlewis.wordpress.com/2017/04/21/undo-understood/
Regards
Jonathan Lewis
On Thu, 22 Apr 2021 at 15:22, Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
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.
