Oracle Performance Engineering Guide: AWR, ASH and SQL Monitor in 19c and 23ai

Oracle Performance Engineering Guide: AWR, ASH and SQL Monitor in Oracle 19c and 23ai

Real Production Troubleshooting Using Oracle Performance Diagnostic Tools

 March 2026

 Chetan Yadav — Senior Oracle and Cloud DBA

⏱️ 12–14 min read

⏱️ Estimated Reading Time: 12–14 minutes

Master AWR, ASH, and SQL Monitor — The Oracle DBA’s Performance Troubleshooting Trinity

Environment Used in This Example

The troubleshooting approach described in this article reflects common production environments such as:

Oracle Database 19c  •  2-Node RAC Cluster  •  8 TB OLTP Database  •  ~2,500 Concurrent Sessions  •  Peak ~1,500 TPS

While the SQL queries shown are simplified for educational purposes, the investigation methodology mirrors real production troubleshooting practices used by experienced DBAs.

During a recent performance investigation, an OLTP database began experiencing significant latency during peak traffic. Average response time increased from roughly 300 milliseconds to more than 5 seconds.

Application teams initially suspected CPU saturation. Infrastructure teams suspected storage latency. Situations like this are common during performance incidents where multiple teams wait for the DBA team to identify the root cause.

The investigation followed a common Oracle DBA diagnostic workflow: identify workload patterns using AWR, analyze session-level activity using ASH, and investigate problematic SQL execution using SQL Monitor. These three tools form the core performance troubleshooting toolkit used by Oracle DBAs in production environments.

Read more »

 Table of Contents

1. The Oracle Performance Troubleshooting Trinity
2. AWR Fundamentals: What It Measures and Why It Matters
3. Reading AWR Reports: Focus on These Sections First
4. ASH Deep Dive: Real-Time Session Analysis
5. SQL Monitor: Real-Time Query Execution Visibility
6. Example Production Investigation
7. Troubleshooting Scenario: CPU Bottleneck
8. Troubleshooting Scenario: Lock Contention
9. DBA Checklist During Performance Incidents
10. Key Takeaways

1. The Oracle Performance Troubleshooting Trinity

Tool	Purpose
AWR	Historical workload analysis
ASH	Session-level activity sampling
SQL Monitor	Real-time SQL execution visibility

Using these tools together allows DBAs to move efficiently from symptom to root cause.

Oracle Performance Troubleshooting Workflow

1. Identify workload changes — Review AWR load profile and wait events to determine whether the issue is caused by increased workload or resource contention.
2. Identify dominant wait events — Examine the Top Timed Events section in the AWR report.
3. Identify problematic SQL — Review SQL ordered by elapsed time to identify expensive queries.
4. Investigate session behavior — Use ASH to identify blocking sessions, hot objects, or concurrency problems.
5. Analyze execution plans — Use SQL Monitor or execution plan analysis to understand query behavior.
6. Implement corrective action — Possible fixes include index creation, SQL tuning, statistics refresh, or application-level adjustments.

2. AWR Fundamentals: What It Measures and Why It Matters

The Automatic Workload Repository (AWR) stores historical database performance metrics captured at regular intervals. By default Oracle creates snapshots every 60 minutes, storing metrics such as wait events, SQL execution statistics, system statistics, memory utilization, and I/O activity. These snapshots allow DBAs to analyze workload trends over time.

What AWR Captures

Metric Category	Description	Why It Matters
Wait Events	Time spent waiting for resources	Identifies performance bottlenecks
Top SQL	SQL statements consuming most resources	Prioritize tuning efforts
System Statistics	Database-level metrics	Understand workload behavior
Memory Usage	SGA and PGA statistics	Detect memory pressure
I/O Statistics	Datafile read and write activity	Identify storage issues

Generating an AWR Report

SQL — Generate AWR Report

@?/rdbms/admin/awrrpt.sql -- Retrieve snapshot IDs SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC;

Adjusting Snapshot Retention

SQL — Configure AWR Snapshot Settings

SELECT snap_interval, retention FROM dba_hist_wr_control; -- Modify snapshot settings (30 days retention, 30 min interval) BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30 ); END; /

Licensing Note

AWR, ASH, and SQL Monitor are part of the Oracle Diagnostics Pack. Using these features requires a Diagnostics Pack license: AWR reports, ASH queries, DBA_HIST views, SQL Monitor reports, and ADDM analysis. If Diagnostics Pack is not licensed, DBAs should instead rely on Statspack, V$SESSION, V$SYSTEM_EVENT, or V$SQL. Always verify licensing compliance before using these features in production environments.

3. Reading AWR Reports: Focus on These Sections First

Although AWR reports can exceed 80 pages, the most important sections include: Top Timed Foreground Events, SQL Ordered by Elapsed Time, Load Profile, and Instance Efficiency Metrics.

Top Timed Foreground Events

This is the most important section. It shows where your database spent the most time. High values for db file sequential read often indicate index-driven workloads, inefficient SQL access patterns, storage latency, or missing indexes.

Figure 1: AWR Top Timed Foreground Events

62% of DB time in db file sequential read confirms an I/O-bound workload. This diagnostic was captured during a real production performance investigation on a high-concurrency OLTP system.

Identifying Expensive SQL

SQL — Extract Top SQL from AWR

SELECT sql_id, plan_hash_value, executions_delta AS executions, ROUND(elapsed_time_delta/1000000,2) AS elapsed_seconds, buffer_gets_delta, disk_reads_delta FROM dba_hist_sqlstat WHERE snap_id BETWEEN :begin_snap AND :end_snap ORDER BY elapsed_time_delta DESC FETCH FIRST 20 ROWS ONLY;

4. ASH Deep Dive: Real-Time Session Analysis

Active Session History (ASH) samples active sessions roughly once per second. Each sample records information such as the SQL ID, session state, wait event, blocking session, and object being accessed.

ASH helps answer critical questions during performance incidents: which sessions are waiting, which SQL statements are responsible, and which objects are involved.

When to Use ASH vs AWR

Use AWR when analyzing performance over hours or days, finding trends and patterns, or comparing current performance to a historical baseline.

Use ASH when troubleshooting active performance issues in real time, identifying which sessions cause specific wait events, or finding contention on specific objects.

Real-Time Session Analysis

SQL — ASH: What’s Running Right Now

SELECT session_id, session_serial#, sql_id, event, wait_class, blocking_session FROM v$active_session_history WHERE sample_time > SYSDATE - INTERVAL '15' MINUTE ORDER BY sample_time DESC;

Advanced ASH Blocking Analysis

SQL — ASH: Identify Blocking Sessions

SELECT blocking_session, COUNT(*) waiting_sessions FROM v$active_session_history WHERE blocking_session IS NOT NULL GROUP BY blocking_session ORDER BY waiting_sessions DESC;

This query quickly identifies sessions responsible for widespread blocking across the database.

Figure 2: ASH Active Session Blocking Chain

SID 892 identified as the root blocking session holding a TX lock, causing 47 sessions to wait. ASH session sampling is particularly valuable for diagnosing real-time database contention.

5. SQL Monitor: Real-Time Query Execution Visibility

SQL Monitor provides real-time execution statistics for SQL statements. Unlike EXPLAIN PLAN, SQL Monitor displays actual row counts, operation-level wait events, execution progress, and parallel query distribution.

What Makes SQL Monitor Different

Feature	EXPLAIN PLAN	SQL Monitor
Shows predicted plan	Yes	Yes
Shows actual execution	No	Yes
Actual row counts	No (estimated)	Yes (actual)
Wait events per operation	No	Yes
Real-time progress	No	Yes

Viewing Monitored SQL

SQL — List Monitored SQL Statements

SELECT sql_id, status, username, elapsed_time/1000000 AS elapsed_seconds FROM v$sql_monitor ORDER BY sql_exec_start DESC;

Generate SQL Monitor Report

SQL — Generate SQL Monitor Report (HTML)

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id => 'your_sql_id', type => 'HTML', report_level => 'ALL' ) FROM dual;

Interpreting SQL Monitor Output

SQL Monitor reports often reveal execution plan issues immediately. A cardinality mismatch — where the optimizer estimates 1,000 rows but 10 million are actually processed — can lead to incorrect join methods, excessive buffer gets, full table scans, and high CPU usage. Such discrepancies are a common root cause of performance degradation in OLTP environments.

Figure 3: SQL Monitor — Real-Time Execution Plan Analysis

Cardinality mismatch detected: optimizer estimated 1,000 rows but 10,842,201 rows were actually processed. Stale statistics caused a HASH JOIN with TABLE ACCESS FULL, resulting in 127 seconds of elapsed time.

6. Example Production Investigation

In one investigation, an AWR report showed log file sync contributing to nearly 40% of database wait time. ASH analysis revealed hundreds of sessions waiting on commit operations.

Further investigation identified an application batch process committing after every single row instead of batching transactions. After adjusting the commit strategy in the application layer, commit frequency dropped significantly and the database response time returned to normal levels.

This example illustrates how combining AWR historical analysis with ASH session sampling helps identify the true root cause of performance problems.

7. Troubleshooting Scenario: CPU Bottleneck

Symptom: Users report application slowness. AWR report shows 80% of database time is CPU.

Common causes of high CPU:

• Issue Inefficient SQL execution plans — full table scans on large tables
• Issue Excessive hard parsing — no bind variables in application SQL
• Issue Application connection storms — too many sessions opening simultaneously
• Issue Expensive PL/SQL function calls in SQL WHERE clauses

SQL — Top SQL by CPU Time

SELECT sql_id, executions_delta, ROUND(cpu_time_delta/1000000,2) AS cpu_seconds FROM dba_hist_sqlstat ORDER BY cpu_time_delta DESC FETCH FIRST 20 ROWS ONLY;

Quick wins:

• Fix Add missing indexes to eliminate full table scans
• Fix Convert literals to bind variables to reduce hard parsing
• Fix Implement connection pooling to reduce session overhead
• Fix Rewrite SQL to avoid expensive functions in WHERE clauses

8. Troubleshooting Scenario: Lock Contention

Symptom: Users report "hanging" transactions. AWR shows high enq: TX - row lock contention wait time.

SQL — Current Lock Contention

-- Currently blocked sessions SELECT blocking_session, sid, serial#, username, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL; -- Historical lock analysis with ASH (last 2 hours) SELECT sql_id, blocking_session, COUNT(*) AS lock_samples, ROUND(COUNT(*) * 1, 2) AS approx_wait_seconds, MIN(sample_time) AS first_seen, MAX(sample_time) AS last_seen FROM dba_hist_active_sess_history WHERE sample_time > SYSDATE - INTERVAL '2' HOUR AND event LIKE '%enq: TX%' AND blocking_session IS NOT NULL GROUP BY sql_id, blocking_session ORDER BY lock_samples DESC;

9. DBA Checklist During Performance Incidents

Database Layer

• Check active session count
• Identify dominant wait events via AWR Top Timed Events
• Review top SQL by elapsed time

SQL Layer

• Examine execution plans with SQL Monitor
• Verify statistics freshness
• Check for unexpected full table scans

Infrastructure Layer

• Monitor OS CPU usage from AWR OS Stats
• Check I/O latency per datafile
• Validate storage performance

Application Layer

• Connection pool saturation
• Inefficient or unparameterized queries
• Sudden workload spikes or batch jobs

10. Key Takeaways

Effective Oracle performance troubleshooting requires a structured diagnostic approach.

• AWR Use to understand workload patterns and trends over time.
• ASH Use to analyze session-level activity and identify blocking chains.
• SQL Monitor Use to investigate SQL execution behavior in real time.
• Combined Combining these tools allows DBAs to resolve performance incidents efficiently.
• License Always verify Diagnostics Pack licensing before using AWR/ASH in production.

About the Author

Chetan Yadav

Chetan Yadav is a Senior Oracle, PostgreSQL, MySQL, and Cloud DBA with 15+ years of hands-on experience managing production databases across on-premises, hybrid, and cloud environments.

His expertise includes Oracle performance tuning, high availability architecture, database migrations, disaster recovery, and cloud database platforms. He regularly shares practical DBA insights based on real production troubleshooting experience.

This blog focuses on real-world DBA problems, career growth, and practical learning — not theoretical documentation or vendor marketing.

LinkedIn YouTube Instagram Twitter (X) Facebook Newsletter

Related Reading from Real Production Systems

If you want to deepen your understanding of Oracle performance tuning and diagnostics, the following articles may also be useful:

• Oracle Database 23ai: Revolutionizing Data Distribution Across the Globe Why it matters: Understanding Oracle 23ai’s built-in performance enhancements helps interpret AWR reports showing improvements in distributed query performance and automatic index management.
• N8N Oracle Telegram Alerts: Automating Database Monitoring Why it matters: After identifying performance issues with AWR/ASH, automate alerting when specific wait events or SQL patterns recur. This guide shows how to monitor AWR metrics continuously.
• GCP AlloyDB Performance Tuning Guide: Oracle 19c Comparison for DBAs Why it matters: If you’re considering migrating from Oracle, understanding how AlloyDB’s performance monitoring compares to AWR/ASH helps evaluate whether cloud alternatives provide equivalent diagnostic capabilities.
• Automating Backup and Restore in Oracle 19c and 23ai: Complete DBA Guide Why it matters: Performance issues often emerge after schema changes or data loads. AWR comparison before/after backup operations helps identify performance regressions from maintenance activities.

Oracle Database 26ai: New Features DBAs Should Test First in 2026

Oracle Database 26ai: New Features DBAs Should Test First

AI-Powered Automation, Vector Search, and Production-Critical Features

📅 February 26, 2026

👤 Chetan Yadav - Senior Oracle & Cloud DBA

⏱️ 12-13 min read

⏱️ Estimated Reading Time: 12–13 minutes

🤖 Oracle 26ai - The Database That Tunes Itself While You Sleep

The Oracle Database 26ai beta went live last week. My manager sent the email: "Download it. Test it. Report back on Monday with what breaks."

I spent 72 hours testing every major feature Oracle advertised. Some lived up to the hype. Others... didn't. Here's what you actually need to test first, based on what will impact production databases most.

Oracle 26ai isn't just another version bump with minor bug fixes. This release introduces AI-driven query optimization, native vector search for machine learning workloads, property graph enhancements, and automated schema evolution. If your organization is considering adopting any of these features, this guide shows you exactly what to test and what production gotchas to watch for.

This isn't marketing hype—it's a DBA's practical testing roadmap based on early access to 26ai. If you're evaluating whether to upgrade, these are the features that matter most and the tests that separate real value from vendor promises.

⏱️ Reading Time: 10–12 minutes

Top-10 Wait Events Query (Universal Database Performance Tuning)

It’s a peak business hour. Users complain the application is “slow,” dashboards look normal, CPU is not maxed out, and storage graphs look fine. Someone asks the classic question:

“The database is up… so why is everything waiting?”

This is the exact moment where strong DBAs look at wait events instead of guessing. Understanding the top-10 wait events is not just a tuning skill —it’s a career-defining mindset.