Methodology - Expert Oracle RAC Performance Diagnostics and Tuning - page 7

Methodologies could be different depending on the work involved. There could be methodologies for

• Development life cycle

• Migration

• Testing

• Performance tuning

Performance Tuning Methodology
The performance tuning methodology can be broadly categorized into seven steps.

Problem Statement
Identify or state the specific problem in hand. This could be different based on the type of application and the phase
of the development life cycle. When a new code is being deployed into production, the problem statement is to meet
the requirements for response time and transaction per second and the recovery time. The business analysts, as we
have discussed earlier, define these requirements. Furthermore, based on the type of requirement being validated, the
scope may require some additional infrastructure such as data guard configuration for disaster recovery.
On the other hand, if the code is already in production, then the problem statement could be made in terms of
slow response time that the users have been complaining about; a dead lock situation that has been encountered in
your production environment; an instance in a RAC configuration that crashes frequently, and so forth.
A clear definition of the tuning objective is a very important step in the methodology because it basically defines
what is going to be achieved in the testing phase or test plan that is being prepared.

Information Gathering
Gather all information relating to the problem identified in step one. This depends on the problem being addressed.
If this is a new development rollout, the information gathering will be centered on the business requirements,
the development design, entity model of the database, the database sizing, the cardinality of the entities, the SLA
requirements, and so forth. If this is an existing application that is already in production, the information-gathering
phase may be around collecting statistics, trace, log, or other information. It is important to understand the
environment, the configuration, and the circumstances around the performance problem. For instance, when a user
complains of poor performance, it may be a good idea to interview the user. The interview can consist of several levels
to understanding the issue.
What kind of functional area of the application was used, and at what time of the day was the operation
performed? Was this consistently occurring every time during the same period in the same part of the application
(it is possible that there was another contending application at that time, which may be the cause of the slow
performance)? This information will help in collecting data pertaining to that period of the day and will also help
in analyzing data from different areas of the applications, other applications that access the database, or even
applications that run on the same servers.
Once user-level information is gathered, it may be useful to understand the configuration and environment
in general:

SINGLETON on one instance
or more than one instance ( UNIFORM )? What other services are running on these servers?

• Does the application use database services? Is the service running as

• Is the cluster configured to use server pools?

• What resource plans have been implemented to prioritize the application (if any)?