22.2 Detecting Potential Bottlenecks

 < Day Day Up > 



So what can be done to detect potential bottlenecks? If you have read this entire book by now you probably realize that there are multiple methods which can be used to tune Oracle installations. I tend to think of tuning methods as being approached based on the skills set of database administrators. Database administrators generally come from two main schools of thought. The first is development and the second is systems administration, systems administration being by far the more prominent. Developers will take a top-down approach to tuning and systems administrators will take a bottom-up approach to tuning. What does this mean? Developers will approach tuning from the application, leading down into the database. Systems administrators will approach a problem from the operating system. Therefore, developers are more likely to move directly into the data model and particularly application SQL code. Systems administrators will more than likely look at the hardware and the operating system, leading up into the database. The result is that developers are more likely to tune from the perspective of the first two parts in this book while systems administrators will veer towards the third part of this book. This leads us to the purpose of this chapter, which is to explain how to find performance problems by using Oracle Database internal structures, namely database statistics in the Oracle Database Wait Event Interface, using performance views, Oracle Enterprise Manager and STATSPACK.

Part II of this book gradually built up to tuning of SQL code, eventually describing the "how" and "what with" of tuning SQL code. Part III has presented various fundamentals of physical and configuration tuning as already mentioned. This final chapter of tuning in Part III is thus a method of tuning most likely to be used by systems administrators but very useful for everyone. Therefore, there has to be some complexity to promote understanding followed by easy solutions. This chapter as a result covers what is often called the Oracle Database Wait Event Interface, or quite simply put, a bunch of performance views allowing drilling into wait events and all sorts of statistics, allowing for detection of potential bottlenecks. The objective is obviously to tune the bottlenecks.

Therefore, using the Oracle Database Wait Event Interface is simply another tuning method or tool and can often provide database administrators with enough ammunition to convince developers where SQL code can be altered to drastically improve performance. Obviously there are numerous steps database administrators can take to improve performance without involving developers or having to persuade them to alter application code. However, there is a point where application-based SQL code simply must be changed if better performance is required.

The other aspect of using the Oracle Database Wait Event Interface for tuning Oracle installations is that it tends to be a reactive rather than a proactive tuning method. This means that wait event tuning is used to drill down into problems as they occur and is thus akin to putting out fires rather than preventing them. This is often the only available tuning method due to high time and cost limitations placed on the development process. Perhaps more planning and expense may appear to be required if tuning is done proactively. Proactive tuning means writing better SQL code and designing the database model for performance in the first place. However, this is often not possible. Therefore, reactive tuning is usually the only available approach.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net