DBA AWR 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 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.

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, Schema, Action contains, Diagnostic Pack enabled, Container Data

Oracle EBS Tables Used

v$waitstat, dual, dba_objects, gv$sqlarea, obj$, dba_hist_snapshot, dba_hist_active_sess_history, dba_users

Report Categories

Enginatics

DBA AWR Active Session History, DBA SGA Active Session History, DBA SGA Blocking Session Summary, DBA AWR SQL Performance Summary, DBA AWR System Metrics Summary, DBA AWR Settings, DBA Blocking Sessions, DBA SGA+PGA Memory Configuration, DBA AWR Wait Event Summary (active session history)

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 AWR Blocking Session Summary 21-Jan-2019 095329.xlsx
Blitz Report™ XML Import DBA_AWR_Blocking_Session_Summary.xml
SQL Source DBA AWR Blocking Session Summary.sql
Full Report on Enginatics www.enginatics.com/reports/dba-awr-blocking-session-summary/

Executive 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.”

Business Challenge

Solution

This report summarizes the blocking relationships found in the history.

Key Features:

Architecture

The report analyzes DBA_HIST_ACTIVE_SESS_HISTORY to find rows where BLOCKING_SESSION is populated.

Key Tables:

Impact

© Enginatics GmbH