DBA SGA Active Session History – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

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/

Report Parameters

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

Oracle EBS Tables Used

gv$sqlarea, obj$, gv$active_session_history, dba_procedures, dba_users

Report Categories

Diagnostic Pack, Enginatics

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

Running This SQL Without Blitz Report

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.

Download & Import Options

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/

Case Study & Technical Analysis

Abstract

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).

Technical Analysis

Core Methodology

Blocking Session Analysis

The report includes logic to link blocked sessions to their blockers.

Key Views

Operational Use Cases

© 2026 Enginatics