Learning Dynamic Performance Table (6)--v$session_wait&v$session_event

Learning Dynamic Performance Table

Sixth article -(1)-v$session_wait 2007.5.30

This is a critical view of looking for performance bottlenecks. It provides any case where the session is currently waiting in the database (if the session is currently not doing anything, it shows its last wait event). When there is a performance problem with the system, this view can be used as a starting point to indicate the direction in which the problem is explored.

In v$session_wait, each SESSION connected to the instance corresponds to a record.

v$session_wait the common columns in

• Sid:session identification
• Event:session the currently awaited event, or the last wait event.
• Wait_time:session time to wait for an event (unit, 1% seconds) If this column is 0, the session does not have any waiting sessions at the current session.
• seq#: Session wait event will trigger its value self-growth
• P1, P2, P3: Wait in the event for more details
• P1text, P2text, P3text: Explanation P1,P2,P3 Event

Note:

There are four meanings of the 1.State field:

(1) Waiting:session is waiting for this event.

(2) Waited unknown time: Due to the setting of the Timed_statistics value to false, the timing information cannot be obtained. Indicates that a wait has occurred, but the time is short.

(3) Wait short time: Indicates that an await has occurred, but that there is no record due to the fact that it is not more than a single time unit.

(4) Waited Knnow time: If the session waits and then gets the required resources, it will enter this state from waiting.

There are four meanings of 2.wait_time values:

(1) Value >0: The Last Wait time (in 10ms), is not currently in the waiting state.

(2) value =0:session is waiting for the current event.

(3) Value =-1: The last wait time is less than 1 statistical units and is not currently in the waiting state.

(4) Value =-2: The time statistics status is not set to available and is not currently in the waiting state.

The 3.wait_time and second_in_wait field values are related to state:

(1) If the state value is waiting, then the wait_time value is useless. The second_in_wait value is the actual wait time (in seconds).

(2) If the state value is wait unknow time, then the Wait_time value and the second_in_wait value are useless.

(3) If the state value is wait short time, then both the Wait_time value and the second_in_wait value are useless.

(4) If the state value is waiting known time, then the Wait_time value is the actual latency (in seconds) and the second_in_wait value is useless.

v$session_wait the connection column in

Column View Joined column (s)

Sid V$session Sid

Example:

1. List the current system's wait events

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 group query by event and Wait_time lists the following information:

• Most sessions are idle events such as: Sql*net message from client, pipe get, Pmon timer, and so on.
• The CPU usage of the session can be roughly calculated from the non-wait events of the last session, except for this: it seems that most of the sessions are not waiting for something (are they all working?). But its last wait event is sql*net message from client.

2. List the wait events for the specified ID

SELECT * from v$session_wait where sid=100;

3. Application of P1,P2,P3 for the analysis of waiting events

The columns of the v$session_wait view represent the buffer busy wait events as follows:

P1-The total number of files that are associated with the pending data file.

The number of blocks of data files in the P2-P1.

P3-describes the code that waits for a reason.

Example: select P1 "File #", p2 "Block #", p3 "Reason Code"

From v$session_wait

where event = ' buffer busy waits ';

If the results of the above query show a block in the busy wait, the following query will show the name and type of this piece:

Select owner, Segment_name, Segment_type

From Dba_extents

where file_id = &p1 and &p2 between block_id and block_id + blocks-1;

We 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 (reason encoding) from v$session_wait to know the reason for the session waiting. The reason codes range from 0 to 300, and the following are some of the things that are represented by some encodings:

0 Blocks are read into the buffer.

100 We want a new (create) block, but this one is currently being read in by another session.

110 we want to set the current block as shared, but this one is read in by another session, so we have to wait for the end of Read ().

120 we want to get the current block, but others have read this piece into the buffer, so we can only wait for the other person's read-in to end.

130 blocks were read in by another session, and no other coordinated blocks were found, so we had to wait for the end of the read. This situation can also occur after a buffer deadlock. So you have to read the CR into the block.

200 we want to create a new block, but others are using it, so we have to wait for others to use the end.

The session wants to read a block in the Scur or xcur, and if the block exchange or session is in a discontinuous TX mode, the wait may take a long time.

220 queries the current version of a block in the buffer, but someone uses this piece in an illegal mode, so we can only wait.

230 Gets a block in CR/CRX mode, but the change in the block starts and does not end.

The 231 CR/CRX scan finds the current block, but the change in the block starts and does not end.

Sixth article -(2)-v$session_event 2007.5.30

This view records each of the wait events for each session. The v$session_wait shows the current wait event for the session as described above, while v$session_event logs all events from the session since it was started.

v$session_event the common columns in

• Sid:session identification
• Event:session Waiting for an event
• Total_waits: The total number of waits for the current event of this session
• time_waited: Total wait time in this session (unit, 1% seconds)
• Average_wait: Average wait time for current event in this session (unit, 1% seconds)
• Total_timeouts: Wait Timeout number

Other usages are similar to v$session_wait and do not detail

Note:

Oracle's wait events are an important basis for measuring Oracle health and metrics. The concept of wait events was introduced in Oracle7.0.1.2, with roughly 100 waiting events. This number is increased to approximately 150 in Oracle 8.0, with approximately 200 events in oracle8i and approximately 360 waiting events in oracle9i. There are two main categories of wait events, idle (idle) Wait events and non-idle (non-idle) wait events.

Information about idle events and non-idle events is now available through Google and can be found in much more detail, while

There is a very detailed description of wait events in Oracle Database Performance Tuning Guide and reference, and there is little to be found here. But I see in the Itpub forum there are enthusiastic people to organize the CHM format non-idle event description, interested friends can download, link as follows:

Non-idle event description

See: http://www.itpub.net/728733.html

Learning Dynamic Performance Table (6)--v$session_wait&v$session_event