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:
- Problem statement. Identify or state the specific problem in hand
(e.g., poor response time or poorly performing SQL statement).
- 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).
- 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.
- 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.
- Problem resolution. Work to resolve the performance issue (e.g.,
tune the SQL query).
- Testing against baseline. Test to see if the performance issue has
been resolved. For example, request that the user who complained
test the performance.
- 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.
