| layout | default |
|---|---|
| title | DBA SGA Active Session History | Oracle EBS SQL Report |
| description | Active session history from the SGA. Parameter 'Blocked Sessions only' allows a blocking scenario root cause analysis, e.g. by doing a pivot in Excel (see… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Diagnostic Pack, Enginatics, DBA, SGA, Active, Session, gv$sqlarea, obj$, gv$active_session_history |
| permalink | /DBA%20SGA%20Active%20Session%20History/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Active session history from the SGA.
Parameter 'Blocked Sessions only' allows a blocking scenario root cause analysis, e.g. by doing a pivot in Excel (see example in the online library). 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 then. For scenarios where the blocking sessions are active and part of the ASH, Tanel Poder has a treewalk ash_wait_chains.sql, showing the whole chain of ASH records linked by a unique join, including the sample_id: https://blog.tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/
We recommend doing ASH performance analysis with a pivot in Excel rather than by SQL, as Excel's drag- and drop is a lot faster than typing commands manually. A typical use case is to analyze which EBS application user waited for how many seconds in which forms UI and to drill down further into the SQL or wait event root causes.
Column 'Module Name' shows either the translated EBS module display name or, e.g. for background sessions, the process name (from the program column) to enable pivoting by this column only. Oracle's background process names are listed here: https://docs.oracle.com/database/121/REFRN/GUID-86184690-5531-405F-AA05-BB935F57B76D.htm#REFRN104
To identify an object or segment of a hot block, for example causing wait events, Oracle's dba_extents view is unusable due to performance bugs in 19c+, and Franck Pachot published a workaround here: https://www.dbi-services.com/blog/efficiently-query-dba_extents-for-file_id-block_id/
User Name, Module Type, Module contains, Show Blocking Session Info, Blocked Sessions only, Request Id, Request Id (multi session), Restrict User Name to Client_ID, From Time, To Time, Code Name starts with, Entry Procedure contains, Wait Event, Exclude Wait Event, SID - Serial#, SQL Id, Max Temp Space larger than GB, Show SQL Text, UI Sessions only, Session Type, Instance Id, Machine, Schema, Action contains, Program contains, Diagnostic Pack enabled
gv$sqlarea, obj$, gv$active_session_history, dba_procedures, dba_users
DBA AWR Active Session History, DBA AWR Blocking Session Summary, DBA SGA Blocking Session Summary, DBA Blocking Sessions, DBA AWR SQL Performance Summary, DBA ORDS Configuration Validation, DBA Blitz Report ORDS Configuration, DBA SGA SQL Performance Summary, DBA AWR System Metrics Summary
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 Active Session History 24-Sep-2018 011658.xlsx |
| Blitz Report™ XML Import | DBA_SGA_Active_Session_History.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-sga-active-session-history/ |
The DBA SGA Active Session History report is the primary tool for real-time and near-real-time performance diagnostics in Oracle Database. Unlike AWR reports which provide hourly snapshots persisted to disk, this report queries GV$ACTIVE_SESSION_HISTORY directly from the System Global Area (SGA). This allows for second-by-second analysis of database activity, wait events, and resource consumption for the immediate past (typically the last few hours, depending on buffer size and activity levels).
- Sampling: The database samples the state of all active sessions once per second.
- In-Memory: Data is read from the circular ASH buffer in the SGA. Once the buffer is full, older data is overwritten (and 1 in 10 samples are flushed to AWR on disk).
- Pivot Analysis: The report is designed to be exported to Excel, where users can pivot by
SAMPLE_TIME,WAIT_CLASS,EVENT,SQL_ID, orMODULEto isolate performance bottlenecks.
The report includes logic to link blocked sessions to their blockers.
- Limitation: If a blocking session is "Idle" (e.g., a user updated a row but hasn't committed and went to lunch), it will not appear in the ASH data as an active session, even though it is holding a lock.
- Resolution: The report attempts to provide context, but for complex lock chains, specialized scripts (like Tanel Poder's
ash_wait_chains) or theDBA Blocking Sessionsreport (which queriesV$SESSIONdirectly) might be needed.
GV$ACTIVE_SESSION_HISTORY: The source of truth for recent activity.GV$SQLAREA: Joins to provide SQL text and execution stats.DBA_USERS/DBA_PROCEDURES: Resolves IDs to human-readable names.
- "It's slow right now": Diagnosing current performance spikes.
- Root Cause Analysis: Determining who was holding the lock that caused a pile-up 10 minutes ago.
- Application Profiling: Filtering by
MODULE(e.g., 'eBusiness Suite') to see where the application spends its time.
- 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