If we perform a few more samples, we will get a good idea of the objects the session is waiting for and also where the objects reside. This information provides a very important 3-circle analysis link to the application and also to the operating system.

In this example, from an application perspective, we should expect to find SQL that is requesting blocks from the orders table. Look for that SQL. You will find it. It must be there, because if it were not run, it would be impossible for a session to be waiting for a block in the orders table. From an operating system perspective, if the average wait time is significant, we should expect the device where the associated database file resides to be very busy, or the path from the server process to the database file to be hindered in some way. This example demonstrates that with only limited but very specific information, you can make a pinpoint diagnosis.

Now suppose we want to gain a better understanding of the other sessions waiting for multiblock reads. Once again, we can reference v$session_wait. But instead of selecting a specific session, we list all the sessions waiting on a given wait event-in this case, db file scattered read. Figure 2-13 shows such a query, which simply uses the wait event like a command-line parameter. At the specific moment of the query, there were three sessions patiently waiting for the blocks to be retrieved from the operating system. Sampling the file and block numbers while running a script like objfb.sql (shown in Figure 2-12) will help build a strong connection between all three circles in a 3-circle analysis.