BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  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

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.