Chapter 14: Tuning a Single Instance

---

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 the overall health of the database. While at certain periods an aggressive performance tuning of the application and database may be required, during other times it is just routine continuous monitoring and tuning.

As we have discussed in the previous chapter, performance tuning is not an art; the systems are tuned based on data that is collected during the periods of poor performance as evidence, and the data collected provides an indication where the actual problem could potentially reside. However, many times it is not that simple. For the continuous monitoring and tuning of systems, a process or method should be adopted that helps streamline the activity. As in most repeatable situations, a methodology should be adopted, and once it has been validated and approved it needs to be practiced. This methodology should be iterated all day, every day, to continuously tune the systems.

In this chapter we will look into the scientific approach of troubleshooting, performance tuning, and maintaining a healthy database system. Tuning a RAC implementation has many tiers. However, since a RAC configuration comprises one or more instances connected to a shared database, tuning of a RAC configuration starts with tuning a single instance. This single instance should be tuned similar to tuning a stand-alone configuration of the database and once this is tuned the other tiers, network, interconnect, cluster manager, etc., are to be incorporated into the tuning process.

---