Understanding & Tuning Oracle Database - Chapter 1

Lets understand the terms and performance problems in Oracle Database and how to tune it effectively.

Topic Covered :
1. Common Performance problems.
2. Available tools to capture DB metrics.
3. Tuning Methodology
4. Tools for tuning
5. Tuning Objectives

Common Performance Problems in an application:

Application Issues : Poorly written SQL, Poor session management, serialized resources, etc..

Instance Issues : Memory, Process, I/O and Instance configs
OS Issues : I/O, SWAP, Parameters settings and Network configs

There is a variety of tools are available to capture the metrics and advice on tuning. All are based on the same methodology as mentioned below.

Tools :

1. Basic Diagnostics Tools :
       Dynamic Performance Views
       Statistics
       Metrics
2. AWR ( Optional tuning packs with Enterprise editions) / Statspack ( free with all the editions)
3. ADDM ( Automatic Database Diagnostics Monitor) (Optional tuning packs - licensed)
4. DBA Scripts - DBA create their own SQL scripts to capture the DB Performance.

All tuning tools are based on the basic diagnostics  tools (Dynamic performance views, statistics and metrics collected by the instance.)

Tuning Methodology :

Follow the top-down approach if the project is in design/development phase. Tune the design -> Tune the application -> Tune the DB instance. During the testing and production phases, tuning is more bottom-up approach.
Ex 1: Eliminate the Full Index scan that causes the I/O contention before looking into the table layout on disk.

Use appropriate data structures and designs -
Ex 2: Reverse key indexes may work well in RAC environment to reduce hot blocks but it may also lead to large number of blocks being transported between the instances if they write to same table.

Identify greatest bottleneck and tune it to get the highest potential benefit out of it. Use DB time to find the most common bottlenecks in the DB. Your aim should to "Reduce the longest waits and largest service times".

Tuning goals are always come from SLA. When the SLA fails, we do look for tuning the potential problem area. However the tuning goals aren't always specific. To start with tuning, we do need following criteria

• Measurable
• Achievable
• Specific
• Cost Effective

• Define the Problem and derive a goal - Define the problem statement and Derive the expectation. Source of Information could be users, stats, metrics and different reports.
• Collect Host & DB Stats - Collect Host & DB Stats for couple of past tests and compare with baseline.
• Look for common performance errors- Check of general errors before jumping to advanced stage and keep digging until you find the cause.
• Build a trial solution -  Build a solution and implement it If successful, Validate the issue and plan for implementing the solution in real environment.

Tools for Tuning ( these tools are discussed in subsequent chapters)

• Time Model
• Top Wait Events
• Alert Logs
• Trace files
• Dynamic Performance Views & Tables
• Statspack

Optionals

• AWR
• ADDM

Tuning Objectives

• Reduce Response Time/ minimize user wait time 
• Increase throughput - Decrease the time to perform a job
• Increase load capabilities- Increase the capacity to handle more concurrent requests
• Reduce Recovery Time - This is useful when the business have high instance availability requirement. There might be thousands to millions of dollar of loss to business for DB instance goes down. Tuning recovery balance out the additional disk writes of rego files.

Continue to Chapter 2