Skip to content

DESCRIPTION.md

Latest commit

 

History

History
45 lines (36 loc) · 4.23 KB

DESCRIPTION.md

File metadata and controls

45 lines (36 loc) · 4.23 KB

Case Study & Technical Analysis: FND Concurrent Requests

Executive Summary

The FND Concurrent Requests report is the definitive operational dashboard for Oracle E-Business Suite System Administrators and Support Analysts. It provides real-time and historical visibility into the Concurrent Processing system, enabling teams to monitor system health, troubleshoot performance bottlenecks, and ensure the timely execution of critical business processes.

Business Challenge

  • Operational Blind Spots: Standard Oracle forms are often slow and lack the advanced filtering needed to manage high-volume environments with millions of requests.
  • Performance Troubleshooting: Identifying the root cause of a "long-running" request usually requires separate DBA tools to check database sessions and wait events.
  • Resource Management: Unchecked requests generating massive log or output files can silently consume disk space and degrade system performance.

The Solution

This report bridges the gap between functional administration and technical performance monitoring.

  • Unified Monitoring: It consolidates request status, scheduling parameters, and delivery options into a single, filterable view.
  • Real-Time Diagnostics: For currently running requests, it exposes critical database metrics—including the active SQL_ID, execution text, and wait classes—allowing immediate diagnosis of "stuck" jobs.
  • Proactive Alerting: Parameters like "Running longer than x Minutes" or "Log file larger than x MB" enable proactive identification of anomalies before they impact users.

Technical Architecture (High Level)

  • Primary Tables: FND_CONCURRENT_REQUESTS, FND_CONCURRENT_PROGRAMS_VL, FND_USER, FND_RESPONSIBILITY_VL.
  • Performance Views: GV$SESSION, GV$PROCESS, GV$SQL (accessed dynamically for running requests).
  • Logical Relationships:
    • The report joins the core Request table to Program, User, and Responsibility definitions to provide human-readable context.
    • For requests with Phase = 'Running', it performs a left join to the database session views (GV$SESSION) using the ORACLE_PROCESS_ID (SPID) to retrieve real-time execution details.
    • It parses and decodes the raw argument string to display meaningful parameter values.

Parameters & Filtering

  • Scheduled or Running: A binary flag to instantly filter the list to only active or pending workloads, removing historical clutter.
  • Running longer than x Minutes: A critical threshold filter for identifying performance outliers and potential runaways.
  • Show active session SQL Text: When enabled, retrieves the actual SQL statement currently executing in the database for running requests.
  • Output/Log file larger than x MB: Identifies requests that are consuming excessive file system storage.
  • Wait for x Minutes: Highlights requests that have been in a 'Pending' state for an extended period, indicating potential manager issues.

Performance & Optimization

  • Conditional Logic: The report is optimized to only query heavy performance views (GV$) when relevant (i.e., for running requests), ensuring the report itself runs instantly.
  • Indexed Access: Filters on REQUEST_ID, REQUEST_DATE, and PROGRAM_ID leverage standard EBS indexes to ensure fast retrieval of historical data.
  • Direct Output: Bypasses the XML Publisher layer to stream data directly to Excel, capable of handling tens of thousands of rows without timeout.

FAQ

Q: Can I see the SQL statement for a request that has already completed? A: No, the "Active Session SQL Text" is retrieved from the live database session cache (GV$SQL). Once a request finishes and the session closes, this runtime information is no longer available in memory.

Q: Why do some requests show a status of 'Inactive' but 'No Manager'? A: This usually indicates that the Concurrent Manager defined to run this specific program is not running or is busy. The report helps identify these bottlenecks by showing the 'Manager Name' and 'Node'.

Q: Does this report show requests from all RAC nodes? A: Yes, it queries global views (GV$) to capture session information across all nodes in a Real Application Clusters (RAC) environment.