Database Tuning

The Tuning Lifecycle

Database performance tuning is a systematic, iterative process of identifying, analyzing, and resolving performance bottlenecks. The tuning methodology prescribed by Oracle follows a top-down approach: start with high-level system analysis, then drill down to specific components. The lifecycle consists of:

1. Performance Planning: Establish baseline metrics and performance objectives during the design phase.
2. Instance Tuning: Optimize memory structures (SGA, PGA), I/O configuration, and process limits.
3. SQL Tuning: Identify and optimize high-impact SQL statements.
4. Application Tuning: Optimize application design, including connection management, transaction boundaries, and data access patterns.
5. Monitoring and Maintenance: Continuously monitor performance and adjust as workloads evolve.

Proactive vs. Reactive Tuning

Proactive Tuning anticipates performance issues before they impact users. It involves: establishing performance baselines using AWR snapshots, capacity planning and trend analysis, regular review of ADDM findings, implementing best practices for schema design and indexing, and load testing before production deployment.

Reactive Tuning responds to performance problems after they are reported. It involves: analyzing current performance data (ASH, real-time SQL monitoring), diagnosing the root cause of slowdowns, and implementing corrective measures. While reactive tuning is sometimes unavoidable, a mature DBA practice emphasizes proactive tuning to minimize the frequency and severity of reactive incidents.

Instance Tuning

Instance tuning focuses on the memory structures and processes that constitute the Oracle instance. The key areas are:

SGA Tuning: The SGA components must be sized appropriately for the workload. Under-sized buffer cache causes excessive physical I/O (visible as free buffer waits wait events). Under-sized shared pool triggers latch contention on the library cache and excessive hard parsing. The buffer cache hit ratio $\beta = 1 - \frac{\text{physical reads}}{\text{logical reads}}$ is a coarse indicator but should not be the sole metric. Oracle's Automatic Shared Memory Management (ASMM) uses SGA_TARGET to dynamically resize pools based on workload demand.

PGA Tuning: The PGA is critical for sort and hash operations. Under WORKAREA_SIZE_POLICY=AUTO, Oracle distributes PGA_AGGREGATE_TARGET among active work areas. The PGA advisor (V$PGA_TARGET_ADVICE) helps compute the marginal reduction in disk I/O as PGA target increases. The goal is to minimize multipass executions — operations that spill to temporary tablespace multiple times due to insufficient memory.

From an economic perspective, the DBA allocates memory to maximize throughput. The first-order condition requires that the marginal performance product of memory be equalized across pools: $\frac{\partial T}{\partial m_{\text{buf}}} = \frac{\partial T}{\partial m_{\text{sp}}} = \cdots = \lambda$, where $\lambda$ is the shadow price of memory — the incremental throughput obtainable from one additional byte of SGA.

Tuning Activities

Performance Planning: Define performance requirements in terms of response time targets and throughput goals. Size the system appropriately for the expected workload. Plan storage layout to avoid I/O bottlenecks (separate datafiles, redo logs, and archive logs on different physical devices).

Tuning Application Design: Ensure efficient connection management (connection pooling), appropriate use of bind variables to promote cursor sharing and reduce hard parses, and proper transaction design (keep transactions short to minimize lock contention).

Tuning Data Access: Create appropriate indexes for query predicates and join conditions. Use partitioning for large tables to enable partition pruning. Consider materialized views for expensive aggregations. Use index-organized tables (IOTs) for tables primarily accessed by primary key.

Tuning Data Manipulation: Use bulk operations (FORALL, BULK COLLECT) for processing large volumes in PL/SQL. Use direct-path inserts (INSERT /*+ APPEND */) for bulk data loading. Minimize the use of triggers and complex constraints on high-volume tables.

Reducing Network Traffic: Use array processing to fetch multiple rows per round trip. Avoid unnecessary data transfer (select only needed columns). Use stored procedures to encapsulate business logic on the server side. Consider database resident connection pooling for web applications with many short-lived connections.

AWR Architecture

The Automatic Workload Repository (AWR) is a built-in repository that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning. AWR takes snapshots of cumulative instance statistics at regular intervals (default 60 minutes) and stores them in the SYSAUX tablespace. Each snapshot captures:

• Time Model Statistics: DB Time, DB CPU, background CPU time, parse time, PL/SQL execution time, and Java execution time.
• Wait Event Statistics: Time spent waiting for various resources (I/O, locks, latches, network, etc.).
• Session Statistics: Active session counts, logons, commits, rollbacks.
• System Statistics: OS-level metrics such as CPU utilization, I/O rates, and memory usage.
• SQL Statistics: Elapsed time, CPU time, buffer gets, disk reads, executions, and rows processed for top SQL statements.

AWR snapshots are retained based on the RETENTION period (default 8 days for Enterprise Edition). The MMON (Memory Monitor) background process is responsible for taking snapshots and purging expired data.

Automatic Database Diagnostic Monitor (ADDM)

ADDM is a rules-based diagnostic engine that automatically analyzes AWR snapshots to identify performance bottlenecks. ADDM runs automatically after each AWR snapshot and whenever requested by the DBA. It decomposes Database Time (DB Time) — the total time spent by foreground sessions working on user calls — into components:

$$DBTime = \sum_{i=1}^{n} \left( CPU_i + \sum_{j} Wait_{ij} \right)$$

where $i$ indexes active sessions and $j$ enumerates wait event classes. ADDM then partitions DB Time across SQL statements, wait classes, and instance resources, identifying bottlenecks and providing actionable recommendations.

ADDM findings are organized into categories: SQL statements consuming significant DB Time, CPU bottlenecks, undersized memory structures (buffer cache, shared pool, PGA), I/O subsystem bottlenecks, network latency issues, lock contention, and configuration issues (e.g., improper parameter settings).

-- Generate an ADDM report
@?/rdbms/admin/addmrpt.sql

-- Or query ADDM findings programmatically
SELECT finding_name, impact, recommendations
FROM DBA_ADVISOR_FINDINGS
WHERE task_name LIKE 'ADDM%'
ORDER BY impact DESC;

AWR Reports

An AWR report provides a comprehensive snapshot of database performance over a specified time interval. It is generated using the awrrpt.sql script and includes: load profile (transactions per second, logical/physical reads), instance efficiency percentages (buffer cache hit ratio, library cache hit ratio), top 5 timed events, wait class breakdown, SQL statistics by elapsed time, CPU, and I/O, and advisory sections (SGA, PGA, buffer cache, shared pool sizing).

-- Generate an AWR report
@?/rdbms/admin/awrrpt.sql