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@xxxxxxxxx> 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
