| layout | default |
|---|---|
| title | DBA SGA Blocking Session Summary | Oracle EBS SQL Report |
| description | Summary of blocked and blocking sessions based on the active session history from the SGA. The link to blocking sessions is deliberately nonunique without… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Diagnostic Pack, Enginatics, DBA, SGA, Blocking, Session, v$waitstat, dual, dba_objects |
| permalink | /DBA%20SGA%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 SGA. 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, Instance Id, Schema, Action contains, Program contains, Diagnostic Pack enabled
v$waitstat, dual, dba_objects, gv$sqlarea, obj$, gv$active_session_history, dba_users, y
DBA SGA Active Session History, DBA AWR Blocking Session Summary, DBA AWR Active Session History, CAC Inventory Accounts Setup
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 SGA Blocking Session Summary 21-Jan-2019 083708.xlsx |
| Blitz Report™ XML Import | DBA_SGA_Blocking_Session_Summary.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-sga-blocking-session-summary/ |
The DBA SGA Blocking Session Summary report aggregates data from the Active Session History (ASH) in the SGA to provide a retrospective view of locking and contention. While the standard ASH report shows all activity, this report filters and summarizes specifically for blocking scenarios, helping DBAs understand the impact and duration of lock chains that occurred recently.
- Source: Queries
GV$ACTIVE_SESSION_HISTORYwhereBLOCKING_SESSIONis not null. - Aggregation: Groups by the blocking session's attributes to show which session/user was the "root blocker" and how much time other sessions spent waiting behind them.
- Wait Events: Typically focuses on
enq: TX - row lock contention,library cache lock, orbuffer busy waits.
- Sampling Bias: Short locks (< 1 second) might be missed by the 1-second sampler.
- Idle Blockers: As noted in the ASH analysis, if the blocker is idle, it won't be in ASH. This report is best for finding "active" blockers—e.g., a batch job that is running slow SQL and holding locks that block online users.
GV$ACTIVE_SESSION_HISTORY: The in-memory circular buffer of active session samples.
- Post-Mortem: Analyzing a "system hang" that cleared up before a DBA could look at it.
- Pattern Recognition: Identifying if a specific scheduled job consistently blocks users at 2:00 PM every day.
- Concurrency Tuning: Identifying "hot" records or tables that are subject to frequent contention.
- 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