DBA SGA Blocking Session Summary – 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
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.
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, 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
Oracle EBS Tables Used
v$waitstat, dual, dba_objects, gv$sqlarea, obj$, gv$active_session_history, dba_users
Report Categories
Related Reports
DBA SGA Active Session History, DBA AWR Blocking Session Summary, DBA AWR Active Session History, DBA Blocking Sessions, DBA ORDS Configuration Validation, DBA AWR SQL Performance Summary, DBA SGA SQL Performance Summary, DBA Sessions
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 SGA Blocking Session Summary 21-Jan-2019 083708.xlsx |
| Blitz Report™ XML Import | DBA_SGA_Blocking_Session_Summary.xml |
| SQL Source | DBA SGA Blocking Session Summary.sql |
| Full Report on Enginatics | www.enginatics.com/reports/dba-sga-blocking-session-summary/ |
Case Study & Technical Analysis
Abstract
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.
Technical Analysis
Core Logic
- 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.
Limitations of ASH for Locking
- 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.
Key View
GV$ACTIVE_SESSION_HISTORY: The in-memory circular buffer of active session samples.
Operational Use Cases
- 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.
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