Re: _rollback_segment_count side effects

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_at_gmail.com> 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_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