 |
|
Oracle Tips by Burleson |
Wait Interface – A New Approach
For the past couple of years, this was the
state of affairs for database administrators. Simply tune by ratios
and hope for the best. In the past several years, however, a new
methodology has started picking up steam. It is frequently called
the Wait Interface movement and loyal adherents to this system
appear to be afraid to even use the word ratio. Wait Interface
simply refers to the mechanism by which the Remote DBA can interface with
the database to see where it is spending its time, whether that time
is waiting or working. If a large amount of time is spent waiting
for a resource, this might be an indicator of why the users are
calling complaining about performance.
Skepticism reigned as the topic of Wait
Interface surfaced at a user conference. After all, Oracle wasn’t
making the presentations and it just seemed too good to be true.
Gaja Vadyanatha’s presentation, Myths and Folklore IOUG Live! 2001,
changed all that. Understanding the practical application of the
Wait Interface was the key to understanding this new approach. This
new approach was different from the holy grail of ratio tuning and
frequently seemed a mirage. For example, why would increase the
buffer cache if the slow response time is being caused by an
overloaded disk controller or inefficient SQL statements?
The Wait Interface was introduced to the
Oracle code several versions ago in Oracle 7 and has become more
reliable and robust with each version and release. Over time, it
has gained more attention and with that, better understanding of how
to use it to tune performance. It consists of dynamic performance
views which are primarily v$system_event, v$session_event,
v$session_wait and v$event_name.
Since the arrival of Windows in the computer
world, interface is usually associated with graphical, as in
Graphical User Interface (GUI). Therefore, the name wait interface
implies a method of access to look at the waits in the database.
However, there is nothing graphical about this interface. In fact,
the dynamic performance views that are used are simply views into
the system statistics. SQL queries are used to examine these views
to see where the database is spending its time. There are some
vendors and others in the open source world that have created GUI
tools to access the wait interface and others have enhanced their
products to include screens to look at the wait events. These are
handy resources, but it is important to understand the foundational
concepts. Knowing what the wait events are available and how to fix
them is more important than whether to use a set of scripts from the
Internet or a licensed tool with lots of flashing colors.
To better understand the wait interface the
commuting example from earlier will be used in the hopes that it may
illustrate the concepts using a real-world scenario:
30 minutes to get to work =
20 minutes driving +
10 minutes waiting for traffic lights
In this case, since the drive time is the
biggest component of the commute, start looking there for ways to
shorten drive time. If drive time is increased because of the use
of side streets, maybe adding a few miles to the trip in order to
use a freeway might reduce the drive time since the speed limit is
higher. Perhaps the freeway is more like a parking lot since
everyone else uses the same route at the same time. Going into work
45 minutes earlier could cut drive time by 25%. Maybe a new engine
will enhance performance since the current one has trouble keeping
up with the speed limit. The moral of that story is that there is
no standard answer using ratio methodology so it is necessary to
look at the big picture. Notice that unlike the earlier stoplight
ratio-tuning example, the first component under investigation is
drive time, not time at stoplights.
When using the wait interface and after
identifying the largest chunk of time, that becomes the first target
for enhancement. If most of the time is spent in file I/O
operations, it probably does not make any sense to try and tune the
library cache.
This approach is one of the 2 main
approaches that will be discussed in this book. This concept was
not mentioned in a Performance Tuning class from Oracle Education a
few years ago. Rumor has it that the class is being retooled to
address wait-based tuning. The wait-interface approach is valuable
because it unambiguously tells where the database is hurting which
allows changes to be made to have the biggest positive impact on
performance.
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Interface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
