Because the transaction table is stored in an Oracle block, there is a limit to the number of slots it can hold, which depends on the Oracle block size. If the transaction table becomes full and a new entry must be added, the oldest inactive8 transaction entry is overwritten by the new entry. If the transaction is active and there is no more room in the transaction table or multiple sessions need to change the transaction table, a buffer busy waits event is posted. I provide a lot more information about the transaction table and undo entries in the "TX Enqueue Waits" section later in this chapter.

If the database is not using automatic undo management (AUM), and is therefore using traditional rollback segments, the solution is simple. Just create an additional rollback segment, which will also create an additional transaction table, thereby distributing transaction table activity. You will notice that the buffer busy wait contention subsides. Keep adding additional rollback segments until buffer busy waits is far from the top wait event.

Most Oracle systems today take advantage of Oracle AUM capabilities. By default, Oracle tries to assign only one active transaction per undo segment. If each undo segment has an active transaction and if there is space available in the undo tablespace, Oracle will automatically create an additional undo segment. This usually takes care of the buffer busy waits. However, if there is no more room in the undo segment tablespace, multiple transactions will be assigned to each undo segment, and eventually undo segment header contention will result. The solution is to add another database file to your undo segment tablespace, thus enabling Oracle to create additional undo segments. It's that simple.