Oracle OWI wait Event History View and related view

Oracle OWI wait Event History View and related views 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, respectively, 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 [SQL] -- as you can see, 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 the historical sid goex_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 When the block is read into buffer -- seq # is 1 or 2, it indicates that it is in idle waiting state. If WAIT_TIME is 0, it 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 11 6 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 [SQL] -- 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 197 44 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 preceding query, the system waits mostly for Idle, and the overall system runs well. -- To facilitate the demonstration, we can check which sid causes excessive User I/O goex_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 -- The following query shows that the session with sid 1083 has the most experience waiting for the Network to wait for goex_admin @ SYBO2SZ> select * from v $ session_wait_class where sid = 1083. order by 6 desc; sid serial # WAIT_CLASS_ID WAIT_CLASS # WAIT_CLASS TOTAL_WAITS TIME_WAITED ------ ------------------- ----------- certificate ----------- 1083 72 2000153315 7 Network 11 0 1083 6 Idle 10 15 2723168908 72 1083 1 Application 2 0 1083 72 1740759767 8 User I/O 2 3 3. View v $ system_wait_class [SQL] -- this view contains information about the v $ session_wait_class view, displays the total wait time and wait time of the wait 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 Concurr Ency 51928 81428 1893977003 0 Other 38786 43836 4217450380 1 Application 28600 20398 3290255840 2 Configuration 15309 296458 4166625743 3 Administrative 1298 30917 10 rows selected. 4. View v $ event_histogram [SQL] -- v $ event_histogram view is the histogram of waiting events. 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 fil E sync 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. Wait events of Oracle body supply can quickly locate the waiting events that the specified sid is going through. 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.