Note
This view is introduced since OceanBase Database V1.4.
Purpose
The V$SESSION_WAIT view displays information about the current or last wait events of all sessions on the current OBServer node, such as the event name and the amount of time spent on waiting.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| SID | NUMBER | NO | For V4.3.x:
|
| CON_ID | NUMBER | NO | The ID of the tenant. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node. |
| EVENT | VARCHAR2(64) | NO | The name of the wait event. |
| P1TEXT | VARCHAR2(64) | NO | The description of the first parameter of the wait event. |
| P1 | NUMBER | NO | The value of the first parameter of the wait event. |
| P2TEXT | VARCHAR2(64) | NO | The description of the second parameter of the wait event. |
| P2 | NUMBER | NO | The value of the second parameter of the wait event. |
| P3TEXT | VARCHAR2(64) | NO | The description of the third parameter of the wait event. |
| P3 | NUMBER(38) | NO | The value of the third parameter of the wait event. |
| WAIT_CLASS_ID | NUMBER | NO | The ID of the class to which the wait event belongs. |
| WAIT_CLASS# | NUMBER | NO | The sequence number of the class to which the wait event belongs. |
| WAIT_CLASS | VARCHAR2(64) | NO | The name of the class to which the wait event belongs. |
| STATE | VARCHAR2(19) | NO | The status of the current wait event. Valid values:Waiting: The session is waiting for the event.Waited unknown time: The time information cannot be obtained because timed_statistics is set to false. In other words, a wait event has occurred but lasted only for a very short period.Wait short time: A wait event has occurred but lasted for less than one unit of time, and therefore was not recorded.Waited known time: If the session has obtained the required resources after the wait, it will switch from the waiting state to this state. |
| WAIT_TIME_MICRO | NUMBER | NO | The wait time, in microseconds. It indicates the elapsed wait time if the session is in the waiting state and indicates the total wait time of the last wait if the session is not in the waiting state. |
| TIME_REMAINING_MICRO | NUMBER | NO | 0: The session is waiting for the current event.-1: The wait time of the last wait is shorter than one measurement unit, and the session is not in the waiting state.-2: Time statistics collection is disabled, and the session is not in the waiting state. |
| TIME_SINCE_LAST_WAIT_MICRO | NUMBER | NO | The amount of time in microseconds elapsed since the end of the last wait. The value is 0 if the session is in the waiting state. |
Sample query
Query information about the current or most recent wait event for all sessions on the current node.
obclient [SYS]> SELECT * FROM SYS.V$SESSION_WAIT WHERE ROWNUM =1\G
The query result is as follows:
*************************** 1. row ***************************
SID: 1152921504606867748
CON_ID: 1004
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
EVENT: wait in request queue
P1TEXT: pcode
P1: 5387
P2TEXT: level
P2: 0
P3TEXT: priority
P3: 0
WAIT_CLASS_ID: 102
WAIT_CLASS#: 2
WAIT_CLASS: CONFIGURATION
STATE: WAITED SHORT TIME
WAIT_TIME_MICRO: 14
TIME_REMAINING_MICRO: -1
TIME_SINCE_LAST_WAIT_MICRO: -360953647143855179