Personally, I seriously consider each option in parallel. I will detail this in the last chapter of this book, but I predict that the expected performance change in terms of throughput increase and response time decrease. I also include factors such as the difficulty and skill required, how long it will take to implement, and the uptime and availability issues. There are also other issues and things to consider that I may know nothing about. Perhaps there are considerations related to the business or budget. So I don't simply start with the SQL or with increasing the buffer cache. I gather information and help others gather information. Then, together, we cooperatively develop the best forward motion plan.

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.