Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 10, 2005

Oracle: Collecting Real Time Wait Events

By DatabaseJournal.com Staff


By Alexander Bubernak

Wait event tuning has become very popular in recent years. Many DBAs are moving away from the cumbersome Ratio approach of tuning and using wait events. Ratio tuning has been proven unreliable in general and it can lead to a hit or miss way of troubleshooting and fixing problems. The ratio tuner would also have to have various complex scripts and an advanced knowledge of Oracle internals to try to figure out or make an educated guess of where the problem may be. This process may have to be repeated many times before a problem is resolved.

Collecting wait events will show where a database or session is spending most of its time. Once this is known, you can drill down to the root cause of the wait. You also do not need a large amount of complex scripts; a simple query can show what event the database or session is waiting on.

All databases will have waits events even in a perfectly tuned database and application. Some events point to bottlenecks while others can point to the limitation of the server, CPU, IO, etc. and others are normal wait events like certain idle events.

Idle events are events that spend their time idle or waiting for an action. The time keeps incrementing for the event until an action occurs. An action can be a database process, user or application process. These wait events can be ignored most of the time. Be careful, because if the event is idle and you expect it not to be, then this can show where a potential problem is. For example, the event 'SQL*Net message from client' is a true idle wait event. The database is waiting for a response from the client to do something. If you log onto the database using SQL*Plus and not issue a command, this event's wait time will increase as it waits for a command from you. In this case, this idle event can be ignored. Now, if an application or batch program is processing code during this event, it can mean that the application or batch program is taking longer than it should to process.

There are two common ways of collecting wait event information: the Oracle extended trace (10046 event), which I discuss in article "Collecting Oracle Extended Trace (10046 event)", and using V$ views; which is the main topic of this article. You can miss events, timing and certain other details using V$ views instead of 10046 event because they only show the wait event at the moment in time you select from them. However, the events you will miss are probably so fast that they are not that significant for troubleshooting a large bottleneck in a database or user session at the time users report a performance problem.

I will focus this discussion on collecting wait events at the user session level. Database users are the ones that will notice the slow down and they do not care that everything looks good at the database level. They only know that they are losing production time because of the problem. Focusing on user waits, in my experience, will help overall database performance.

You also need to make certain that you collect properly scoped data. If a process runs for an hour, you can collect events for that hour and see where that process has spent most of its time. Now, if a user is logged on for an hour and everything is running fine except for the last 5 minutes and you look at the average or overall wait events you may miss the real bottleneck because that wait is only a small piece of the entire session.

Oracle provides a number of views to aid in tuning; a few are:

  • V$SYSTEM_EVENT, which gives you total system waits for an event since database startup.
  • V$SESSION_EVENT, which is at the session level.
  • V$SESSION_WAIT gives you what an active session is waiting on at that point in time.
  • V$SESSION, which gives you session information and has a column ROW_WAIT_OBJ# that tells you the object involved when joined with OBJECT_ID or DATA_OBJECT_ID in DBA_OBJECTS. ROW_WAIT_OBJ# column is in version 8i on up, but is not populated until version 9i and above.

Version 10g has some more V$ views for tracking history called V$%_HISTORY, including V$ACTIVE_SESSION_HISTORY (ASH), which samples non-idle wait events every second and new columns in V$SESSION to combine information in V$SESSION_WAIT. Oracle 10g specific wait events will be covered in much more detail in another article.

Detail of Views

V$SYSTEM_EVENT - Aggregated statistics for wait events since last database startup.

EVENT - Name of event.

TOTAL_WAITS - Number of times had to wait for an event.

TOTAL_TIMEOUTS - Total amount of times went to get an event resource and timed out waiting for the event.

TIME_WAITED - Sum of time waited for an event in centiseconds (1/100).

AVERAGE_WAIT - Average amount of time waiting for an event in centiseconds (1/100).

TIME_WAITED_MICRO - Sum of time for an event in microseconds (1/1000000). This column is 9i and above.

EVENT_ID - ID of the wait event. Column is in 10g.

V$SESSION_EVENT - Aggregated statistics for wait events since start of session. It has the same columns and definitions as V$SYSTEM_EVENT except for the following additional columns.

SID - Session ID

MAX_WAIT - Maximum time waited for an event.

V$SESSION_WAIT - Unlike the previous view, this view does not contain aggregated statistics; it contains statistics on the event the session is waiting on at the moment. The P(n) columns are an important piece of information used to help determine where the problem exists.

SID - Session ID.

SEQ# - Sequence number that uniquely identifies the wait event. This sequence is incremented with each wait event completion.

EVENT - Name of wait event.

P1TEXT - A description of what the P1 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.

P1 - This value definition is dependent on the event. Use PARAMETER1 value from V$EVENT_NAME to describe what this value is used for.

P1RAW - Same value as P1 except in hexadecimal format.

P2TEXT - A description of what the P2 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.

P2 - This value definition is dependent on the event. Use PARAMETER2 value from V$EVENT_NAME to describe what this value is used for.

P2RAW - Same value as P2 except in hexadecimal format.

P3TEXT - A description of what the P3 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.

P3 - This value definition is dependent on the event. Use PARAMETER3 value from V$EVENT_NAME to describe what this value is used for.

P3RAW - Same value as P3 except in hexadecimal format.

WAIT_CLASS_ID - ID of the wait class. Column is in 10g.

WAIT_CLASS# - Number of the class. Column is in 10g.

WAIT_CLASS - The name of the wait class (Idle, Network, System I/O, etc.). Column is in 10g.

WAIT_TIME - The last amount of time that the session waited for an event. A 0 value means the session is currently waiting on the event. This value of 0 will not increase until the session completes the wait event. Time is in centiseconds.

SECONDS_IN_WAIT - If WAIT_TIME equals 0, then this is the number of seconds spent waiting on the event so far. This can give you complicated results when a timeout occurs like with enqueues because it will reset to 0, but this is beyond the scope of this article.

STATE - State of the wait event: WAIT_TIME=0 - WAITING, the process is in the state of waiting on an event. WAIT_TIME=-1 - WAITED SHORT TIME, wait time completed in less than a centisecond. WAIT_TIME=-2 - WAITED UNKOWN TIME, duration of wait is unknown because TIME_STATISTICS was set to false. WAIT_TIME>0 - WAITED KNOWN TIME, wait completed. WAIT_TIME is the duration of the wait event in centiseconds.

V$SESSION - Session details, the only listing column used in the scope of this article.

ROW_WAIT_OBJ# - This is the object id of the object involved and is joined to the DBA_OBJECTS.OBJECT_ID or DBA_OBJECTS.DATA_OBJECT_ID column. This column is only populated in version 9i and above. A value that is not -1 means that the session is waiting on a lock for this object. Previous versions of the database will need to use P(n) values from V$SESSION_WAIT to find objects in DBA_EXTENTS.

V$EVENT_NAME - This view displays the definition of P(n) columns from V$SESSION_WAIT and can be joined on the NAME column.

EVENT# - Number of the wait event.

NAME - Name of the event.

PARAMETER1 - Description of the value for P1.

PARAMETER2 - Description of the value for P2.

PARAMETER3 - Description of the value for P3.

WAIT_CLASS_ID - ID of the wait class. Column is in 10g.

WAIT_CLASS# - Number of the class. Column is in 10g.

WAIT_CLASS - The name of the wait class (Idle, Network, System I/O, etc.). Column is in 10g.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM