Figure 6-30. A diagram of the order table segment head block (4,50), which contains free lists with links to free blocks

Fortunately, the solutions are very straightforward and they work very well. If you are using manual segment space management, then segment space management is controlled by what are called free lists. As shown in Figure 6-30, I picture a free list as a jellyfish-like structure, with each of the stinging strings a list containing nodes, which point to free blocks. Oracle's free list approach usually works fine, but in high-concurrency situations, the existing free lists simply cannot handle the workload. Fortunately, we can easily alter the table to create additional free lists in another one of the segment's blocks. The result is a header block popularity reduction, and hence a reduction in buffer busy wait contention. Simply keep adding free lists until the contention subsides. The number of free lists can be found in the free lists column in the dba_segments view. If the column is empty, then we know free lists are not being used and automatic segment space management (ASSM) is being used.

Another option, which focuses more on the longer term, can be performed during a maintenance opportunity. This is to move high-concurrency segments into a locally managed tablespace with ASSM enabled. ASSM does not use free lists, but instead uses bitmaps to manage free space. While this usually allows for increased table segment header block concurrency, I have spoken with a few of my students who have unexpectedly experienced increased buffer busy activity when converting to ASSM. For some reason (and they never found out), an unknown number of events (for example, workload, Oracle release, application design) had contributed to stressing ASSM in an unexpected way.