Steps in The Oracle Performance Improvement Method

1. Perform the following initial standard checks:

a. Get candid feedback from users. Determine the performance project’s scope

and subsequent performance goals, as well as performance goals for the

future. This process is key in future capacity planning.

b. Get a full set of operating system, database, and application statistics from the

system when the performance is both good and bad. If these are not available,

then get whatever is available. Missing statistics are analogous to missing

evidence at a crime scene: They make detectives work harder and it is more

time-consuming.

c. Sanity-check the operating systems of all systems involved with user

performance. By sanity-checking the operating system, you look for hardware

or operating system resources that are fully utilized. List any over-used

resources as symptoms for analysis later. In addition, check that all hardware

shows no errors or diagnostics.

The Oracle Performance Improvement Method

Performance Improvement Methods 3-3

2. Check for the top ten most common mistakes with Oracle, and determine if any of

these are likely to be the problem. List these as symptoms for later analysis. These

are included because they represent the most likely problems. ADDM

automatically detects and reports nine of these top ten issues. See Chapter 6,

"Automatic Performance Diagnostics" and "Top Ten Mistakes Found in Oracle

Systems" on page 3-4.

3. Build a conceptual model of what is happening on the system using the symptoms

as clues to understand what caused the performance problems. See "A Sample

Decision Process for Performance Conceptual Modeling" on page 3-3.

4. Propose a series of remedy actions and the anticipated behavior to the system,

then apply them in the order that can benefit the application the most. ADDM

produces recommendations each with an expected benefit. A golden rule in

performance work is that you only change one thing at a time and then measure

the differences. Unfortunately, system downtime requirements might prohibit

such a rigorous investigation method. If multiple changes are applied at the same

time, then try to ensure that they are isolated so that the effects of each change can

be independently validated.

5. Validate that the changes made have had the desired effect, and see if the user's

perception of performance has improved. Otherwise, look for more bottlenecks,

and continue refining the conceptual model until your understanding of the

application becomes more accurate.

6. Repeat the last three steps until performance goals are met or become impossible

due to other constraints.

This method identifies the biggest bottleneck and uses an objective approach to

performance improvement. The focus is on making large performance improvements

by increasing application efficiency and eliminating resource shortages and

bottlenecks. In this process, it is anticipated that minimal (less than 10%) performance

gains are made from instance tuning, and large gains (100% +) are made from isolating

application inefficiencies.