DBA AWR Active Session History – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Database Administration · all Database Administration reports
Overview
Active session history from the automatic workload repository
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
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
v$waitstat, dual, dba_objects, 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 SGA Blocking Session Summary, DBA AWR SQL Performance Summary, DBA Blocking Sessions, DBA SGA+PGA Memory Configuration, DBA AWR System Metrics Summary, DBA ORDS Configuration Validation, DBA AWR Settings
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
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 |
| SQL Source | DBA AWR Active Session History.sql |
| Full Report 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
© Enginatics GmbH