Dynamic Learning Performance Table 3 -- v $ session_wait

This is a key view for finding performance bottlenecks. It provides what the session is waiting for in the database under any circumstances (if the session is not currently doing anything, it will display its last waiting event ). When the system has performance problems, this view can be used as a starting point to identify the direction of the problem.

In V $ SESSION_WAIT, each session connected to the instance corresponds to a record.

Common columns in V $ SESSION_WAIT

SID: session ID
EVENT: the EVENT currently waiting for the session, or the last waiting EVENT.
WAIT_TIME: the time when the session waits for the event (unit: 1% seconds). If this column is 0, the current session has not waited.
SEQ #: session wait events trigger auto-increment of Values
P1, P2, P3: Detailed information waiting for the event
P1TEXT, P2TEXT, and P3TEXT: Explanations for p1, p2, and p3 events

Note:
1. The State field has four meanings ﹕
Waiting: the SESSION is Waiting for this event.
Waited unknown time: the time cannot be obtained because the value of timed_statistics is set to false. It indicates a wait, but the time is short.
Wait short time: indicates that a Wait has occurred, but the time is not more than one time unit, so no records are recorded.
Waited knnow time: if the session waits and receives the required resources, it enters the State from waiting.

The Wait_time value also has four meanings:
Value> 0: The last wait time (unit: 10 ms). The current status is not waiting.
Value = 0: the session is waiting for the current event.
Value =-1: The last wait time is smaller than one statistical unit. The current status is not waiting.
Value =-2: The time statistics status is not set to available and is not waiting.

3. The Wait_time and Second_in_wait field values are related to the state:
If the value of state is Waiting, the value of wait_time is useless. The Second_in_wait value is the actual wait time (unit: seconds ).
If the state value is Wait unknow time, the wait_time value and Second_in_wait value are useless.
If the state value is Wait short time, the wait_time value and the Second_in_wait value are useless.
If the value of state is Waiting known time, the value of wait_time is the actual Waiting time (unit: seconds), and the value of Second_in_wait is useless.

Connection column in V $ SESSION_WAIT

Column View Joined Column (s)
Sid v $ SESSION SID

Example:
1. List wait events of the current system
SELECT event,
Sum (decode (wait_time, 0, 0) "Curr ",
Sum (decode (wait_time, 0, 0, 1) "Prev ",
Count (*) "Total"
FROM v $ session_wait group by event order by count (*);
EVENT Prev Curr Tot
-----------------------------------------------------------
PL/SQL lock timer 0 1 1
SQL * Net more data from client 0 1 1
Smon timer 0 1 1
Pmon timer 0 1 1
SQL * Net message to client 2 0 2
Db file scattered read 2 0 2
Rdbms ipc message 0 7 7
Enqueue 0 12 12
Pipe get 0 12 12
Db file sequential read 3 10 13
Latch free 9 6 15
SQL * Net message from client 835 1380 2215

This query lists the following information by event and wait_time groups:
Most sessions are idle events, such as SQL * Net message from client, pipe get, and PMON timer.
The cpu usage of the session can be roughly calculated through the last non-Wait event of the session. In addition: it seems that most sessions are not waiting for anything (are they working ?) However, the last wait event is SQL * Net message from client.

List wait events of a specified ID
Select * from v $ session_wait where sid = 100;

Analyze wait events using p1, p2, and p3
The column in the v $ session_wait view represents the following buffer busy wait events:
P1-Total number of waiting-related data files.
The number of data files in the P2-P1.
P3-code that describes the reason for waiting.
Example: select p1 "File #", p2 "Block #", p3 "Reason Code"
From v $ session_wait
Where event = 'buffer busy waits ';
If the preceding query results show that a block is waiting, the following query displays the name and type of this block:
Select owner, segment_name, segment_type
From dba_extents
Where file_id = & P1 and & P2 between block_id and block_id + blocks-1;

You can also query dba_data_files to determine the file_name of the waiting file by using P1 in v $ session_wait.
Query the value of P3 (cause code) from v $ session_wait to find out the reason for the session waiting. The cause code ranges from 0 to 300, and the following are some of the encoding items:
0 blocks are read into the buffer.
100 we want to create a NEW block, but this block is currently read by another session.
110 we want to set the current block as shared, but this block is read by another session, so we have to wait until read () ends.
120 we want to obtain the current block, but other people have read this block into the buffer zone, so we can only wait for others' reading to end.
130 blocks are read by another session and no other coordinated blocks are found. Therefore, we must wait until the reading ends. This situation may also occur after a buffer deadlock. Therefore, the CR of the block must be read.
200 we want to create a new block, but others are using it, so we have to wait for others to finish using it.
210 The Session wants to read the blocks in SCUR or XCUR. If the block switching or session is in discontinuous TX mode, it may take a long time to wait.
220 query the current version of a block in the buffer zone, but someone uses this block in invalid mode, so we can only wait.
230 obtain a block in CR/CRX mode, but the change in the block starts and does not end.
231 CR/CRX scan found the current block, but the changes in the block started and did not end.