Performance tuning a RAC implementation

The following is an excerpt from Chapter 9 of Oracle 10g RAC Grid, Services & Clustering by Murali Vallath. Vallath is an Oracle Certified Database Administrator with expertise in implementation, configuration and performance tuning of Oracle parallel server/Oracle Real Application Clusters. Vallath currently provides consulting services through Summersky Enterprises LLC.

Click here to read the full chapter.

Performance tuning of any application, including the database, is an iterative process. This means that to maintain a healthy database, one must constantly monitor and fine-tune it. During certain periods, an aggressive performance tuning of both the application and database may be required. At other times, only routine continuous monitoring and maintenance may be needed. During this time, system hiccups may be discovered and solutions tried and tested.

The goal of a DBA or the application developer is to provide efficient, well-performing applications with good response time. In order for the application to provide a good response, the system, database, and SQL queries should be well tuned. Systems are tuned based on data collected during periods of poor performance; the evidence and the data collected may provide an indication of where the actual problem resides. For 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 every time there is a need to tune the system.

In this chapter, we will look into a scientific approach to troubleshooting, performance tuning, and maintaining a healthy database system. Tuning a RAC implementation has many aspects, and the techniques will vary depending on whether the RAC cluster is preproduction or live. Since a RAC configuration comprises one or more instances connected to a shared database, tuning a RAC configuration ideally starts with tuning the individual instances prior to the deployment of the production cluster. Individual instances in the cluster should be tuned using the same techniques used for single-instance databases. Once the individual instances are tuned, the other tiers, network, interconnect, cluster manager, and so on, should be incorporated into the tuning process.

Methodology

Problem-solving tasks of any nature need to be approached in a systematic and controlled manner. There needs to be a defined procedure or an action plan, and this procedure needs to be followed step by step from start to finish. During every step of the process, data is collected and analyzed, and the results are fed into the next step, which in turn is performed using a similar systematic approach. Hence, methodology is the procedure or process followed from start to finish, from identification of the problem to problem solving and documentation. A methodology is a procedure or process that is repeatable as a whole or in increments through iterations. During all of this analysis, the cause or reasons for a behavior or problem should be based on quantitative analysis and not on guesswork.

The performance tuning methodology can be broadly categorized into seven steps:

1. Problem statement. Identify or state the specific problem in hand (e.g., poor response time or poorly performing SQL statement).
2. Information gathering. Gather all information relating to the problem identified in step one. For example, when a user complains of poor performance, it may be a good idea to interview him or her to identify what kind of function the user was performing and at what time of the day (there may have been another contending application at that time, which may have caused the slow performance).
3. Area identification. Once the information concerning the performance issue is gathered, the next step is to identify the area of the performance issue. For example, the module in the application that belongs to a specific service type may be causing the performance issue.
4. Area drilldown. Drill down further to identify the cause or area of the performance issue. For example, identify the SQL statement or the batch application running at the wrong time of day.
5. Problem resolution. Work to resolve the performance issue (e.g., tune the SQL query).
6. Testing against baseline. Test to see if the performance issue has been resolved. For example, request that the user who complained test the performance.
7. Repeating the process. Now that the identified problem has been resolved, attempt to use the same process with the next problem.

Click here to read the full chapter.

Printed with permission from Digital Press, a division of Elsevier. Copyright 2006. Oracle 10g RAC Grid, Services & Clustering by Murali Vallath. For more information about this book and other similar titles, please visit elsevier.com.