Written by: Alireza Kamrani
Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing. Wait event data reveals various symptoms of problems that might be impacting performance, such as latch contention, buffer contention, and I/O contention. Remember that these are only symptoms of problems, not the actual causes.
Wait events are grouped into classes. The wait event classes include: Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O, and User I/O.
A server process can wait for the following:
• A resource to become available, such as a buffer or a latch.
• An action to complete, such as an I/O.
• More work to do, such as waiting for the client to provide the next SQL statement to execute. Events that identify that a server process is waiting for more work are known as idle events.
Wait event statistics include the number of times an event was waited for and the time waited for the event to complete.
If the initialization parameter TIMED_STATISTICS is set to true, then you can also see how long each resource was waited for.
To minimize user response time, reduce the time spent by server processes waiting for event completion. Not all wait events have the same wait time. Therefore, it is more important to examine events with the most total time waited rather than wait events with a high number of occurrences. Usually, it is best to set the dynamic parameter TIMED_STATISTICS to true at least while monitoring performance.
Dynamic Performance Views Containing Wait Event Statistics
These dynamic performance views can be queried for wait event statistics:
• V$ACTIVE_SESSION_HISTORY
The V$ACTIVE_SESSION_HISTORY view displays active database session activity, sampled once every second.
• V$SESS_TIME_MODEL and V$SYS_TIME_MODEL
The V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views contain time model statistics, including DB time which is the total time spent in database calls.
• V$SESSION_WAIT
The V$SESSION_WAIT view displays information about the current or last wait for each session (such as wait ID, class, and time).
• V$SESSION
The V$SESSION view displays information about each current session and contains the same wait statistics as those found in the V$SESSION_WAIT view. If applicable, this view also contains detailed information about the object that the session is currently waiting for (such as object number, block number, file number, and row number), the blocking session responsible for the current wait (such as the blocking session ID, status, and type), and the amount of time waited.
• V$SESSION_EVENT
The V$SESSION_EVENT view provides summary of all the events the session has waited for since it started.
• V$SESSION_WAIT_CLASS
The V$SESSION_WAIT_CLASS view provides the number of waits and the time spent in each class of wait events for each session.
• V$SESSION_WAIT_HISTORY
The V$SESSION_WAIT_HISTORY view displays information about the last ten wait events for each active session (such as event type and wait time).
• V$SYSTEM_EVENT
The V$SYSTEM_EVENT view provides a summary of all the event waits on the instance since it started.
• V$EVENT_HISTOGRAM
The V$EVENT_HISTOGRAM view displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis.
• V$FILE_HISTOGRAM
The V$FILE_HISTOGRAM view displays a histogram of times waited during single block reads for each file.
• V$SYSTEM_WAIT_CLASS
The V$SYSTEM_WAIT_CLASS view provides the instance wide time totals for the number of waits and the time spent in each class of wait events.
• V$TEMP_HISTOGRAM
The V$TEMP_HISTOGRAM view displays a histogram of times waited during single block reads for each temporary file.
Investigate wait events and related timing data when performing reactive performance tuning. The events with the most time listed against them are often strong indications of the performance bottleneck.
For example, by looking at V$SYSTEM_EVENT, you might notice lots of buffer busy waits. It might be that many processes are inserting into the same block and must wait for each other before they can insert. The solution could be to use automatic segment space management or partitioning for the object in question.
System Statistics
System statistics are typically used in conjunction with wait event data to find further evidence of the cause of a performance problem.
For example, if V$SYSTEM_EVENT indicates that the largest wait event (in terms of wait time) is the event buffer busy waits, then look at the specific buffer wait statistics available in the view V$WAITSTAT to see which block type has the highest wait count and the highest wait time.
After the block type has been identified, also look at V$SESSION real-time while the problem is occurring or V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views after the problem has been experienced to identify the contended-for objects using the object number indicated. The combination of this data indicates the appropriate corrective action.
Statistics are available in many V$ views. The following are some of the V$ views that contain system statistics.
V$ACTIVE_SESSION_HISTORY
This view displays active database session activity, sampled once every second.
V$SYSSTAT
This contains overall statistics for many different parts of Oracle Database, including rollback, logical and physical I/O, and parse data. Data from V$SYSSTAT is used to compute ratios, such as the buffer cache hit ratio.
V$FILESTAT
This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
V$ROLLSTAT
This contains detailed rollback and undo segment statistics for each segment.
V$ENQUEUE_STAT
This contains detailed enqueue statistics for each enqueue, including the number of times an enqueue was requested and the number of times an enqueue was waited for, and the wait time.
V$LATCH
This contains detailed latch usage statistics for each latch, including the number of times each latch was requested and the number of times the latch was waited for
Segment-Level Statistics
You can gather segment-level statistics to help you spot performance problems associated with individual segments. Collecting and viewing segment-level statistics is a good way to effectively identify hot tables or indexes in an instance.
After viewing wait events and system statistics to identify the performance problem, you can use segment-level statistics to find specific tables or indexes that are causing the problem.
Consider, for example, that V$SYSTEM_EVENT indicates that buffer busy waits cause a fair amount of wait time. You can select from V$SEGMENT_STATISTICS the top segments that cause the buffer busy waits. Then you can focus your effort on eliminating the problem in those segments.
You can query segment-level statistics through the following dynamic performance views:
• V$SEGSTAT_NAME: This view lists the segment statistics being collected and the properties of each statistic (for instance, if it is a sampled statistic).
• V$SEGSTAT: This is a highly efficient, real-time monitoring view that shows the statistic value, statistic name, and other basic information.
• V$SEGMENT_STATISTICS: This is a user-friendly view of statistic values. In addition to all the columns of V$SEGSTAT, it has information about such things as the segment owner and table space name. It makes the statistics easy to understand, but it is more costly.
Implement and Measure Change
Often at the end of a tuning exercise, it is possible to identify two or three changes that could potentially alleviate the problem. To identify which change provides the most benefit, it is recommended that only one change be implemented at a time. The effect of the change should be measured against the baseline data measurements found in the problem definition phase.
Typically, most sites with dire performance problems implement several overlapping changes at once, and thus cannot identify which changes provided any benefit. Although this is not immediately an issue, this becomes a significant hindrance if similar problems subsequently appear, because it is not possible to know which of the changes provided the most benefit and which efforts to prioritize.
If it is not possible to implement changes separately, then try to measure the effects of dissimilar changes.
For example, measure the effect of making an initialization change to optimize redo generation separately from the effect of creating a new index to improve the performance of a modified query. It is impossible to measure the benefit of performing an operating system upgrade if SQL is tuned, the operating system disk layout is changed, and the initialization parameters are also changed at the same time.
Performance tuning is an iterative process. It is unlikely to find a 'silver bullet' that solves an instance-wide performance problem. In most cases, excellent performance requires iteration through the performance tuning phases, because solving one bottleneck often uncovers another (sometimes worse) problem.
Knowing when to stop tuning is also important. The best measure of performance is user perception, rather than how close the statistic is to an ideal value.
Hope this post was useful for you.
No comments:
Post a Comment