Re: _rollback_segment_count side effects

Thanks a lot again for always useful feedback.

Those numbers are session stats ( I actually attached full session stats)

I plotted the max number of transactions(Y axis) over snap_ids (x axis) and looks like except some outliers max number of transactions historically in the majority of cases is under 400 and in the vast majority of the cases is under 600.

[image: image.png]

This problematic query is reading data modified on multiple instances and number of online/offline segments is as follows:

1 OFFLINE 116

1              ONLINE                 315

2              OFFLINE               45

2              ONLINE                 260

3              OFFLINE               190

3              ONLINE                 123

În joi, 29 apr. 2021 la 12:22, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> I forgot to link to the script:
> https://jonathanlewis.wordpress.com/awr-correlate/
>
> Regards
> Jonathan Lewis
>
>
> On Thu, 29 Apr 2021 at 09:42, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> 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
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png



text/plain attachment: ses_stats.txt