RE: _rollback_segment_count side effects

Those are great notes.  

One “and”: If the tables in question are appearing only in queries that read them into PGA and not the SGA, then you may accrete significant amounts of activity doing consistent read undo over and over again. A force reading of the recently updated (or whole thing for small things) periodically (your mileage may vary, if it’s need more than twice a day it bears further investigation, more likely once a week is plenty) it is POSSIBLE your future undo reads will decrease.  

How much dancing you need to do to force blocks read into the SGA rather than the PGA is version specific and may include making sure at least one column from each row piece is referenced in the case of blocks that contain only non-first row piece data. If you have very many columns (over 254 or 255 and I’m not looking that up) or if your application inserts a row with just the primary key (or the PK and a few columns) for a lot of rows and then goes back after that commit and puts values of significant length greater than the original insert’s length for columns you may have a table that dodges block cleanout if you only read the blocks with first row pieces by a single row id per block in your force sga scenario.  

As far as I know, we still don’t have a maintenance command to clean out all the blocks for a subpartition, partition, or table. Preparing a tablespace to become read only should do this comprehensively, but I haven’t checked that in years and last check it didn’t completely work.

Preparing to table a tablespace to read only also seems like swatting a fly with a grenade.  

Good luck. Learn and do what JL suggested, and if what I wrote seems to apply to you, there is a good chance your applications need to be reconsidered.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, April 23, 2021 4:39 AM
To: ORACLE-L (oracle-l_at_freelists.org) Subject: 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.  

1. You waste buffer cache unnecessarily
2. 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