Oracle owi wait event view (V $ session_wait/V $ session_event/V $ system_event)

Generally, when you submit an SQL statement, there will always be one or more waiting events. That is to say, the process has to wait because the required resources are occupied. Oracle provides us with an available view of these wait events. Based on these views, we can know which events cause the SQL statement to be inefficient and modify or adjust accordingly. Based on Oracle 10 Gb, this article describes how to obtain the wait event information through the view v $ session_wait, V $ session_event, and V $ system_event.

 

1. Wait for event-related parameters
Timed_statistics
Specifies whether time-related statistics are collected when database statistics are collected. This parameter affects the time-related values in the wait event and is subject to statistics_level.
Statistics_level
Specifies the level for collecting database and operating system statistics.
The value range of this parameter is statistics_level = {All | typical | basic}. The level of alter session and alter system can be modified.
The default value is typical.

 

2. View of wait events
You can obtain the view related to the wait event of the current database from the dict database dictionary, as shown in the following SQL statement:
Select *
From dict
Where table_name like '% v $ event %' or table_name like '% v $ % wait % ';

Several important wait Event Views
The following three views provide the statistics and timing of wait events at different granularities.
V $ session_wait (minimum-granularity wait event)
V $ session_event (this view provides a summary of the events waiting for the previous view)
V $ system_event (this view provides a summary of all wait events since the instance is started)

Granularity relationships of several views
V $ session_wait restart v $ session_event restart v $ system_event
The following sections describe these views one by one.

 

3. View v $ event_name
This view lists the descriptions of all wait events in the current Oracle version. Such as wait event ID, name, parameter, category, etc.
Select count (*) from V $ event_name;

Select wait_class, count (*)
From v $ event_name
Group by wait_class
Order by 2;

 

4. View v $ session_wait
This view displays session-level wait information, providing detailed information for the events or resources currently waiting for each session.
That is to say, in view v $ session_wait, the session of each connected instance corresponds to a row of active or inactive information.
In the view, P1, P2, and P3 indicate the specific resources that the corresponding session is waiting for. p1text, p2text, and p3text are text descriptions of P1, P2, and P3.
Starting from Oracle 10g R1, all information of this view can be obtained directly from V $ session.

1) The State field has four meanings ﹕
A. Waiting: the current session is waiting for this event. Seconds_in_wait indicates the waiting time.
B. Waited unknown time: when the value of timed_statistics is false, the waiting time cannot be determined.
C. Wait short time: the wait_time column is shown as-1 in the case of a wait of less than 1 second.
D. Waited known time: if the session waits and the required resources are obtained, the request enters the current state from waiting and the time is sent to the wait_time column.

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

3) The wait_time and seconds_in_wait field values are related to the State:
A. If the value of State is waiting, the value of wait_time is invalid. The seconds_in_wait value is the actual wait time (unit: seconds ).
B. If the state value is wait unknow time or wait short time, the wait_time value and seconds_in_wait value are invalid.
C. 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 seconds_in_wait is invalid.

4) Specify the current session wait event
Select *
From v $ session_wait
Where sid = & input_sid;

 

5. View v $ session_event
This view records the total waiting time, waiting time, and maximum waiting time of each session wait event.
That is to say, this view is a summary of all the waits for each session since it was started.
The SID column in the V $ session_event view provides Sid-based information for waiting for events.

This view is equivalent to a subset of V $ system_event. Both of them provide statistics based on the waiting time.
The difference is that V $ session_event provides session-level statistics, and V $ system_event provides instance-level statistics.
SQL> DESC v $ session_event;
Name type nullable default comments
----------------------------------------------------
SID number y
Event varchar2 (64) y event name
Total_waits number y total number of waits for the current event
Total_timeouts number y number of times the requested resource fails to be obtained after the first wait OF THE SESSION
Time_waited number y total time spent waiting for the event (1% seconds)
Average_wait number y average wait time (important reference column, 1% seconds)
Max_wait number y maximum wait time (1% seconds)
Time_waited_micro number y
Event_id number y
Wait_class_id number y
Wait_class # Number y
Wait_class varchar2 (64) y

 

6. View v $ system_event
Displays the total number of waits for all Oracle sessions after the instance is started, including the total number of waits and total timeouts. This is a summary of all sessions on all wait events.
SQL> DESC v $ system_event;
Name type nullable default comments
----------------------------------------------------
Event varchar2 (64) y
Total_waits number y
Total_timeouts number y
Time_waited number y
Average_wait number y
Time_waited_micro number y
Event_id number y
Wait_class_id number y
Wait_class # Number y
Wait_class varchar2 (64) y

 

7. Demo wait events

--> View the total number of sessions in the current database goex_admin @ sybo2sz> select count (*) from V $ session; count (*) ---------- 20 -- view the current total number of wait events of the database -- The following query result is equal to the number of sessions, that is, a session corresponds to an idle or non-idle wait event goex_admin @ sybo2sz> select count (*) from V $ session_wait; count (*) ---------- 20 -- open a session 1 to update EMP -- session 1scott @ sybo2sz> Update EMP set sal = Sal + 100 Where deptno = 20; 5 rows updated. -- enable another session 2 to try to delete some records on EMP Author: Robinson -- sess Ion 2 is blocked at this time blog: http://blog.csdn.net/robinson_0612 Scott @ sybo2sz> Delete from EMP where deptno = 20; -- we can see that there is an Enq wait event on the current database Sid 1067 (SID of Session 2 in 1067) goex_admin @ sybo2sz> select Sid, event, wait_class, wait_time, seconds_in_wait, state from V $ session_wait where Sid = 1067; Sid event wait_class wait_time seconds_in_wait state -------------------------------------------------------------------------- ---------- ------------------- 1067 Enq: TX-row lock contention application 0 960 waiting -- in the session with Sid 1067, when the Tx lock waits for the resource to be obtained, query the session again, now it has become idle waiting. Goex_admin @ sybo2sz> select Sid, event, wait_class, wait_time, seconds_in_wait, State from V $ session_wait where Sid = 1067; sid event wait_class wait_time wait state ------ wait -------------------- ----------------- wait 1067 SQL * Net message from client idle 0 10 waiting -- The following script queries the wait event goex_admin @ sybo2sz Based on V $ session_event> @ wait_event_by_sidenter value for Sid: 1067 enter value for Sid: 1067 event time_spent ------------------------------------------ Enq: TX-row lock contention 95489 --> the total waiting time is 95489, in the unit of 1% seconds, more precise CPU used when call started 4 than seconds_in_wait -- Query instance-level wait events below -- when you enter Enq, the goex_admin @ sybo2sz> @ wait_event_inst_lvlenter value for input_event_name: enqenter value for input_event_name: enqwait_class event total_waits wait time_waited average_wait startup_time before running ------------ --------------------- ------------- your application Enq: Ro-fast Object reuse 99 1 393 4 20130410 20: 22: 43 Application Enq: TX-row lock contention 382 379 111,476 292 20: 22: 43 concurrency Enq: TX-index contention 3 0 28 9 20130410 20: 22: 43 configuration Enq: HW-contention 27 1 592 22 20130410 20: 22: 43 other latch: enqueue hash chains 5 0 6 1 20130410 20: 22: 43 other Enq: FB-contention 1 0 10 10 20130410 20: 22: 43 other Enq: TX-contention 4 0 158 39 20130410 20: 22: 43 other Enq: CF-contention 6 5 1,556 259 20130410 rows selected. -- When all is input, all wait events of the instance are listed as goex_admin @ sybo2sz> @ wait_event_inst_lvlenter value for input_event_name: allenter value for input_event_name: allwait_class event total_waits wait time_waited average_wait startup_time before running ------------ --------------------- ------------- parse application SQL * Net break/reset to client 99 0 1 0 20130410 20: 22: 43 Application Enq: ro-fast Object reuse 99 1 393 4 20130410 20: 22: 43 Application Enq: TX-row lock contention 382 379 111,476 292 20: 22: 43 commit Log File sync 33,165 3,788 798,189 24 20130410 20: 22: 43 concurrency latch: Library cache pin 5 0 0 0 20130410 20: 22: 43 concurrency latch: library cache lock 5 0 15 3 20130410 20: 22: 43 concurrency Enq: TX-index contention 3 0 28 9 20130410 20: 22: 43 concurrency cursor: mutex s 6,737 0 29 0 20130410 20: 22: 43goex_admin @ sybo2sz> @ wait_event_inst_lvlenter value for input_event_name: allenter value for input_event_name: allwait_class event total_waits wait time_waited average_wait startup_time before running ------------ --------------------- ------------- parse application SQL * Net break/reset to client 99 0 1 0 20130410 20: 22: 43 Application Enq: ro-fast Object reuse 99 1 393 4 20130410 20: 22: 43 Application Enq: TX-row lock contention 382 379 111,476 292 20: 22: 43 commit Log File sync 33,165 3,788 798,189 24 20130410 20:22:43 ...................... concurrency latch: Library cache pin 5 0 0 0 20130410 20: 22: 43 concurrency latch: Library cache lock 5 0 15 3 20130410 20: 22: 43 concurrency Enq: TX-index contention 3 0 28 9 20130410 20: 22: 43 concurrency cursor: mutex s 6,737 0 29 0 20130410 20:22:43

8. scripts used in the demo

a、wait_event_by_sid.sqlrobin@SZDB:~/dba_scripts/custom/sql> more wait_event_by_sid.sqlSELECT event, time_waited AS time_spent  FROM v$session_event WHERE sid = &sid       AND wait_class<>'Idle'       AND event NOT IN              ('Null event',               'client message',               'KXFX: Execution Message Dequeue - Slave',               'PX Deq: Execution Msg',               'KXFQ: kxfqdeq - normal deqeue',               'PX Deq: Table Q Normal',               'Wait for credit - send blocked',               'PX Deq Credit: send blkd',               'Wait for credit - need buffer to send',               'PX Deq Credit: need buffer',               'Wait for credit - free buffer',               'PX Deq Credit: free buffer',               'parallel query dequeue wait',               'PX Deque wait',               'Parallel Query Idle Wait - Slaves',               'PX Idle Wait',               'slave wait',               'dispatcher timer',               'virtual circuit status',               'pipe get',               'rdbms ipc message',               'rdbms ipc reply',               'pmon timer',               'smon timer',               'PL/SQL lock timer',               'SQL*Net message from client',               'SQL*Net message to client',               'SQL*Net break/reset to client',               'SQL*Net more data to client',               'rdbms ipc message',               'WMON goes to sleep')UNION ALLSELECT b.name, a.VALUE  FROM v$sesstat a, v$statname b WHERE a.statistic# = b.statistic# AND b.name = 'CPU used when call started' AND a.sid = &sid;b、wait_event_inst_lvl.sqlrobin@SZDB:~/dba_scripts/custom/sql> more wait_event_inst_lvl.sqlSET LINES 160SET NUMWIDTH 18COL class FOR a15COL event FOR a30COL total_waits FOR 999,999,999COL total_timeouts FOR 999,999,999COL time_waited FOR 999,999,999,999COL average_wait FOR 999,999,999,999COL event FORMAT a30 WRAPCOL wait_class FORMAT a20  SELECT b.wait_class,         a.event,         total_waits,         total_timeouts,         time_waited,         average_wait,         c.startup_time    FROM v$system_event a, v$event_name b, v$instance c   WHERE a.event = b.name         AND UPPER (a.event) LIKE                DECODE (UPPER ('&input_event_name'),                        'ALL', UPPER (a.event),                        UPPER ('%&input_event_name%'))         AND b.wait_class<>'Idle'         AND a.event NOT IN            ('Null event',               'client message',               'KXFX: Execution Message Dequeue - Slave',               'PX Deq: Execution Msg',               'KXFQ: kxfqdeq - normal deqeue',               'PX Deq: Table Q Normal',               'Wait for credit - send blocked',               'PX Deq Credit: send blkd',               'Wait for credit - need buffer to send',               'PX Deq Credit: need buffer',               'Wait for credit - free buffer',               'PX Deq Credit: free buffer',               'parallel query dequeue wait',               'PX Deque wait',               'Parallel Query Idle Wait - Slaves',               'PX Idle Wait',               'slave wait',               'dispatcher timer',               'virtual circuit status',               'pipe get',               'rdbms ipc message',               'rdbms ipc reply',               'pmon timer',               'smon timer',               'PL/SQL lock timer',               'SQL*Net message from client',               'SQL*Net message to client',               'SQL*Net break/reset to client',               'SQL*Net more data to client',               'rdbms ipc message',               'WMON goes to sleep')ORDER BY b.wait_class, a.time_waited;

9. Summary
A. The timed_statistics and statistics_level parameters affect the collection of wait event time slices.
B. The V $ event_name database Dictionary (not a dynamic view here) provides names, categories, and IDs related to all wait events.
C, V $ session_wait provides the currently waiting events or resources for the session of the current instance (for a single event, one session corresponds to one record)
D. V $ session_event provides the total waiting time, waiting time, and maximum waiting time for each event in each session.
E and V $ system_event provide the total number of waiting times since the instance is started, including the total number of waiting times and total timeout, which is a summary of all sessions on all waiting events.
F. If the user says that the response of a session is slow and the CPU usage is too busy, the session must be slow because the session is in a waiting event.
G. Based on the related views described above, you can quickly locate the real cause of slow session.

 

More references

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Oracle rowid

Null Value and index (1)

Null Value and index (2)

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor

Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)