| layout | default |
|---|---|
| title | DBA AWR Blocking Session Summary | Oracle EBS SQL Report |
| description | Summary of blocked and blocking sessions based on the active session history from the AWR. The link to blocking sessions is deliberately nonunique without… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Diagnostic Pack, Enginatics, DBA, AWR, Blocking, Session, v$waitstat, dual, dba_objects |
| permalink | /DBA%20AWR%20Blocking%20Session%20Summary/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Summary of blocked and blocking sessions based on the active session history from the AWR. The link to blocking sessions is deliberately nonunique without jointing to sample_id to increase the chance to fetch the blocking session's additional information such as module, action and client_id. In some cases, such as row lock scenarios, the blocking session is idle and does not show up in the ASH.
We recommend doing further analysis with a pivot in Excel rather than by SQL, as Excel's drag- and drop is a lot faster than typing commands manually.
User Name, Module Type, Module contains, From Time, To Time, Wait Event, SID - Serial#, SQL Id, UI Sessions only, Session Type, Schema, Action contains, Diagnostic Pack enabled, Container Data
v$waitstat, dual, dba_objects, gv$sqlarea, obj$, dba_hist_snapshot, dba_hist_active_sess_history, dba_users, y
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.
| Resource | Link |
|---|---|
| Excel Example Output | DBA AWR Blocking Session Summary 21-Jan-2019 095329.xlsx |
| Blitz Report™ XML Import | DBA_AWR_Blocking_Session_Summary.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-awr-blocking-session-summary/ |
The DBA AWR Blocking Session Summary report focuses specifically on database locking and concurrency issues. It aggregates data from the AWR Active Session History to identify "Blocking Chains"—situations where one session holds a resource (like a row lock) that prevents other sessions from proceeding. This is often the cause of "application hangs."
- Lock Storms: A single user leaving a record open on their screen can block a batch job, which then blocks other users, creating a cascade of hung sessions.
- Code Defects: Identifying application code that holds locks longer than necessary or commits infrequently.
- Idle Blockers: Detecting sessions that are "Idle" (not consuming CPU) but are still holding locks (e.g., "SQL*Net message from client").
This report summarizes the blocking relationships found in the history.
Key Features:
- Blocker Identification: Identifies the "Root Blocker"—the session at the top of the chain.
- Wait Event Analysis: Shows what the blocked sessions were waiting for (e.g., "enq: TX - row lock contention").
- Impact Assessment: Shows how many sessions were blocked and for how long.
The report analyzes DBA_HIST_ACTIVE_SESS_HISTORY to find rows where BLOCKING_SESSION is populated.
Key Tables:
DBA_HIST_ACTIVE_SESS_HISTORY: Source of blocking info.DBA_OBJECTS: Identifies the object being locked (if available).DBA_USERS: Identifies the users involved.
- Application Stability: Helps developers fix code that causes concurrency bottlenecks.
- Operational Efficiency: Reduces the time required to diagnose "system hang" incidents.
- User Education: Can be used to show users the impact of leaving transactions uncommitted.
- 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