DBA AWR Active Session History – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Overview
Active session history from the automatic workload repository
Report Parameters
User Name, Module Type, Module contains, Show Blocking Session Info, Blocked Sessions only, Request Id, From Time, To Time, Code Name starts with, Entry Procedure contains, Wait Event, Exclude Wait Event, SID - Serial#, SQL Id, Plan Hash Value, Show SQL Text, UI Sessions only, Machine, Session Type, Schema, Action contains, Diagnostic Pack enabled, Container Data
Oracle EBS Tables Used
gv$sqlarea, obj$, dba_hist_active_sess_history, dba_hist_sqltext, dba_procedures, dba_users
Report Categories
Related Reports
DBA AWR Blocking Session Summary, DBA SGA Active Session History, DBA AWR SQL Performance Summary, DBA SGA Blocking Session Summary, DBA SGA+PGA Memory Configuration, DBA AWR System Metrics Summary, DBA AWR Wait Event Summary (active session history), DBA AWR Settings, DBA AWR CPU Load (active session history)
Running This SQL Without Blitz Report
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | DBA AWR Active Session History 11-May-2017 125507.xlsx |
| Blitz Report™ XML Import | DBA_AWR_Active_Session_History.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-awr-active-session-history/ |
Executive Summary
The DBA AWR Active Session History report is a forensic performance analysis tool. It mines the Automatic Workload Repository (AWR) to reconstruct the “Active Session History” (ASH) for a past time period. While real-time ASH shows what is happening now, this report allows DBAs to answer “What happened then?” with second-by-second granularity.
Business Challenge
- Post-Mortem Analysis: “Users reported the system froze yesterday between 2:00 and 2:15 PM. What caused it?”
- Wait Analysis: Identifying the specific bottleneck (CPU, I/O, Locks) that dominated the workload during the incident.
- SQL Identification: Pinpointing the exact SQL ID and execution plan that was consuming resources at that time.
Solution
This report dumps the historical ASH data, allowing for detailed filtering and pivoting.
Key Features:
- Granularity: ASH samples active sessions every second (in memory) and persists a sample (1 in 10) to AWR.
- Dimensions: Can analyze by User, Module, SQL ID, Wait Event, Machine, etc.
- Blocking Info: Shows which session was blocking which other session.
Architecture
The report queries DBA_HIST_ACTIVE_SESS_HISTORY, the persistent storage for ASH data.
Key Tables:
DBA_HIST_ACTIVE_SESS_HISTORY: The core history table.DBA_USERS: Usernames.DBA_HIST_SQLTEXT: SQL text for the captured SQL IDs.
Impact
- Root Cause Analysis: Moves performance tuning from guessing to evidence-based diagnosis.
- SLA Management: Helps explain service interruptions to stakeholders with concrete data.
- Trend Identification: Can be used to spot recurring patterns of contention (e.g., “Every day at 9 AM, we hit this lock”).
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics