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

Within the context of the business requirements the key terminologies used in these definitions should also
be defined: for instance, 95% of cases; Transaction X should complete within 1 second. What's a transaction in this
context? Is it the time it takes to issue the update statement? Or is it the time it takes for the user to enter something
and press the “update” or “commit” button? Or yet, is it the entire round-trip time between the user pressing the “OK”
button and the database completing the operation saving or retrieving the data successfully and returning the final
results back to the user?
Early understanding of the concepts and terminology along with the business requirements helps all stack
holders of the project to have the same viewpoint, which helps in healthy discussions on the subject.

• Throughput : Number of requests processed by the database over a period of time normally
measured by number of transactions per second.

• Response time : Responsiveness of the database or application to provide the requests results
over a stipulated period of time, normally measured in seconds.

In database performance terms, the response time could be measured as database time or db time. This is the
amount of time spent by the session at the database tier performing operations and in the process of completing
its operation, waiting for resources such as CPU, disk I/O, and so forth.

Tuning the System
Structured tuning starts by normalizing the application workload and then reducing any application contention.
After that is done, we try to reduce physical I/O requirements by optimizing memory caching. Only when all of that is
done do we try to optimize physical I/O itself.

Step 1: Optimizing Workload
There are different types of workloads:

• Workloads that have small quick transactions returning one or few rows back to the requestor

• Workloads that return a large number of rows (sequential range scan of the database) back to

the requestor

• A mixed workload where the users sometimes request for small random rows; however, they

can also request a large number of rows

The expectations are for applications to provide good response to various types of workloads. Optimization of
database servers should be in par with the workloads they can support. Overcomplicating the tuning effort to extract
the most out of the servers may not give sufficient results. Therefore, before looking at resource utilization such as
memory, disk I/O, or upgrading hardware, it's important to ensure that the application is making optimal demands on
the database server. This involves finding and tuning the persistence layer consuming excessive resources. Only after
this layer is tuned should the database or O/S level tuning be considered.

Step 2: Finding and Eliminating Contention
Most applications making requests to the database will perform database I/O or network requests, and in the process
of doing this consumes CPU resources. However, if there is contention for resources within the database, the database
and its resources may not scale well. Most database contention could be determined using Oracle's wait interface by
querying V$SESSION , V$SESSION_WAIT , V$SYSTEM_WAIT , V$EVENT_NAME , and V$STATNAME . High wait events related to
latches and buffers should be minimized. Most wait events in a single instance (non-Real Application Clusters [RAC])
configuration represent contention issues that will be visible in RAC as global events, such as global cache gc buffer
busy . Such issues should be treated as single instance issues and should be fixed before moving the application to a
RAC configuration.