In addition to the real-time feature, v$session_wait also provides additional details that are lost when the wait details are summarized in the v$session_event and v$system_event views. This detailed information is contained in parameter columns: p1, p2, and p3. These parameter columns also have associated text and raw columns, providing even more information. These parameters are event-dependent; that is, their contents depend on the wait event. For example, if the wait event is db file scattered read, then p1 and p2 contain the first block's file number and block number, respectively, and p3 contains the number of blocks requested. But if the wait event is latch free, then p2 contains the latch number, which can be joined with v$latchname to determine the latch for which the session is waiting. Note that starting with Oracle Database 10g, v$session contains all the v$session_wait columns. Table 2-2 lists some of the important columns in this view.

WAITED UNKNOWN TIME: Last wait time is unknown; perhaps instance parameter timed_statistics is false

Suppose we wanted to take a closer look at a particular session. Continuing our example based on Figure 2-8 and Figure 2-9, look closely at Figure 2-10. This is a very simple script based on v$session_wait. In this example, we caught session 205 in the middle of a wait and the details are exposed. Figure 2-10 clearly shows session 205 is currently waiting for an operating system multiblock read to complete. And we also know that 16 blocks are being requested (P3), the first block's file number is 4 (P1), and its block number is 12925 (P2).