Re: _rollback_segment_count side effects

There are always a few more questions you can ask yourself before taking action that is awkward to unwind - but setting the rollbacksegments somewhere close to 500 is probably not a bad idea; however I would first check:
a) how many rollback segments do you have - online and offline - at present; if it's a lot less than 500 I wouldn't take it much higher than the current total (say 10% more at most), b) this is presumably the current setting - did you run the historical query from the blog to see if that showed up anything interesting - did the max_utilization hover around a smaller number for a long time (many days) and jump very suddently to the 533 or did it grow steadily. Is there any pattern of peak usage in the current_utilization during the normal working day. (You're not really likely to get much information from this historic data, but it's worth a quick look.)
c) Are the stats you reported from just one snapshot, from a critical snapshot, or v$sysstat since startup - if the latter how long since startup. Looking at the average records applied per transaction table read-consistent that's about 1,000 per transaction table rollback. That's not a huge threat (especially if its v$sysstat and a lot of time has passed). Again, though, you could take a trend line through the AWR to see if most of the rollbacks are just a few records with a couple of rollbacks that are hundreds of thousands. There's a script on my blog that demonstrates a quick and dirty hack for how to do something like this. (It's a very old script so there are better coding methods). d) my comments in (c) apply equally well to the " data blocks consistent reads - undo records applied", but in this case you need to compare the value with "CR blocks created" - if you have a busy system and there's a lot of interesting around a relatively small area of the database (and that would include hot spots in indexes) it would be perfectly reasonably to see lots of cases of "a little work" being done to get read-consistency on query.

Regards
Jonathan Lewis

On Thu, 29 Apr 2021 at 05:59, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> 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
>
> data blocks consistent reads - undo records applied
> 126936562
>
> transaction tables consistent reads - undo records applied
> 151042001
>
> transaction tables consistent read rollbacks
> 165714
>
> undo change vector size
> 1.0182E+11
>
>
> Thank you.
>
> În vin., 23 apr. 2021 la 11:39, Jonathan Lewis <jlewisoracle_at_gmail.com> a
> scris:
>
>> 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