On a large Oracle system with hundreds of thousands of extents, querying dba_extents can take an unexpectedly long time. This is one reason why I force an additional step to run a query like the one in Figure 6-26. For performance reasons, some DBAs will periodically make a copy of the dba_extents view and run their report based on the copy. Unless a real-time copy of the extent situation is needed (which isn't the case in this example), dba_extents-based queries can run noticeably faster.

Figure 6-26. Taken from the OSM tool kit script objfb.sql, given a block's file number and block number, this script produces a useful set of information about the block's object.

Figure 6-25 shows three sessions are waiting for file 4 and block 54. Executing the script shown in Figure 6-26 resulted in the output shown Figure 6-27. This particular buffer (file 4, block 54) is a data segment and part of the orders table. We don't yet know if the buffer is the orders table header block or a data block containing rows. Step 3 will reveal the answer.