The explanation is different for the two instances.
In the second instance, the rollback segment with the greater number of waits also has a greater volume of gets and writes than the other segments. The writes figure is in bytes, and a get is needed for each new transaction, or when a transaction allocates a new block in the rollback segment. The higher ratio of writes to gets in this case indicates that this rollback segment has taken one big transaction that generated about 40M of undo. The waits are due to the fact that there were more concurrent transactions at that time than the number of rollback segments and so one of the small light transactions occasionally had to share the rollback segment that was in use by the intensive long transactions which resulted in the modest amount of contention reflected in the statistics.
That explanation does not apply to the first instance, because there the ratio of writes to gets is actually lower than in the other rollback segments, whereas the volume of writes is about the same. This suggests that the extra gets are in fact from distributed transactions (or queries) that generate no undo locally. The fact that they have all shared the same rollback segment suggests that they were recursive transactions of a common parent. A PL/SQL procedure that performed about 850,000 iterations of a loop containing a distributed query and a commit would fit the data. Again, the modest contention is attributable to the fact that other transactions shared the rollback segment while this procedure was running.
Thanks for an interesting question.
We have an application using Weblogic and JDBC thin client that produces the following rollstats:
NAME GETS WRITES WAITS EXTENTS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- RBS01 2585427 305906760 3 20 2621440 2613248 0 2436 0 RBS02 2570051 302711008 2 20 2621440 2613248 0 2411 0 RBS03 3438965 301730316 691 20 2621440 2613248 0 2407 0 RBS04 2567999 303579146 3 20 2621440 2613248 0 2420 0 RBS05 2565932 302304022 3 20 2621440 2613248 0 2410 0 RBS06 2514492 296904872 6 20 2621440 2613248 0 2366 0 RBS07 2499044 295891152 0 20 2621440 2613248 0 2360 0 RBS08 2614077 303132502 26 20 2621440 2613248 0 2421 0 RBS09 2579325 303536264 4 20 2621440 2613248 0 2416 0 RBS10 2556660 301572674 0 20 2621440 2613248 0 2405 0 SYSTEM 1204 4440 0 5 401408 0 0 0Although the waits are relatively low in relation to the writes, I am perplexed by the concentration of waits on RBS03. I would have thought that the waits would have been distributed more evenly given the Oracle's round robin undo header allocation scheme for transactions.
Is this as a result of the connection pooling that results in multiple transactions from a single Oracle session?
We also have another system with the following stats:
NAME GETS WRITES WAITS EXTENTS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- RBS01 476241 77343212 2 20 2621440 2613248 0 665 0 RBS02 472366 83366688 12 20 2621440 5890048 3 716 25 RBS03 471079 79044042 0 20 2621440 3006464 1 674 3 RBS04 474029 86338382 1 20 2621440 7593984 4 738 38 RBS05 475770 93705390 54 20 2621440 15589376 10 794 99 RBS06 501762 143416536 363 20 2621440 68665344 51 1179 504 RBS07 472256 77388994 0 20 2621440 2613248 0 669 0 RBS08 471459 76440836 1 20 2621440 2613248 0 655 0 RBS09 473491 76062574 0 20 2621440 2613248 0 650 0 RBS10 473873 77245412 0 20 2621440 2613248 0 663 0 SYSTEM 1709 6660 0 5 401408 0 0 0 RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE ------------------------------ ------------------- --------------- ------------------ ----------- distributed_transactions 0 4 31 31 dml_locks 0 21 504 504 enqueue_locks 21 60 1471 1471 enqueue_resources 21 51 624 624 lm_locks 0 0 0 UNLIMITED lm_procs 0 0 0 0 lm_ress 0 0 0 UNLIMITED max_rollback_segments 11 11 31 31 mts_max_servers 0 0 20 20 parallel_max_servers 0 0 0 0 processes 53 58 100 100 sessions 53 60 115 115 sort_segment_locks 1 3 UNLIMITED UNLIMITED temporary_table_locks 0 0 UNLIMITED UNLIMITED transactions 16 22 126 126Given the low number of concurrent transactions, should we still be experiencing waits against the undo headers?