Oracle owi wait Event History View and related view

The wait event view provided by Oracle allows us to obtain detailed information about the specified session and instance-level wait events. These views are V $ session_wait, V $ session_event, and V $ system_event. However, these views are powerless for historical wait events. In this regard, Oracle also provides the historical wait event view v $ session_wait_history, And the view v $ session_wait_class and V $ system_wait_class also provide performance analysis based on the waiting category, the following is a description based on Oracle 10 Gb.

 

1. View v $ session_wait_history

-- We can see that the V $ session_wait_history view provides information about historical wait events. -- V $ session_wait_history is basically the same as V $ session_wait. Only the seq #, event #, and wait_count columns are added. The -- seq # column indicates the waiting events encountered by the session in sequence. The most recent waiting event is 1, and the farthest is 10. -- This view can be used to quickly learn about the waits that the specified Sid has experienced recently (in the US, only the last 10 waiting events are provided ). -- When a new wait event is started, the information in this view will be refreshed, the event with the longest record time will be removed, and the latest wait event is 1, move another sequence number to the backend. The -- wait_time column indicates the waiting time of the event in the session. = 0: The system is waiting for the event to complete;> 0: The last wait time. -- Wait_cont indicates the number of waits. -- Query history sidgoex_admin @ sybo2sz> select distinct Sid 2 from V $ session_wait_history H 3 where not exists 4 (select 1 5 from V $ session_event 6 where Sid = H. sid ); sid ---------- 1062 1065 1060 -- query the history wait event information with Sid 1062 below -- The following query shows that the SID has experienced a series of wait events -- seq #10 indicates that the SQL is when the database is parsed to the library cache, the wait -- seq # is 9, which indicates the wait encountered when the data dictionary is loaded to the buffer. The value of seq # is 7 or 8, indicating that the database file sequential is returned. read wait -- seq #6 indicates that the data block required by the current Sid is read into the buffer, at this time, other sessions are reading the block into the buffer -- seq # is 1 or 2, indicating that the block is in idle waiting state. The value from wait_time to 0 can also be known as idle waiting state, then the SID is disabled goex_admin @ sybo2sz> select Sid, seq #, event #, event, wait_time, wait_count from V $ session_wait_history where Sid = 1062; sid seq # event wait_time wait_count ---------- --------------------------------- ---------- 1062 1 257 SQL * Net message from client 0 1 1062 2 253 SQL * Net message to client 0 1 1062 3 116 DB file sequential read 9 1 1062 4 257 SQL * Net message from client 0 1 1062 5 253 SQL * Net message to client 0 1 1062 6 67 read by other session 6 1 1062 7 116 DB file sequential read 11 1 1062 8 116 dB file sequential read 1 1 1062 9 205 latch: row cache objects 9 1 1062 10 212 latch: Library cache 5 1

2. View v $ session_wait_class

-- This view provides information about all sessions currently connected to the database instance. -- View All session wait classes goex_admin @ sybo2sz> select wait_class #, 2 wait_class, 3 sum (total_waits), 4 sum (time_waited) 5 from V $ session_wait_class 6 group by wait_class #, wait_class 7 order by 3, 4; wait_class # wait_class sum (total_waits) sum (time_waited) ----------- hour ---------------------------- 1 Application 42 0 2 configuration 1219 110012 5 commit 1332 154 0 other 14387 19744 4 concurrency 34092 74180 8 user I/O 1945820 42429 9 system I/O 3899256 731677 7 network 4580071 9068 6 idle 10577566 3175310702-from the above query, the system waits mostly for idle, the overall system runs well. -- To facilitate the demonstration, we can check which Sid causes excessive user I/ogoex_admin @ sybo2sz> select Sid, 2 time_waited, 3 total_waits, 4 round (time_waited/total_waits, 2) as percnt 5 from V $ session_wait_class 6 where wait_class # = 8 and rownum <2 7 order by percnt; Sid time_waited total_waits percnt ------ ----------- ---------- 1014 12088 186162. 06 -- once the SID that causes too many wait events is found, the corresponding SQL statement can be further found. You can also find all the waiting events experienced by this Sid. -- View the waiting category for a single session -- Author: Robinson -- Blog: queue> select * from V $ session_wait_class where Sid = 1083 order by 6 DESC; sid serial # wait_class_id wait_class # wait_class hour time_waited ------ ------------------- ----------- hour ------------- ----------- 1083 72 2000153315 7 network 11 0 1083 72 2723168908 6 idle 10 15 1083 72 4217450380 1 Application 2 0 1083 72 1740759767 8 user I/O 2 3

3. View v $ system_wait_class

-- This view contains information about the V $ session_wait_class view. It displays the total waiting time and waiting time for the class at the instance level after the instance starts. -- This view evaluates the database performance based on the overall wait event type of the database, rather than a single Wait event. -- The following query shows that the most wait events are based on user classes. Focus on user I/O. SQL> select * from V $ system_wait_class order by 4 DESC; wait_class_id wait_class # wait_class total_waits time_waited ------------- ----------- hour ----------- 1740759767 8 user I/O 276741470 5086685 2000153315 7 network 131198524 177709 6 idle 2723168908 33734334 9 system I/O 6334346903 4108307767 11799468 2288863 5 commit 1204500 91997 3875070507 4 concurrency 51928 81428 1893977003 0 other 38786 43836 4217450380 1 Application 28600 20398 3290255840 2 configuration 15309 296458 3 Administrative 4166625743 1298 rows selected.

4. View v $ event_histogram

-- V $ event_histogram view is the waiting event histogram. It is similar to the histogram used in the column to describe the frequency of wait events within a specific waiting period. -- Based on the Analysis of the frequency of some specific wait events, we can determine whether the wait event is in an abnormal state and take further measures. -- Analyze the following query results: goex_admin @ sybo2sz> select * from V $ event_histogram where event # = 115; event # event wait_time_milli wait_count ---------- ------------------- --------------- ---------- 115 Log File sync 1 1739 115 Log File sync 2 1151 Log File sync 4 115 Log File sync 8 374 Log File sync 16 115 1637 115 log File sync 32 4070 115 Log File sync 64 4308 115 Log File sync 128 3698 115 Log File S YNC 256 3491 115 Log File sync 512 3556 115 Log File sync 1024 6083 -- the preceding query shows the number of times Log File sync wait events occur based on the event frequency since the instance is started. -- In the range between 6083 ms and Ms, the number of occurrences of this event reaches. In the case of less than 2 ms, there were also 1739 times. There are obvious exceptions. -- The above situation indicates that the database is experiencing a serious log file sync event. In the above case, we should also see the waiting event in the top event of the AWR report. -- Log File sync waits for the event to be related to the transaction commit rollback, indicating that there are too many commits or short transactions. Check whether batch submission, log file size, and buffer size are reasonable.

5. Summary
A. The waiting events of Oracle body supply can quickly locate the waiting events that the specified Sid is going through. Reference: Oracle owi wait event view
B. You can use the V $ session_wait_history view to query the last 10 pending information of the historical wait events.
C. View v $ session_wait_class provides detailed metrics for all session-level waiting events based on the waiting event category.
D. View v $ system_wait_class provides detailed information for measuring wait events at the instance level (after the instance is started) according to the wait event category.
E. View v $ session_wait_class and view v $ system_wait_class consider the wait event bottleneck of the current system from the overall (wait event class), rather than a single Wait event.
F. View v $ event_histogram provides histogram information for the wait event. Serious wait events can be displayed in this view if they appear in the top event.

 

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)