SYS-03: Database Performance Snapshot

Oracle EBS database performance degradation manifests as slow page response, concurrent program timeouts, and lock wait events that are invisible to the application layer but fully visible in Oracle's V$ dynamic performance views. When the Oracle DBA team is not embedded in the support engagement, the Oracle EBS consultant is often the first to identify database-level root causes by reading the V$ views that are accessible from an application schema connection — V$SESSION, V$SQL, V$LOCK, V$WAIT_STAT, and V$SYSSTAT — and interpreting what they reveal about the database workload.

Blocking sessions are the most operationally impactful database performance issue in Oracle EBS. A blocking session holds an exclusive lock on a row or table that another session is waiting to modify. The blocked session is suspended and cannot complete until the blocker either commits or is killed. In Oracle EBS, the most common blocking scenario is a concurrent program that holds a lock on FND_CONCURRENT_REQUESTS or on an application table row, blocking both other concurrent programs and end-user transactions from completing. SYS-03 identifies blocking chains — the root blocker, all blocked sessions, and the SQL they were executing at the moment of the snapshot.

Top SQL by buffer gets (logical reads) and elapsed time are the second major diagnostic target. A single poorly-performing SQL statement executing thousands of times per hour can saturate the buffer cache and force physical disk reads for all other sessions. SYS-03 captures the top 10 SQL statements by buffer gets and by elapsed time from V$SQL, with the execution plan hash value for the DBA team to analyze. This provides the application team with the specific SQL and its source module before escalating to the DBA for execution plan analysis.

SYS-03 provides a read-only Oracle EBS database performance snapshot — no DML, no schema changes, no index creation — using only V$ views accessible from an application schema. The output is a structured diagnostic snapshot that the consultant can hand to the DBA team with specific SQL, session IDs, and wait event names to investigate.

Blocking Chain Analysis

Root blocking session identification — SID, user, program, lock object, duration. Full chain of blocked sessions. SQL each session was executing. Lock type and mode. The complete picture needed to safely terminate the root blocker.

Top SQL Identification

Top 10 SQL statements by buffer gets and by elapsed time from V$SQL. SQL_ID, plan hash value, execution count, and source module for each. Provides the DBA team with specific SQL to analyze without requiring them to be on-site.

Wait Event Profile

Top wait events by total wait time in the snapshot window. Wait class (I/O, Concurrency, Application, Network). Distinguishes application-layer waits (lock contention, commit) from I/O waits (db file sequential read) for correct escalation path.

Capacity Indicators

Tablespace free percentage for all tablespaces. Buffer cache hit ratio. Redo log switch frequency. Active session count by module. These four indicators establish whether the issue is a one-time event or a systemic capacity constraint.

════════════════════════════════════════════════════════════
  ORACLE EBS R12 — DATABASE PERFORMANCE SNAPSHOT
════════════════════════════════════════════════════════════
  Environment        : PROD
  Snapshot Date      : 25-FEB-2026 10:44:18
  Case Number        : SYS-504180
════════════════════════════════════════════════════════════

[ SECTION 1 — BLOCKING SESSIONS ]          STATUS: ✗ BLOCKING CHAIN
────────────────────────────────────────────────────────────
  ✗ BLOCKING CHAIN DETECTED
  Blocker SID 881    : User APUSER — Validate Payables Invoices — holding 14m
  Blocked SID 892    : User GLUSER — Post Journals — waiting 14m
  Blocked SID 901    : User APUSER2 — Create Accounting — waiting 12m
  Lock Object        : AP_INVOICES_ALL — row lock on INVOICE_ID 4841022

[ SECTION 2 — TOP SQL (BUFFER GETS) ]      STATUS: ⚠ HIGH LOAD
────────────────────────────────────────────────────────────
  SQL_ID 7gk2a8fqm4h : 8,241,084 buffer gets — 14,882 executions ⚠
  Module             : SQLAP — Validate Payables Invoices
  Text               : SELECT * FROM AP_INVOICES_ALL WHERE ORG_ID = :b1...

[ SECTION 3 — TOP WAIT EVENTS ]            STATUS: ⚠ LOCK WAITS
────────────────────────────────────────────────────────────
  ⚠ 'enq: TX - row lock contention' — 14m 22s total wait time
  'db file sequential read' — 2m 18s — within normal range ✓
  'log file sync' — 18s — healthy ✓

[ SECTION 4 — TABLESPACE ]                 STATUS: ✓ PASS
────────────────────────────────────────────────────────────
  All tablespaces > 20% free — no space pressure ✓

[ SECTION 5 — BUFFER CACHE ]               STATUS: ✓ PASS
────────────────────────────────────────────────────────────
  Buffer cache hit ratio: 98.4% — healthy ✓

════════════════════════════════════════════════════════════
  DIAGNOSTIC SUMMARY
════════════════════════════════════════════════════════════
  Blocking chain — SID 881 holding row lock on AP_INVOICES_ALL
  Top SQL driving excessive buffer gets — plan review recommended
  FIX: Terminate blocker SID 881 (DBA), share top SQL with DBA team
════════════════════════════════════════════════════════════
  Snapshot saved  : CONS_BACKUP.SYS_PERF_SNAPSHOT_504180 ✓
  Registry ID     : 7122
════════════════════════════════════════════════════════════

Diagnostic Engine

Runs 30+ checks across blocking session chains (root blocker, all blocked sessions, lock object), top 10 SQL by buffer gets and elapsed time with module source, top wait events with total wait time, tablespace free space, buffer cache hit ratio, redo log switch frequency, and active session count by module.

Backup Created

SYS-03 is entirely read-only — SELECT only from V$ views. No DML of any kind. A point-in-time snapshot of V$SESSION, V$SQL, and V$LOCK is saved to CONS_BACKUP.SYS_PERF_SNAPSHOT_<case#> for the DBA escalation package. The snapshot documents the state at the moment the diagnostic was run.

Guided Data Fix

SYS-03 produces a structured escalation package for the DBA team: blocking chain with session IDs and SQL, top SQL with SQL_ID and plan hash, wait event profile. The consultant-level fix is terminating the root blocking session — which requires ALTER SYSTEM KILL SESSION privilege (DBA) and is confirmed with the DBA before execution.

KB Article Generated

Complete KB article generated — snapshot timestamp, blocking chain details, top SQL identification, wait event profile, DBA escalation package. Upload directly to your knowledge base.

KB-SYS-504180-001 · Script: SYS-03

DB Blocking Chain — SID 881 (Validate Payables Invoices) Blocking 2 Sessions for 14 Minutes

Module: System / Technical
Platform: EBS R12.2.10
Status: RESOLVED

Symptom

Oracle EBS PROD — blocking session chain detected. SID 881 (APUSER, Validate Payables Invoices) holding row lock on AP_INVOICES_ALL INVOICE_ID 4841022 for 14 minutes. 2 sessions blocked — Post Journals (SID 892) and Create Accounting (SID 901). Top wait event: enq: TX - row lock contention.

Root Cause

Validate Payables Invoices concurrent program (SID 881) acquired an exclusive row lock on AP_INVOICES_ALL during validation and did not release it. Concurrent program had been running for 4h 22m — significantly over normal duration of 35 minutes. Investigation found the program was stuck processing an invoice with a CCID disabled after the program started.

Tables Affected

V$LOCK: SID 881 LMODE=6 (exclusive) on AP_INVOICES_ALL OBJECT_ID 48812
V$SESSION: SID 892, 901 REQUEST=6, EVENT=enq: TX - row lock contention

Fix Applied

DBA terminated SID 881 (Validate Payables Invoices) via ALTER SYSTEM KILL SESSION. Blocked sessions 892 and 901 resumed immediately. CCID causing the stuck validation identified (01-6200-7100-0000 — disabled 01-FEB). CCID re-enabled temporarily, invoice revalidated, CCID re-disabled. Blocked programs resubmitted and completed successfully.

Prevention

Validate Payables Invoices program duration monitoring added — alert if running > 2 hours. CCID disable checklist updated: check for invoices in validation using the account before disabling.

Session Blocking Chain

The most urgent database-level issue in EBS. A stuck or long-running session holds an exclusive lock that cascades — 1 blocker can freeze 10+ sessions. SYS-03 identifies the root blocker, all blocked sessions, and the exact SQL and object involved for the DBA termination decision.

SQL

High-Load SQL Saturating Buffer Cache

A single SQL statement with millions of buffer gets can degrade performance for every other session. SYS-03 surfaces the SQL_ID and module source from V$SQL. The DBA team uses the plan hash value to identify whether a missing index or a bad plan is the cause.

Space

Tablespace Approaching Full

A tablespace below 10% free will cause ORA-01653 errors the moment a program tries to extend a segment. SYS-03 identifies the tablespace, current free space, and the largest segments to support the DBA's space allocation decision.

Redo

Redo Log Switches Too Frequent

Log switches more than 4 per hour indicate redo log files are undersized for the current transaction volume. Each switch causes a brief checkpoint stall. SYS-03 identifies the switch frequency and the current redo log file sizes.

View	Type	Purpose
V$SESSION	V$ View	Active sessions — SID, user, program, status, wait event
V$SQL	V$ View	SQL in shared pool — buffer gets, elapsed time, executions, plan hash
V$LOCK	V$ View	Lock holders and waiters — type, mode, object
V$WAIT_STAT	V$ View	Wait event statistics — total waits, total wait time by event
V$SYSSTAT	V$ View	System statistics — buffer gets, physical reads, redo size
DBA_SEGMENTS	DBA View	Segment sizes — for tablespace capacity analysis
DBA_FREE_SPACE	DBA View	Tablespace free space by datafile

Decision Framework

How Every Fix Decision Is Made

Before any data is modified in a production Oracle database, AP-01 walks through a four-stage decision process. Every condition identified by the diagnostic maps to exactly one resolution path.

First Option — Always

Can the front end fix this?

Oracle's own forms and concurrent programs are always the first option. If the condition can be corrected through Oracle's standard UI — a form, a concurrent program, a setup screen — that path is taken first. No consultant SQL required, no database risk, and the fix is fully supported by Oracle. The diagnostic identifies these conditions explicitly and states the exact front-end navigation path.

✓ Functional Fix

When Front End Is Not Available

Is a direct data fix safe to apply?

When the front-end path is unavailable or would require an unacceptable volume of manual steps, a direct fix is evaluated against explicit criteria. All of the following must be true before proceeding:

→The fix mirrors exactly what Oracle's own code would have done if the underlying condition were corrected

→All dependent tables have been identified and will be updated in the same transaction

→The fix is fully reversible — a single INSERT from the backup table restores the original state

→The environment patch level has been confirmed against the fix logic version

⚡ Direct Fix

Hard Stops — No Exceptions

Does this require Oracle Support?

Certain table areas are never touched directly, regardless of how well the underlying structure is understood. The diagnostic flags these conditions and generates the Service Request documentation:

✗XLA subledger tables — XLA_EVENTS, XLA_AE_HEADERS — incorrect changes corrupt the subledger audit trail in ways undetectable until period close fails or an auditor requests a reconciliation

✗Workflow engine tables — WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES — ad-hoc DML can corrupt the workflow engine state instance-wide

✗Recurring conditions after a fix — indicates a code defect, not a data error. Documented and submitted as a Service Request with a reproducible test case

⚠ Oracle Support

For All Approved Direct Fixes

Backup → Execute → Verify → Document

A timestamped backup table is created and row-count verified before the first UPDATE fires. Explicit parameter confirmation is required — the script will not self-execute. After execution, a verification query confirms the expected state. A complete change record — rows affected, before and after values, database username, timestamp — is written to the FIX_BACKUP_REGISTRY and becomes the primary artifact in the knowledge base entry for this incident.

📋 Documented

Condition Identified	Resolution Path	Notes
Blocking session — root blocker holding lock	Functional First	The DBA terminates the root blocking session via ALTER SYSTEM KILL SESSION after confirming with the session owner that termination is safe. SYS-03 provides the exact SID, SERIAL#, and the SQL the session was running.
Top SQL excessive buffer gets — driving high logical read load	Functional First	Share the SQL_ID and plan hash value from SYS-03 with the DBA team for execution plan analysis. Application-level fix is to identify the module generating the SQL and review the query or underlying EBS setup.
Tablespace approaching full — < 10% free	Functional First	Alert the DBA team with the tablespace name, current free percentage, and growth rate from the SYS-03 snapshot. DBA action: add datafile or enable autoextend. SYS-03 documents the tablespace state before and after.
Buffer cache hit ratio below threshold — < 95%	Functional First	Escalate to DBA with the SYS-03 buffer cache statistics and the top SQL driving physical reads. DBA action: review DB_CACHE_SIZE parameter and identify scan-heavy queries.
Redo log switches too frequent — > 4/hour	Functional First	Escalate to DBA with redo log switch frequency from SYS-03. DBA action: increase redo log file size. SYS-03 documents the switch pattern and the database archiving mode.
Active session spike — sudden increase in concurrent users	Functional First	SYS-03 identifies the module and program driving the session spike. Application-level action depends on the source — a runaway concurrent program, a batch job, or an end-user load event.
Specific wait event dominating	Functional First	SYS-03 identifies the dominant wait event and its total duration in the snapshot window. The DBA team interprets the wait class (I/O, Concurrency, Application) and recommends the fix.
Performance data needed for Oracle Support SR	Direct Fix	SYS-03 packages the V$ snapshot data into CONS_BACKUP.SYS_PERF_SNAPSHOT_<case#> in a format suitable for upload to Oracle Support. The snapshot includes all session, SQL, and wait event data from the moment the issue was occurring.

Safeguards

Nothing Executes Without a Safety Net

Before any data fix runs, the script verifies pre-flight conditions and creates a complete verified backup. If any check fails, the script aborts. There is no partial execution path.

Pre-Flight Checks — All Must Pass

✓

POSTED_FLAG = 'N'

Will not modify a posted transaction under any circumstances

✓

ACCOUNTING_EVENT_ID IS NULL

Will not modify rows with an active or pending XLA accounting event

✓

No active database lock

Checks V$LOCK — will not proceed if another session holds a lock on target rows

✓

CONS_BACKUP schema writable

Backup schema must be accessible before any DML is permitted

✓

Explicit confirmation required

Script outputs a confirmation prompt — DML does not execute until consultant enters the case number as the execution parameter

Backup Methodology

Before the first UPDATE fires, the script creates a complete copy of every row to be modified. Tables are named CONS_BACKUP.<TABLE>_<CASE#> and persist permanently after execution.

V$SESSION snapshot

V$SQL snapshot

V$LOCK snapshot

Row count verified after backup creation. If backup fails for any reason the script aborts without executing any DML. Rollback is a single INSERT from the backup table. No reconstruction required.

SYS-03 — Pre-Flight & Backup Verification

════════════════════════════════════════════════════════════
  PRE-FLIGHT — READ-ONLY SNAPSHOT
════════════════════════════════════════════════════════════
  Mode               : SELECT ONLY — no DML of any kind ✓
  V$ Access          : V$SESSION, V$SQL, V$LOCK accessible ✓
  DBA Views          : DBA_FREE_SPACE, DBA_SEGMENTS accessible ✓
  CONS_BACKUP Schema : Accessible — snapshot table only ✓
────────────────────────────────────────────────────────────
  ALL PRE-FLIGHT CHECKS PASSED
════════════════════════════════════════════════════════════
  Creating : CONS_BACKUP.SYS_PERF_SNAPSHOT_504180
  Rows     : V$SESSION: 284 rows | V$SQL top 10 | V$LOCK: 3 rows ✓
  Registry : FIX_BACKUP_REGISTRY — ID 7122 created ✓
────────────────────────────────────────────────────────────
  SNAPSHOT SAVED — Escalation package ready for DBA team
════════════════════════════════════════════════════════════

Knowledge Base

Every Execution Produces a Record

The knowledge base article is generated automatically from the script's execution output. No manual documentation required. It becomes the institutional record — for the team, for auditors, and for every future engagement in the same environment.

Zero Manual Effort

Every field — environment, tables, before/after values, backup reference, root cause, prevention — is generated from actual execution output. Nothing is written by hand.

Patterns Surface Over Time

The first engagement produces findings. The third produces patterns. Recurring conditions that are invisible as individual incidents become obvious as knowledge base trends.

Survives Staff Turnover

The knowledge base is an institutional record of the Oracle environment. A new manager, a new DBA, or an external auditor can read exactly what happened, what was done, and what prevents recurrence.

KB-SYS-504180-001

DB Blocking Chain — SID 881 (Validate Payables Invoices) Blocking 2 Sessions for 14 Minutes

EBS R12.2.10 · System / Technical

● RESOLVED

Symptom

Root Cause

Tables

V$LOCK: SID 881 LMODE=6 (exclusive) on AP_INVOICES_ALL OBJECT_ID 48812
V$SESSION: SID 892, 901 REQUEST=6, EVENT=enq: TX - row lock contention

Fix Applied

Prevention

Validate Payables Invoices program duration monitoring added — alert if running > 2 hours. CCID disable checklist updated: check for invoices in validation using the account before disabling.

R12 Guide (PDF)	Title & Chapter	Detail
120apug.pdf	Oracle Payables User's Guide — Ch. 3: Payables Open Interface Import	pp. 3-139 to 3-151: Large interface table volume and purge program for performance
120invug.pdf	Oracle Inventory User's Guide — Period Close	Pending transaction volume and MTL_MATERIAL_TRANSACTIONS cleanup for performance
120poug.pdf	Oracle Purchasing User's Guide — Ch. 9: Receipt Accruals	AP/PO accrual reconciliation report and accrual write-off for large table cleanup

Database Performance Snapshot

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in SYS-03

Backup & Rollback for SYS-03

Tables Backed Up — SYS-03

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

What This Script Finds

Session Blocking Chain

High-Load SQL Saturating Buffer Cache

Tablespace Approaching Full

Redo Log Switches Too Frequent

Tables Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Ready to Resolve This in Your Environment?

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in SYS-03

Backup & Rollback for SYS-03

Tables Backed Up — SYS-03

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

What This Script Finds

Session Blocking Chain

High-Load SQL Saturating Buffer Cache

Tablespace Approaching Full

Redo Log Switches Too Frequent

Tables Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Related Diagnostic Scripts

Concurrent Manager Health

Workflow Engine Diagnostic

Interface & Open Tables Audit

Ready to Resolve This in Your Environment?