DBA Hub

📋Steps in this guide1/19

ASH & AWR: How Oracle Diagnoses Performance Bottlenecks

Learn how Oracle ASH & AWR help diagnose database performance bottlenecks. Understand ASH samples, AWR snapshots, reports, and real-world DBA use cases.

oracle configurationintermediate
by OracleDba
41 views
1

Introduction

In today’s always-on enterprise systems, database performance issues can directly impact business operations, user experience, and revenue. Oracle databases are designed with powerful built-in diagnostic tools that help DBAs quickly identify, analyze, and resolve performance bottlenecks. Two of the most important tools in Oracle’s performance diagnostics arsenal are Active Session History (ASH) and Automatic Workload Repository (AWR) . ASH and AWR work together to provide both real-time and historical insights into database activity. While ASH focuses on what is happening right now at a very granular level, AWR provides a summarized, long-term view of database workload and performance trends.
2

Understanding Performance Bottlenecks in Oracle

A performance bottleneck occurs when a database resource becomes overloaded and limits overall system performance. Common bottlenecks include: - CPU saturation - I/O latency (disk or ASM) - Memory pressure (SGA or PGA) - Locking and contention - Inefficient SQL execution plans Oracle continuously collects performance statistics in the background so that DBAs can analyze these issues after they occur—this is where ASH and AWR come into play.
3

What is Active Session History (ASH)?

Active Session History (ASH) is a lightweight, in-memory sampling mechanism that captures information about active database sessions every second.
4

Key Characteristics of ASH

- Samples active sessions only (not idle sessions) - Captured approximately once per second - Stored in memory in the SGA - Provides near real-time performance visibility ASH answers the question: > “What was my database waiting on at a specific point in time?” “What was my database waiting on at a specific point in time?”
5

What Information Does ASH Capture?

Each ASH sample records details such as: - Session ID and Serial# - SQL ID and SQL Plan Hash Value - Wait event and wait class - Blocking session information - Object ID (table, index, etc.) - CPU or wait state This data allows DBAs to drill down to the exact sessions and SQL statements responsible for performance problems.
6

How ASH Helps Diagnose Performance Issues

ASH is especially useful for diagnosing short-lived or intermittent issues that traditional reports might miss.
7

Common Use Cases for ASH

- Identifying top wait events in real time - Detecting blocking sessions and lock contention - Analyzing CPU spikes - Troubleshooting sudden performance slowdowns - Pinpointing problematic SQL statements
8

Example Scenario

If users complain that the application was slow for 5 minutes, ASH can show: - Which sessions were active - What they were waiting on (CPU, I/O, locks) - Which SQL statements were running during that time
9

What is Automatic Workload Repository (AWR)?

Automatic Workload Repository (AWR) is a built-in Oracle repository that stores historical performance data over time.
10

Key Characteristics of AWR

- Takes snapshots by default every 60 minutes - Retains data for 8 days (configurable) - Stores data persistently in the SYSAUX tablespace - Aggregates and summarizes performance statistics AWR answers the question: > “How has my database been performing over time?” “How has my database been performing over time?”
11

What Data Does AWR Collect?

AWR snapshots include a wide range of performance metrics, such as: - Top SQL statements by CPU, I/O, and elapsed time - Wait events and wait classes - System resource usage (CPU, memory, I/O) - Instance efficiency percentages - Load profile and throughput - Segment-level statistics This historical data is invaluable for trend analysis and capacity planning.
12

AWR Reports: The DBA’s Go-To Tool

The most common way to analyze AWR data is through an AWR report , which compares two snapshots and highlights performance differences.
13

Key Sections of an AWR Report

1. Load Profile Shows per-second activity such as: - DB Time - Logical reads - Physical reads - Executes 2. Top Wait Events Identifies where the database spent most of its time: - CPU - User I/O - System I/O - Concurrency 3. SQL Statistics Lists top SQL statements by: - Elapsed time - CPU time - Buffer gets - Disk reads 4. Instance Efficiency Helps assess overall database health and tuning effectiveness.
14

How ASH and AWR Work Together

ASH and AWR are not competing tools—they complement each other.
15

Relationship Between ASH and AWR

- ASH data is periodically flushed into AWR - AWR uses ASH samples to generate ASH reports - ASH reports provide detailed session-level insights for a specific time range
16

ASH Reports

An ASH Report is generated from AWR data but focuses on session activity rather than aggregated statistics.
17

When to Use ASH Reports

- When performance issues occur for a short duration - When you need session-level visibility - When identifying blocking chains or hot objects ASH reports bridge the gap between real-time ASH views and high-level AWR summaries.
18

Licensing Considerations

It’s important to note that: - ASH and AWR are part of the Oracle Diagnostics Pack - Using them requires a valid license in production environments - Always verify compliance before enabling or using these features
19

Best Practices for Using ASH and AWR

- Capture AWR snapshots during known performance issues - Compare good vs bad performance periods - Focus on wait classes before tuning SQL - Validate findings with execution plans and statistics - Avoid over-tuning based on a single report

Comments (0)

Please to add comments

No comments yet. Be the first to comment!