Monitoring and Troubleshooting Oracle APEX Performance with Real-World SQL Queries

 

 Introduction

Oracle Application Express (APEX) is one of the most widely adopted low-code development platforms in the Oracle ecosystem. As organizations scale their APEX deployments, the need for robust monitoring and troubleshooting capabilities becomes critical. This article provides SQL queries that DBAs and developers can use to monitor, diagnose, and optimize their APEX environments.

 What makes monitoring APEX unique is the three-tier architecture it operates on: the Oracle Database at the data layer, APEX at the application layer, and Oracle REST Data Services (ORDS) as the middleware. Understanding how sessions flow through these three layers is the key to effective monitoring.

  Architecture Overview 

Layer	Component	Version	Role
Database	Oracle DB	19.28	Stores data, executes SQL, manages sessions
Application	Oracle APEX	24.2x	Renders UI, manages app logic and session state
Middleware	Oracle ORDS	23.4	HTTP gateway, connection pooling, REST services

When a user opens an APEX application in their browser, the request flows: Browser → ORDS 23.4 → APEX Engine (inside Oracle DB 19.28) → Database processing → ORDS → Browser. This means ORDS maintains a pool of database connections that are reused across user requests.

Session Monitoring Queries
Session monitoring is the foundation of APEX health checks. The queries in this section help you understand who is connected, what they are doing, and how long they have been idle.

 Query 1: Current Active APEX Users

This query provides real-time visibility into APEX sessions connected via ORDS, including both ACTIVE and INACTIVE sessions within the last 30 minutes.

SELECT

    inst_id,

    username,

    osuser,

    machine,

    module,

    program,

    action,

    status,

    TO_CHAR(logon_time, 'DD-MON-YYYY HH24:MI:SS')  AS logon_time,

    last_call_et                                    AS seconds_since_last_call,

    ROUND(last_call_et / 60, 2)                     AS minutes_idle,

    CASE

        WHEN last_call_et <= 300  THEN 'ACTIVE  < 5 min'

        WHEN last_call_et <= 900  THEN 'RECENT  5-15 min'

        WHEN last_call_et <= 1800 THEN 'IDLE    15-30 min'

        ELSE                           'STALE   > 30 min'

    END                                             AS activity_status,

    COUNT(*) OVER ()                                AS total_apex_sessions

FROM

    gv$session

WHERE

    type       = 'USER'

    AND program    = 'Oracle REST Data Services'

    AND last_call_et <= 1800

ORDER BY

    last_call_et ASC;

 

  Query 2:  Count APEX Users

A summary query that gives you the total connected users. This is ideal for scheduled reports, or a quick health check before go-live.

SELECT     TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')    AS checked_at,     COUNT(*)                                        AS total_connected,     SUM(CASE WHEN last_call_et <= 300              THEN 1 ELSE 0 END)                     AS active_last_5min,     SUM(CASE WHEN last_call_et <= 900              THEN 1 ELSE 0 END)                     AS active_last_15min,     SUM(CASE WHEN last_call_et <= 1800              THEN 1 ELSE 0 END)                     AS active_last_30min FROM     gv$session WHERE     type    = 'USER'     AND program = 'Oracle REST Data Services';

Query 3:  Active Sessions Last 2 hours

Identifies sessions that have been connected for an unusually long time. Long-running sessions consume connection pool slots and can degrade performance for other users.

SELECT

    inst_id,

    username,

    machine,

    REGEXP_SUBSTR(module, 'APP (\d+)', 1, 1, NULL, 1)  AS apex_app_id,

    status,

    TO_CHAR(logon_time, 'DD-MON-YYYY HH24:MI:SS')      AS logon_time,

    ROUND((SYSDATE - logon_time) * 60, 2)               AS connected_minutes,

    ROUND(last_call_et / 60, 2)                         AS idle_minutes

FROM

    gv$session

WHERE

    type    = 'USER'

    AND program = 'Oracle REST Data Services'

    AND (SYSDATE - logon_time) * 24 <= 2               -- Within last 2 hours

    AND status  = 'ACTIVE'                             -- Executing SQL right now

ORDER BY

    connected_minutes DESC;

  

Query 4:  Top Client Generating APEX Sessions

Shows which client machines are generating the most APEX database sessions. This is valuable for capacity planning, identifying power users, detecting bot traffic, and troubleshooting client-specific issues.

SELECT

    machine                                             AS client_machine,

    osuser,

    COUNT(*)                                            AS session_count,

    SUM(CASE WHEN last_call_et <= 300

             THEN 1 ELSE 0 END)                         AS active_last_5min,

    MIN(ROUND(last_call_et / 60, 2))                    AS min_idle_min,

    MAX(ROUND(last_call_et / 60, 2))                    AS max_idle_min

FROM

    gv$session

WHERE

    type    = 'USER'

    AND program = 'Oracle REST Data Services'

GROUP BY

    machine,

    osuser

ORDER BY

    session_count DESC;

 

 Performance Analysis Queries

Performance queries help identify bottlenecks, peak load windows, and slow modules

Query 1: Peak APEX Users in Last 30 Days

Identifies the busiest hours over the past 30 days by counting distinct APEX sessions per hour. This is for capacity planning, sizing ORDS connection pools, and understanding usage patterns. It relies on Oracle AWR (Automatic Workload Repository) which samples active sessions every 10 seconds.

 SELECT

    TO_CHAR(s.sample_time, 'DD-MON-YYYY HH24')     AS hour_window,

    COUNT(DISTINCT s.session_id)              AS peak_users

FROM

    dba_hist_active_sess_history s

WHERE

    s.program    = 'Oracle REST Data Services'

    AND s.sample_time >= SYSDATE - 30

GROUP BY

    TO_CHAR(s.sample_time, 'DD-MON-YYYY HH24')

ORDER BY

    peak_users DESC

FETCH FIRST 10 ROWS ONLY;

Query 2: Top 10 Slowest APEX Modules from AWR

Identifies APEX application modules (specific pages or processes) that are generating the most database wait time. High wait time on a specific module indicates either a slow SQL query, resource contention, or an inefficient APEX process that needs optimization.

SELECT

    s.program,

    s.module,

    REGEXP_SUBSTR(s.module, 'APP (\d+)', 1, 1, NULL, 1)  AS apex_app_id,

    COUNT(*)                                               AS sample_count,

    ROUND(AVG(s.wait_time)    / 1000000, 2)               AS avg_wait_sec,

    ROUND(MAX(s.wait_time)    / 1000000, 2)               AS max_wait_sec,

    ROUND(AVG(s.time_waited)  / 1000000, 2)               AS avg_time_waited_sec,

    ROUND(MAX(s.time_waited)  / 1000000, 2)               AS max_time_waited_sec

FROM

    dba_hist_active_sess_history s

WHERE

    s.program     = 'Oracle REST Data Services'

    AND s.sample_time >= SYSDATE - 7

GROUP BY

    s.program,

    s.module,

    REGEXP_SUBSTR(s.module, 'APP (\d+)', 1, 1, NULL, 1)

ORDER BY

    avg_time_waited_sec DESC

FETCH FIRST 10 ROWS ONLY;

 Application Queries

Returns a full inventory of all APEX applications in the workspace including their authentication scheme, status, and last modification details. This is your starting point for any APEX audit or migration project.

 

 Query 1: APEX Application Inventory

 SELECT

    application_id,

    application_name,

      authentication_scheme,

    last_updated_on,

    last_updated_by

FROM

    apex_applications

ORDER BY

    application_id;

 Key Points

• APEX monitoring must consider all three layers: ORDS, APEX engine, and database 

• ORDS connection pooling directly impacts scalability 

• gv$session provides real-time visibility, while AWR enables historical analysis 

• Identifying idle and long-running sessions helps prevent connection pool exhaustion