DBA Blocking Sessions – 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

Chain of currently blocking and blocked database sessions from v$wait_chains

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

None

Oracle EBS Tables Used

dba_objects, dba_data_files, v$wait_chains, gv$session, gv$process, gv$sqlarea

Report Categories

Enginatics

DBA SGA Active Session History, DBA SGA Blocking Session Summary, DBA AWR Blocking Session Summary, DBA AWR Active Session History, DBA AWR SQL Performance Summary, DBA Sessions, DBA ORDS Configuration Validation, DBA SGA Buffer Cache Object Usage, Blitz Report Web Service 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 Blocking Sessions 06-May-2020 103312.xlsx
Blitz Report™ XML Import DBA_Blocking_Sessions.xml
SQL Source DBA Blocking Sessions.sql
Full Report on Enginatics www.enginatics.com/reports/dba-blocking-sessions/

DBA Blocking Sessions - Case Study

Executive Summary

The DBA Blocking Sessions report is a critical diagnostic tool for Oracle E-Business Suite Database Administrators (DBAs). It provides real-time visibility into database lock contention, identifying sessions that are blocking others and causing performance degradation. By visualizing the chain of blocking and blocked sessions, DBAs can quickly pinpoint the root cause of system slowness or “hanging” processes and take immediate corrective action.

Business Challenge

In a high-concurrency environment like Oracle EBS, database locking is a normal mechanism to ensure data integrity. However, when a session holds a lock for an extended period (due to a long-running transaction, uncommitted changes, or a “zombie” process), it can prevent other users from accessing the same data. This leads to:

Identifying the specific session holding the lock—and the SQL statement it is executing—is often like finding a needle in a haystack without the right tools.

Solution

The DBA Blocking Sessions report solves this challenge by querying Oracle’s v$wait_chains and related dynamic performance views. It presents a hierarchical view of the blocking chain, showing exactly which session is at the top of the chain (the “blocker”) and which sessions are waiting (the “blockees”).

Key Features:

Technical Architecture

The report leverages Oracle’s Active Session History (ASH) and wait interface architecture.

Primary Tables/Views:

Logic: The query joins v$wait_chains with session and SQL information to construct a readable output. It filters for sessions that are currently in a wait state due to another session.

Frequently Asked Questions

Q: What is the difference between a “blocking” session and a “blocked” session? A: A blocking session holds a lock on a resource (like a row in a table). A blocked session is trying to acquire a lock on the same resource but must wait until the blocking session releases it (usually by committing or rolling back).

Q: Does this report show historical blocking sessions? A: No, this report shows current blocking sessions. For historical analysis, you would typically use AWR (Automatic Workload Repository) or ASH (Active Session History) reports.

Q: What should I do if I find a blocking session? A: First, analyze the “blocker.” Is it a valid long-running job? If so, you may need to wait. Is it a user who left their screen open with uncommitted changes? You might contact them. Is it a “stuck” process? You might need to kill the session.

Q: Can this report handle RAC (Real Application Clusters) environments? A: Yes, the use of gv$ (Global) views and v$wait_chains ensures that blocking chains across different RAC nodes are detected.

© Enginatics GmbH