learning v$ view-> v$session_wait

 

V$SESSION_WAIT

This is a key view for finding bottlenecks. It tells what every session in the database is currently waiting for (or the last event waited for by the session if it is not waiting for anything). This view can be used as a starting point to find which direction to proceed in when a system is experiencing performance problems.

V$SESSION_WAIT has a row for every session connected to the instance. It indicates if the session is:

• Using a resource
• Waiting for a resource
• Idle (waiting on one of the idle events)

Useful Columns for V$SESSION_WAIT

• SID: Session identifier for the session
• EVENT: Event the session is currently waiting for, or the last event the session had to wait for
• WAIT_TIME: Time (in hundredths of a second) that the session waited for the event; if the WAIT_TIME is 0, then the session is currently waiting for the event
• SEQ#: Gets incremented with every wait of the session
• P1, P2, P3: Wait event specific details for the wait
• P1TEXT, P2TEXT, P3TEXT: Description of P1,P2,P3 for the given event

  See Also: Oracle9i Database Reference and "Wait Events"

  Table 24-18 Wait Time Description

  WAIT_TIME	Meaning	Waiting
  >0	Time waited in the last wait (in 10 ms clock ticks)	No
  0	Session is currently waiting for this event	Yes
  -1	Time waited in the last wait was less than 10 ms	No
  -2	Timing is not enabled	No

Table 24-19 shows an example of how the EVENT, SEQ#, and WAIT_TIME might change over a period of time:

Table 24-19  Events Changing Over Time

Time	Seq #	Event	Wait Time	P1	P2	P3	Action	Waiting
0	43	latch free	0	800043F8	31	1	Get LRU latch	Yes
10	43	latch free	10	800043F8	31	1	Get free buffer	No
20	44	db file sequential read	0	5	1345	1	Issue the read call	Yes
30	44	db file sequential read	10	5	1345	1	Process the buffer	No
35	45	enqueue	0	1415053318	196631	6355	Lock the buffer	Yes
1040	45	enqueue	1000	1415053318	196631	6355	Modify the buffer	No

In this example, the session waited for a latch from 0-10, waited for db file sequential read from 20-30, waited for a lock from 35-1040. The times in between have been exaggerated for illustration purposes. Event and Seq# do not change until the session has to wait again. The Wait Time indicates if the session is actually waiting or using a resource.

Join Columns for V$SESSION_WAIT

Table 24-20 is a list of join columns for V$SESSION_WAIT.

Table 24-20 Join Columns for V$SESSION_WAIT

Column	View	Joined Column(s)
SID	V$SESSION	SID

Example 24-25 Finding Current Waits on the System

SELECT event,
       sum(decode(wait_time,0,1,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, which groups the data by event and by wait_time (0=waiting, nonzero=not waiting), shows the following:

• Most of the sessions are waiting for idle events like SQL*Net message from client, pipe get, PMON timer, and so on.
• The number of sessions using the CPU can be approximated by the number of sessions not waiting (prev), except for one problem: there seem to be a lot of sessions that are not waiting for anything (hence actively using resources) and whose last wait was SQL*Net message from client.

The next step should be to check V$SESSION to see if the session is active or not. Only count the session as actively waiting or using a resource if it is active. Use the following statement to accomplish this. The total column counts the total of all the sessions, however the currently waiting and previously waited (using resource) columns only count active sessions.

SELECT event,
       sum(decode(wait_Time,0,0,DECODE(s.status,'ACTIVE',1,0))) "Prev",
       sum(decode(wait_Time,0,1,DECODE(s.status,'ACTIVE',1,0))) "Curr",
       count(*) "Tot"
  FROM v$session s, v$session_wait w
 WHERE s.sid = w.sid
 GROUP BY event
 ORDER BY count(*);

EVENT                                     Prev  Curr   Tot
---------------------------------------- ----- ----- -----
SQL*Net message to client                    1     1     1 <- idle event
buffer busy waits                            1     1     1
file open                                    1     1     1
pmon timer                                   0     1     1 <- idle event
smon timer                                   0     1     1 <- idle event
log file sync                                0     1     1
db file scattered read                       0     2     2
rdbms ipc message                            0     7     7 <- idle event
pipe get                                     0    12    12 <- idle event
enqueue                                      0    14    14
latch free                                  10    17    20
db file sequential read                      7    22    23
SQL*Net message from client                  0  1383  2240 <- idle event

Now sessions are counted as actively waiting or using a resource only if they are active. This highlights the following:

• There are a total of 2324 sessions.
• 20 sessions are actively using resources (active sessions without an active wait).
• 1463 sessions are waiting.
• 58 of these are waiting for non-idle events. The idle events here being SQL*Net message from client, pipe get, rdbms ipc message, PMON timer, SMON timer, and SQL*Net message to client.

  See Also: "Wait Events"

• 14 sessions are locked out (and may be experiencing poor performance).
• PMON and SMON are sleeping on their timers.
• 24 sessions are waiting for I/O calls to return (db file%read).