Re: _rollback_segment_count side effects

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