Another common nonheader data block issue is when there is a mix of buffers being changed and queried. Typically, this involves both DML and query SQL, but DML SQL can also touch a tremendous number of buffers during filtering. The likely problem is the DML is updating internal buffer structures while another process wants to query the block contents or also make internal buffer changes. Remember that these changes are not row changes, but internal Oracle structure changes. If there is a row or table locking issue, an enqueue wait (discussed later in this chapter) will be posted. Good strategies center on reducing concurrency. Consider reducing the block density (moving rows to other blocks or increasing pct_free), decreasing workload/concurrency activity during peak times, and reducing the number of buffers being touched by both the DML and query SQL.

Figure 6-28 diagrams two segments. The segment on the left is an undo segment, and the segment on the right is a table segment. This section is specific to table segments, and the next section is specific to undo segments.

The first block of the table's first extent is known as the segment header block. As with all segment header blocks, they contain very special internal Oracle structures directly related to their type of segment. For table segments, part of the header block contents is information about the location of blocks that can accept additional inserted rows. These blocks are known as free blocks. When a process must insert a row into a table, it first retrieves the table's segment header block to find a free block, it retrieves that buffer, and then it inserts the row. If there are many processes concurrently inserting rows into the same table, a table segment head block induced buffer busy waits event can result.