| From: | Steve Adams |
| Date: | 25-Apr-2001 15:23 |
| Subject: | Rollback segment contention |
|
|
The advice given in that note is poor. The db block gets statistic counts current mode gets. Current mode gets neither read nor modify rollback segment header blocks, therefore they have nothing to do with buffer busy waits for those blocks. Similarly, only the consistent gets that require cleanout or rollback need to access a rollback segment header block. The vast majority of consistent gets don't. So they too have got nothing to do with buffer busy waits for rollback segment header blocks. Using the sum of these two statistics as the denominator for the ratio is misleading and will grossly understate any performance problem in this area, and the 1% threshold is unlikely to ever be exceeded. I suggest that you preserve the distinction between read waits and write waits, because read waits can be caused by intensive cleanout and/or consistent read rollback in the presence of just one active transaction. If you really want to compute ratios, the best denominator for read waits is the sum of cleanouts only - consistent read gets, rollbacks only - consistent read gets and cleanouts and rollbacks - consistent read gets. The best denominator for write waits is the number of header GETS recorded in V$ROLLSTAT. I will not venture to suggest thresholds of acceptable values for these ratios, because such tuning should be driven by the relative performance impact of the problem, and not by arbitrary ratio thresholds.
|
![]() |
Your enough_rollback_segs.sql entry states that: "Write waits are a stronger indication of insufficient rollback segments." What would be the suggested scale here? Note 1011108.6 indicates that the waits should be compared to the total number of gets:
Seems a bit generalized. Is this the best scale?
|