Re: _rollback_segment_count side effects

Thanks a lot for the detailed answer.

I was looking indeed at v$resource_limit and found these numbers on the instance with highest values:
RESOURCE_NAME/CURRENT_UTILIZATION/MAX_UTILIZATION/INITIAL_ALLOCATION/ LIMIT_VALUE transactions 142 533 5596 UNLIMITED So based on the above, just to be on the safe side I should consider changing _rollback_segment_count to around 500?

I managed to extract the session stats also. Does it look like this is an issue with both read and write consistency?

rollback changes - undo records applied                               19651

> 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