Figure 6-25 shows a single sample query providing the parameter details. In this single sample, three processes were waiting for file number 4 and block number 54. Another sample will normally show multiple processes waiting for a different buffer. Once you have a few samples, move on to step 2.

Figure 6-25. This sample v$session_wait-based OSM script swswp.sql displays the session numbers along with their wait event parameters for a given wait event. In this situation, we are looking for buffer busy waits parameter values and parameter value patterns.

Every Oracle block buffer is part of an Oracle segment. Every Oracle segment is a segment type, such as a data segment, index segment, undo segment, or temporary segment. The solution set is based, in part, on the segment type. Using the buffer busy wait event's p1 (file number) and p2 (block number) gathered in step 1, the segment type can be determined from a query based on dba_extents and dba_data_files, such as the one shown in Figure 6-26.