Blitz Report Web Service Sessions – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Blitz Report · all Blitz Report reports

Overview

Blitz Report runs that execute inside a web service call, from the Excel add-in or a Blitz FSG drilldown, and are still active, with the Oracle EBS user and responsibility that started them.

Running Minutes is the run time of the current database call. The Excel clients stop waiting after 15 minutes. A run that is still active after that has no client reading its result, but its database session and the middle-tier thread serving it stay busy until the query ends.

Kill Session holds the command that ends the session. Temp Used is in MB.

Schedule the report with Running Minutes From to be notified of such sessions.

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

Running Minutes From

Oracle EBS Tables Used

fnd_responsibility_vl, gv$session, gv$process, xxen_report_runs, xxen_reports_v, dba_tablespaces, gv$tempseg_usage, fnd_concurrent_requests

Report Categories

Enginatics

DBA Sessions, Blitz Report History, FND Concurrent Requests, DBA ORDS Configuration Validation, DBA AWR SQL Performance Summary, Blitz Upload History, DBA Blocking 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 Example
Blitz Report™ XML Import Blitz_Report_Web_Service_Sessions.xml
SQL Source Blitz Report Web Service Sessions.sql
Full Report on Enginatics www.enginatics.com/reports/blitz-report-web-service-sessions/

Case Study & Technical Analysis: Blitz Report Web Service Sessions

Executive Summary

The Blitz Report Web Service Sessions report lists the Blitz Report runs that are executing inside a web service call and are still active. These runs come from the Excel add-in or from a Blitz FSG drilldown, and each row shows the Oracle E-Business Suite user and responsibility that started it. System administrators use it to see which reports are running, how long they have been running and how much temporary tablespace they consume, and to end a session that has become unproductive. The report is a monitoring tool for the Blitz Report server side of an installation.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. GV$SESSION is joined to GV$PROCESS on instance and process address, so each session carries its operating system process id.
  2. The run id in the first part of the session’s action attribute is matched to XXEN_REPORT_RUNS, and the run is kept only while its request id, completion date and completion message are empty.
  3. XXEN_REPORTS_V supplies the report name for the run, and FND_RESPONSIBILITY_VL supplies the responsibility name from the run’s responsibility id.
  4. Temporary usage is summed per instance and session address in an inline view, then joined back to the session on the session address.
  5. Sessions that belong to an active concurrent request in FND_CONCURRENT_REQUESTS are excluded.

Parameters & Filtering

The report has no other parameters; the list is always the current set of active web service sessions.

Performance & Optimization

FAQ

Q: How do I find long-running Blitz Report web service calls? A: Run the report with Running Minutes From set to the threshold, for example 15. Each row is an active run, with its user, responsibility, report name and running minutes.

Q: How do I stop a Blitz Report session that is still running? A: Copy the Kill Session command of the row and run it with a user who has the ALTER SYSTEM privilege. The command ends the session immediately.

© Enginatics GmbH