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.
