14.1 Introduction

In the previous chapter we discussed some of the basic tools and utilities available from Oracle for performance monitoring. Tools such as OEM provide a good GUI with drilldown features to determine the specific areas of performance concern. Utilities such as the STATSPACK provide visibility to the health of the database over a specific period of time. The data gathered from STATSPACK can be retained for a user-defined period of time and utilized to a generate trend analysis.

Other opportunities for drilling down into the reasons relating to performance issues with SQL statements were discussed. In this discussion, the EXPLAIN PLAN, SQL Trace, and Oracle event interface provided visibility into some of the ways that Oracle computed the execution plan and how the queries could be tuned to ensure that it is more efficient.

Subsequently, we discussed OWI. The OWI is the method that provides visibility in getting to the bottom of a problem. While the ratios provide the cosmetics by almost always showing good performance when actually the system may be performing slowly, the OWI will help identify where the problem is and the cause of the problem.

All these tools and utilities provide visibility into the methods of accessing data that could be analyzed to determine why the system, application or database is performing poorly.

Performance tuning of any application, including the database, is an iterative process in the sense that it never completes. There is a constant requirement to continuously monitor and tune to maintain...