 Oracle
Tips by BurlesonOracle10g Wait Event Model improvements
In this section we will explain the Wait Event Interface
changes, updates, and improvements introduced in the 10g release.
The wait event model has been steadily gaining ground as a good
tuning tool. Originally introduced in Oracle 7, wait event
analysis got the serious attention of many Remote DBAs in version 8 and
was further improved in Oracle9i.
Overview of Wait Event Model
In a nutshell, the wait event interface provides insight into
where time is consumed. Wait events are collected by the server
process or thread to indicate the ‘wait’ before a process is
completed. As we know, at any given moment an Oracle process is
either busy servicing a request or waiting for something to
happen. Oracle has defined a list of every possible event that an
Oracle process could wait for.
The Wait Event Interface now provides a powerful tool to
monitor the process delays. With its snapshot of the events and
its detailed analysis, it becomes possible for database
administrators to pinpoint areas that need tuning. Wait events
show various symptoms of problems that impact performance.
Examining and solving the resource crunch in those areas
streamline database tuning.
Wait Event Enhancements
Oracle Database 10g introduces many new dynamic performance
views and updates other views. General improvements include:
- New columns in the v$session and v$session_wait views that
track the resources sessions are waiting for.
- A history of waits per session, enabling diagnosis of
performance problems for a desired time frame and period.
- Maintaining wait statistics of each SQL statement in the
library cache
- Histograms of wait durations, rather than a simple
accumulated average
The following list shows the existing views that are modified.
Changes to v$event_name
CLASS# and CLASS columns are added. These columns help to group
related events while analyzing the wait issues. For example, to
list the events related to IO, use the statement,
SELECT name, class#, class
FROM v$event_name
WHERE class# IN (10, 11);
In another example, to group all the events by class to get a
quick idea of the performance issues, use the statement,
SELECT e.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.class#;
Changes to v$session
In the past, sessions experiencing waits were generally located
by joining the v$session_wait view with the v$session view. To
simplify the query, all the wait event columns from v$session_wait
have been added to v$session.
Use the statement below to determine the wait events that
involve the most sessions.
SELECT wait_class,
count(username)
FROM v$session GROUP BY wait_class;
New columns have been added to v$sessions as follows:
SQL_CHILD_NUMBER,
PREV_CHILD_NUMBER, BLOCKING_SESSION_STATUS, BLOCKING_SESSION, SEQ#,
EVENT#, EVENT, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT,
STATE and SERVICE_NAME
Changes to v$session_wait
The new columns include wait_class# and wait_class.
The following list shows the views that are new.
- v$system_wait_class – This view provides the instance-wide
time totals for the number of waits and the time spent in each
class of wait events. This view also shows the object number for
which the session is waiting.
- v$session_wait_class - This view provides the number of
waits and the time spent in each class of wait event on a per
session basis. This view also shows the object number for which
the session is waiting.
- v$event_histogram – This view displays a histogram of the
number of waits, the maximum wait, and total wait time on a
per-child cursor basis. Using this view, you can create a
histogram showing the frequency of wait events for a range of
durations. This information assists you in determining whether a
wait event is a frequent problem that needs addressing or a
unique event.
- v$file_histogram – This view displays a histogram of all
single block reads on a per-file basis. To provide more in-depth
data, the v$file_histogram view shows the number of I/O wait
events over a range of values. You use the histogram to
determine if the bottleneck is a regular or a unique problem.
- v$temp_histogram – This view displays a histogram of all
single block reads on a per-tempfile basis.
- v$session_wait_history – This view displays the last 10 wait
events for each active session.
The new views above are quite helpful in understanding the
overall health of the database. For example, use the
v$system_wait_class view to display wait events occurring across
the database.
SQL> SELECT wait_class#, wait_class,
2 time_waited, total_waits
3 FROM v$system_wait_class
4 ORDER BY time_waited;
WAIT_CLASS# WAIT_CLASS TIME_WAITED TOTAL_WAITS
----------- ---------------- ----------- -----------
5 Commit 10580 29404
2 Configuration 25140 1479
7 Network 28060 35111917
4 Concurrency 34707 16754
8 User I/O 308052 178647
9 System I/O 794444 2516453
1 Application 3781085 68100532
0 Other 38342194 22317
6 Idle 845197701 37411971
9 rows selected.
|
