Oracle Database Performance Tuning - 5 Steps to Resolve Issues

Database performance problems usually fall into two buckets: system-wide bottlenecks (CPU, memory, network, processes) and query-specific slowdowns driven by expensive SQL and poor optimization. The practical takeaway is to narrow the root cause by separating “general resource pressure” from “workload or SQL behavior,” then work through fixes in a repeatable sequence—because tuning often moves the bottleneck rather than eliminating it.

A five-step workflow centers on Oracle’s Autonomous Health Framework (AHF), a diagnostic toolset available at no extra cost under existing software support. First, AHF generates a database performance diagnostic collection on the affected host. Using the AHF diagnostic data collection capability, an Oracle user runs a command (tfactl di collect …) to collect problem-specific diagnostics. AHF then packages results into a zip file per machine, typically enough to use the zip from the host where the collection was initiated.

Second, AHF Insights turns that collection into a “bird’s-eye view” of configuration drift, anomalies, and resource bottlenecks. The best-practice checks highlight where the environment deviates from Oracle recommendations, including severity and repair steps tied to each drift item. Next, the anomalies advisor uses AI-driven detection to flag unusual events—such as signs that the database and host I/O are slow. From there, the workflow drills into operating system metrics to pinpoint bottlenecks. In the example, Oracle processors appear blocked in DSTATE, indicating they are waiting on I/O return, while disk wait times and problematic snapshots reveal where latency spikes occurred. A separate anomaly example shows elevated “Global cache messages,” with the follow-on finding that CPU is the bottleneck; CPU usage trends and process-level breakdowns then show background and foreground database activity consuming large shares of CPU.

Third, noisy neighbors are addressed when the same host runs multiple databases and CPU is frequently saturated. AHF can detect noisy database neighbors using historical CPU data sourced from the Oracle Enterprise Manager repository. After configuring AHF to connect to the EM repository, a cluster analysis report (via AHF balance) compares current noisy-neighbor exposure against what it would look like with recommended CPU counts. The report highlights time periods where databases were impacted, enabling targeted CPU-count adjustments per database or machine.

Fourth, the focus shifts from infrastructure to workload tuning. Using the same diagnostic collection, the workflow opens a Performance Hub report (ASH-based active session history by default) to identify what sessions are doing. Weight-class breakdowns can show whether the system is CPU-bound, and SQL IDs can be surfaced as tuning candidates. The ADDM tab (Automatic Database Diagnostic Monitor) then analyzes AWR data and provides findings with recommended changes and estimated benefit, helping prioritize long-running SQL statements for deeper tuning.

Finally, if issues persist, the workflow ends with escalation: log a technical service request with Oracle Support using the AHF diagnostic collection. Selecting an Express service request option streamlines FastTrack handling, with Oracle Support taking over after the collection is uploaded. The overall message is iterative: fix one bottleneck, re-check, and repeat until the system stabilizes.