245055.1 Oracle Database 10g Enhanced wait model

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.
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).
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.
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.
WHERE = 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.
GLOBAL:- which implies that the holder is a session on another instance.
Following query would be useful to find the blocking session.
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: %’.
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.
Subject: Oracle Database 10g Enhanced wait model
Doc ID: 245055.1 Type: BULLETIN
Modified Date : 26-JUN-2007 Status: PUBLISHED
PURPOSE
Blocking Session
Classification of Events
Break out important latch events
Histogram
Session and System Level Event Class Stats
Combine V$SESSION_WAIT into V$SESSION
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.
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:
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
UNKNOWN:- which implies that we could not figure out the holder.
UNIMPLEMENTED:- which implies that the callback for the event has not been implemented
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.
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.
Added V$SESSION_WAIT_HISTORY
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.
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.