Wait Event Enhancements in Oracle 10g

Oracle Database 10g Enhanced wait model [ID 245055.1]
		Modified 26-JUN-2007     Type BULLETIN     Status PUBLISHED

PURPOSE

This bulletin outlines Enhanced wait model introduced in Oracle Database 10G.

SCOPE & APPLICATION

The Oracle Database 10g has many improvements for the wait reporting mechanism. These include:

1. Blocking Session
2. Classification of Events
3. Break out important latch events
4. Histogram
5. Session and System Level Event Class Stats
6. Combine V$SESSION_WAIT into V$SESSION
7. Added V$SESSION_WAIT_HISTORY
8. NEW "WAIT_CLASS" columns in V$EVENT_NAME

1. Blocking Session

Two new columns blocking_session and blocking_session_status have been added to V$SESSION view.

Column "blocking_session" would contain the session id of the resource holder for which a session is waiting for. Else it would contain Null.

Column "blocking_session_status" would contain the status of the value of the blocking_session column.

The Value of the columns could be:

• VALID:-                         A valid Session ID is present in the Blocking Session column
• NO HOLDER:-              which implies that there are no holders of this resource.
• UNKNOWN:-               which implies that we could not figure out the holder.
• UNIMPLEMENTED:-   which implies that the callback for the event has not been implemented
• GLOBAL:-                     which implies that the holder is a session on another instance.

Following query would be useful to find the blocking session.

SELECT sid, blocking_session, username, blocking_session_status

FROM v$session

ORDER BY blocking_session;

2. Classification of Events 

All wait events have been classified into categories like contention wait/OS service wait/DB Service ,Wait/idle wait, etc.. This will enable the user to immediately find out whether the system is performing poorly due to excessive contention or a background not performing well or whether the Operating System does not have enough resources.

A category has many wait events assigned to it, so the below example shown displays the total waits reported for each class. The wait event class gives an overall view of a particular area. For example, the I/O wait category contains all wait events associated with disk I/O. As a rule, the wait event categories with the highest wait times and counts become the focus of tuning effort.

Example :

col wait_class format a30

SELECT e.wait_class#, e.wait_class,sum(s.total_waits), sum(s.time_waited)

FROM  v$event_name e, v$system_event s

WHERE  e.name = s.event

GROUP BY e.wait_class#,e.wait_class;

Classify the wait events into:

• Idle Waits: Whenever an Oracle process has no work to do this is an idle wait. For most processes this is because they are waiting on the user to provide a new SQL statement to execute.
• Application: These are waits caused by the way the application is designed. These include row lock waits, and table or other locks that are requested by the application either explicitly or implicitly (possibly due to DDL).
• Configuration: These are waits which occur in a badly configured system and weill be reduced dramatically as a result of proper tuning.
• Administrative: These are waits imposed by a privileged users by some action.
• Concurrency: These are waits that can not be tuned and will occur on a system with High Concurrency.
• Commit: This class only has log file sync. It deserves a special class because it is a necessary event and will be high and is supposed to be high on a system doing queries.
• Network: All waits due to network messaging delays belong here. They are supposed to point out network congestion or latency. They should not include think or processing time, only the time spent in the networking code and hardware.
• User I/O Waits: All waits for Disk I/O done by User queries or even SMON, MMON
• System I/O Waits: All waits for Disk I/O done by backgrnd processes like LGWR, DBWR, ARCH, RFS. But not SMON and MMON
• Scheduler: These are waits due to the resource manager
• Cluster: waits which will occur only in RAC mode.
• Other: All the wait events, which do not fit into one of the above classes clearly, or are not important to classify. By not important I mean those that wait for an insignificant amount of time or really do not fit into any one class.

3. Break out important latch events

Before Oracle Database 10g, it is not possible to see the latch waits on a session basis. When a session is waiting it waits on the latch free event, which does not tell much. One needs to see p1, which gives address, and find out which latch is it. Also, One does not know which are the sessions and which are getting the latch and resulting in contention.

Thus, the high contention in the database are breaking out separate wait events for certain latches which have known to be points of past. Some latches events will also have different wait classes. All the event names will have prefix of “latch: “ so that users can get all latch events by writing like ‘latch: %’.

Example:

SELECT event,p1,p2,p3 

FROM v$session_wait

WHERE event like 'latch%';

4. Histogram

The following views have been introduced from Oracle Database 10G which would be useful to generate histogram for system events and I/Os at file level.

• V$EVENT_HISTOGRAM
• V$FILE_HISTOGRAM
• V$TEMP_HISTOGRAM

To collect histogram data in these views database parameter timed_statistics should be set to TRUE.

4.a V$EVENT_HISTOGRAM

The v$system_event view displays the number of waits, the maximum wait time, and the total time waited per wait event. This view that show wait event information do not offer information on the time of each wait event occurrence. A single very long wait can skew the information offered by this view, these waits might not be indicative of the system as a whole.

The v$event_histogram view remedies this. Using this view, you create a histogram showing the frequency of wait events for a range of durations. To provide you with more in depth data the v$event_histogram view shows the number of waits for a wait event over a range of time values. You use the v$event_histogram view to determine if the bottleneck is a regular or a unique problem.

This view has the following columns:

• event# :- The number assigned to the wait event. This corresponds to the event# column in the v$event_name table.
• Event:- The name of the wait event. This corresponds to the name column in the v$event_name table.
• wait_time_milli:- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• wait_count:- The number of waits of duration belonging to this bucket of the histogram

4.b V$FILE_HISTOGRAM

The v$filestat view displays statistics per data file. This information can be skewed due to a couple of very large I/O waits, thus these waits might not be indicative of the system as a whole.

V$FILE_HISTOGRAM displays a histogram of all single block reads on a per-file basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms. The histogram can be used to determine if the bottleneck is a regular or a unique problem.The frequency of each wait event versus the length of the wait events would be seen using the information in the v$file_histogram view.

The following columns make up the v$file_histogram view:

• FILE#:- File number
• SINGLEBLKRDTIM_MILLI :-The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram

4.c V$TEMP_HISTOGRAM

V$TEMP_HISTOGRAM for temporary files. V$TEMP_HISTOGRAM displays a histogram of all single block reads on a per-tempfile basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

• FILE# :- File number
• SINGLEBLKRDTIM_MILLI :- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram.

5. Session and System Level Event Class Stats

The following views have been introduced from Oracle Database 10G, which display total wait wide instance and session.

•   v$system_wait_class
•   v$session_wait_class

5.a v$system_wait_class

The v$system_wait_class view displays the instance wide totals of the time waited and the number of times wait events of this class have occurred. Both of these totals are running totals started when the instance started.

The view consists of the following columns:

• wait_class# :- The wait class number
• wait_class :- The name given to the wait class when registered
• time_waited :- The amount of time spent in this wait by all sessions in the instance
• total_waits :- The number of times waits of this class occurred

From the output of the below query you can determine which classes of wait event you should investigate further.

SELECT wait_class#, wait_class, time_waited, total_waits

FROM v$system_wait_class

ORDR BY time_waited;

5.b v$session_wait_class

The v$session_wait_class view shows the time spent in various classes of wait event operations on a per session basis. The view includes the following columns:

• sid :- The session id (same as in v$sesstat)
• serial# :- The serial number
• wait_class# :- The wait class number
• wait_class :- The name given to the wait class when registered.
• time_waited :- The amount of time spent in this wait class by this session
• total_waits :- The number of times waits of this class occurred for this session

After determining that a wait event class is affecting performance (from the v$system_wait_class view) use v$session to determine the sessions that are experiencing the specific wait event class.

Use the below SQL statement to determine the sessions that have wait events associated with this class.

SELECT  sid,serial#,time_waited,total_waits

FROM v$session_wait_class

WHERE  wait_class# =

ORDER BY time_waited;

Note: Do not expect the wait_time and wait_count columns to add up to the total in v$system_wait_class since it is likely that some sessions would have already ended and therefore would not appear in the v$session_wait_class view.

6. Combine V$SESSION_WAIT into V$SESSION

In the previous releases to determine the sessions experiencing waits, we need to join the v$session_wait view with the v$session view.

In the Oracle Database 10g all wait event columns from v$session_wait have been added to v$session thus increasing performance by eliminating the overhead of joins.

7. Added V$SESSION_WAIT_HISTORY

In the previous releases, we cannot see the last few waits of a session. From Oracle Database 10g a new view V$Session_wait_history will allow us to see the last few wait events a session waited on.

The last 10 wait events that a session experienced can be displayed using the v$session_wait_history view. The session has to be currently active. Once the session ends this information is not available.

We can use the seq# column to sort the wait events into the order in which the wait events occurred for the session.

SELECT sid,seq#,event

FROM v$session_wait_history

WHERE sid = ;

8. NEW "WAIT_CLASS" columns in V$EVENT_NAME

The following columns have been added to the v$event_name view:

• wait_class# :- The wait class number
• wait_class :- The name of the class of wait events

Following query can check waits caused by I/O:

SELECT name, wait_class#, wait_class

FROM v$event_name

WHERE wait_class# in (10,11);

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Back to top

Rate this document 

Article Rating Rate this document Excellent Good Poor Did this document help you? Yes No Just browsing How easy was it to find this document? Very easy Somewhat easy Not easy		Comments Provide feedback for this article. Please use 'Contact Us' for other feedback. Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel