Monitoring the Production

When your team runs a load test against Oracle E-Business Suite 12.2, the Apps DBA is the single point of accountability for backend health. The most common ask from the test lead is:

📢 "How many users are connected? How many are active? Are there any stuck or problematic sessions? Is the JDBC pool holding up?"

This post walks through a structured, step-by-step approach to answer all of the above — with ready-to-run SQL queries and OS commands, organized in the exact order you should execute them during the test.

⚙️ Applies to: Oracle EBS 12.2.x | WebLogic 12c | Oracle Database 19c RAC | Solaris / Linux

🏗️ Understanding the Flow — Where Do Users Connect?

Before diving into queries, let's understand the connection path during a load test:

Load Tool (JMeter / OATS / Selenium)
    ↓
F5 / Load Balancer (VIP:443)
    ↓
Oracle HTTP Server (OHS) — Port 8443
    ↓   mod_wl_ohs routes to WebLogic
WebLogic oacore Managed Servers
    ↓   JDBC DataSource Pool (EBSDataSource)
Oracle Database 19c RAC — APPS Schema

Layer	What to Monitor	Who Connects
Frontend (OHS)	HTTP request count, response time, error rate	Load tool → OHS
Middle Tier (oacore WLS)	Thread pool, heap, stuck threads, JDBC pool	OHS → oacore managed servers
Backend (Database)	v$session, waits, locks, SQL perf	oacore JDBC → DB (APPS user)

As a DBA, your primary focus is the middle-tier → database boundary: the JDBC sessions that oacore opens into the database. Every user action on the UI ultimately becomes one or more JDBC sessions in v$session.

📍 STEP 1 — How Many Users Are Connected to the Frontend?

This tells you the total user load the application is handling right now.

1A. Active Application Users (ICX Sessions — Frontend)

-- Active frontend users with valid ICX sessions (logged-in users)
SELECT COUNT(DISTINCT fus.user_id) AS active_frontend_users
FROM   icx_sessions ics
JOIN   fnd_user fus ON ics.user_id = fus.user_id
WHERE  ics.disabled_flag = 'N'
AND    ics.last_connect > SYSDATE - 1/24
AND    ics.counter > 0;

1B. Detailed Breakdown — Who Is Connected, Since When, From Where

-- Detailed active frontend sessions: user, responsibility, login time, last activity
SELECT fus.user_name,
       frt.responsibility_name,
       ics.created_date                             AS login_time,
       ics.last_connect                              AS last_activity,
       ROUND((SYSDATE - ics.last_connect) * 1440, 1) AS idle_minutes,
       ics.session_id                                AS icx_session_id
FROM   icx_sessions ics
JOIN   fnd_user fus ON ics.user_id = fus.user_id
LEFT JOIN fnd_responsibility_tl frt ON ics.responsibility_id = frt.responsibility_id
       AND frt.language = 'US'
WHERE  ics.disabled_flag = 'N'
AND    ics.last_connect > SYSDATE - 1/24
AND    ics.counter > 0
ORDER BY ics.last_connect DESC;

1C. Frontend Session Count — Trend Over Time

-- Session trend: how many users connected per 10-minute window (last 2 hours)
SELECT TO_CHAR(TRUNC(ics.created_date, 'MI') -
       MOD(TO_NUMBER(TO_CHAR(ics.created_date, 'MI')), 10) / 1440,
       'HH24:MI') AS time_window,
       COUNT(*) AS sessions_created
FROM   icx_sessions ics
WHERE  ics.created_date > SYSDATE - 2/24
GROUP BY TRUNC(ics.created_date, 'MI') -
         MOD(TO_NUMBER(TO_CHAR(ics.created_date, 'MI')), 10) / 1440
ORDER BY 1;

💡 Tip: Cross-check this number with the load tool's (JMeter / OATS) virtual user count. If the ICX session count is much lower than the virtual user count, logins may be failing silently — check the OHS and oacore access logs.

📍 STEP 2 — How Many Sessions Are Connected to the Backend Database?

Every oacore managed server opens a JDBC connection pool to the database. Each pool connection appears as a session in v$session.

2A. Total Database Sessions — By Type

-- Total DB sessions breakdown: JDBC (oacore) vs Forms vs Concurrent Mgr vs Others
SELECT
  CASE
    WHEN program LIKE '%JDBC Thin%'        THEN 'JDBC (oacore/WebLogic)'
    WHEN program LIKE '%frmweb%'           THEN 'Forms'
    WHEN program LIKE '%FNDLIBR%'          THEN 'Conc Mgr (FNDLIBR)'
    WHEN program LIKE '%TNS%'              THEN 'TNS Background'
    WHEN program LIKE '%oracle@%'          THEN 'DB Background Process'
    ELSE 'Other (' || NVL(program, 'N/A') || ')'
  END AS session_type,
  COUNT(*) AS session_count,
  SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active,
  SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive
FROM   v$session
WHERE  type = 'USER'
GROUP BY
  CASE
    WHEN program LIKE '%JDBC Thin%'        THEN 'JDBC (oacore/WebLogic)'
    WHEN program LIKE '%frmweb%'           THEN 'Forms'
    WHEN program LIKE '%FNDLIBR%'          THEN 'Conc Mgr (FNDLIBR)'
    WHEN program LIKE '%TNS%'              THEN 'TNS Background'
    WHEN program LIKE '%oracle@%'          THEN 'DB Background Process'
    ELSE 'Other (' || NVL(program, 'N/A') || ')'
  END
ORDER BY session_count DESC;

2B. JDBC Sessions Per App-Tier Node (Machine-Level Breakdown)

-- JDBC pool usage per app-tier hostname
SELECT machine,
       COUNT(*) AS total_jdbc,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END)   AS active,
       SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive,
       SUM(CASE WHEN status = 'KILLED' THEN 1 ELSE 0 END)   AS killed,
       MIN(logon_time)                                         AS earliest_logon,
       MAX(logon_time)                                         AS latest_logon
FROM   v$session
WHERE  program LIKE '%JDBC Thin Client%'
GROUP BY machine
ORDER BY total_jdbc DESC;

📌 Key Check: Compare the active column with your MaxCapacity JDBC pool setting (typically 50 per managed server). If active ≈ MaxCapacity, the pool is saturated and users will get "Cannot obtain a connection" errors.

📍 STEP 3 — Drill Into Active JDBC Sessions (What Are They Doing?)

3A. All Active JDBC Sessions — Current SQL, Wait Event, Duration

-- All currently ACTIVE oacore JDBC sessions with full context
SELECT s.sid,
       s.serial#,
       s.machine,
       s.module,
       s.action,
       s.sql_id,
       s.last_call_et                                    AS running_secs,
       s.event,
       s.wait_class,
       s.blocking_session,
       s.seconds_in_wait,
       p.spid                                            AS os_pid,
       SUBSTR(sq.sql_text, 1, 200)                       AS sql_snippet
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
       AND s.sql_child_number = sq.child_number
WHERE  s.program LIKE '%JDBC%'
AND    s.status = 'ACTIVE'
AND    s.wait_class <> 'Idle'
ORDER BY s.last_call_et DESC;

3B. Active Sessions by Module & Action (Which EBS Pages Are Hit Hardest?)

-- Top EBS modules/actions consuming database time during load test
SELECT s.module,
       s.action,
       COUNT(*) AS active_sessions,
       MAX(s.last_call_et) AS max_running_secs,
       ROUND(AVG(s.last_call_et), 1) AS avg_running_secs,
       LISTAGG(DISTINCT s.sql_id, ', ') WITHIN GROUP (ORDER BY s.sql_id) AS sql_ids
FROM   v$session s
WHERE  s.program LIKE '%JDBC%'
AND    s.status = 'ACTIVE'
AND    s.wait_class <> 'Idle'
GROUP BY s.module, s.action
ORDER BY active_sessions DESC;

3C. Active vs Inactive Ratio — Per Managed Server

-- Active vs Inactive ratio per oacore managed server (by service_name / machine)
SELECT s.machine,
       s.service_name,
       COUNT(*) AS total,
       SUM(CASE WHEN s.status = 'ACTIVE' AND s.wait_class <> 'Idle'
            THEN 1 ELSE 0 END) AS truly_active,
       SUM(CASE WHEN s.status = 'INACTIVE'
            THEN 1 ELSE 0 END) AS idle_in_pool,
       ROUND(SUM(CASE WHEN s.status = 'ACTIVE' AND s.wait_class <> 'Idle'
            THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100, 1) AS active_pct
FROM   v$session s
WHERE  s.program LIKE '%JDBC Thin%'
GROUP BY s.machine, s.service_name
ORDER BY truly_active DESC;

💡 Healthy Ratio: During steady load, active_pct should stay between 10–40%. If it consistently exceeds 60–70%, the DB or oacore is under stress.

📍 STEP 4 — Identify Problematic / Stuck / Blocked Sessions

This is the most critical step. During a load test, problems manifest as sessions that are stuck too long, blocked by locks, burning CPU, or waiting on I/O.

4A. Sessions Running Longer Than 60 Seconds (Potentially Stuck)

-- JDBC sessions running > 60 sec — candidates for stuck threads
SELECT s.sid,
       s.serial#,
       s.machine,
       s.module,
       s.action,
       s.sql_id,
       s.last_call_et                               AS running_secs,
       ROUND(s.last_call_et / 60, 1)                AS running_mins,
       s.event,
       s.wait_class,
       s.blocking_session,
       SUBSTR(sq.sql_text, 1, 300)                  AS sql_snippet
FROM   v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
       AND s.sql_child_number = sq.child_number
WHERE  s.program LIKE '%JDBC%'
AND    s.status = 'ACTIVE'
AND    s.last_call_et > 60
ORDER BY s.last_call_et DESC;

4B. Blocked Sessions — Lock Holder and Waiter Chain

-- Full blocking tree: who is blocking whom
SELECT
  'BLOCKER'          AS role,
  s1.sid             AS sid,
  s1.serial#         AS serial,
  s1.machine         AS machine,
  s1.module          AS module,
  s1.sql_id          AS sql_id,
  s1.last_call_et    AS running_secs,
  s1.event           AS event,
  NULL               AS blocked_by,
  (SELECT COUNT(*) FROM v$session x WHERE x.blocking_session = s1.sid) AS victims
FROM   v$session s1
WHERE  s1.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
UNION ALL
SELECT
  'WAITER'           AS role,
  s2.sid,
  s2.serial#,
  s2.machine,
  s2.module,
  s2.sql_id,
  s2.last_call_et,
  s2.event,
  s2.blocking_session AS blocked_by,
  0
FROM   v$session s2
WHERE  s2.blocking_session IS NOT NULL
AND    s2.program LIKE '%JDBC%'
ORDER BY role, running_secs DESC;

4C. Sessions Waiting on Problematic Events

-- Sessions on known-bad wait events (locks, cursor contention, I/O)
SELECT s.sid, s.serial#, s.machine, s.module,
       s.sql_id, s.last_call_et AS running_secs,
       s.event, s.wait_class, s.seconds_in_wait,
       SUBSTR(sq.sql_text, 1, 200) AS sql_snippet
FROM   v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
       AND s.sql_child_number = sq.child_number
WHERE  s.program LIKE '%JDBC%'
AND    s.status = 'ACTIVE'
AND    s.event IN (
         'enq: TX - row lock contention',
         'enq: TM - contention',
         'cursor: pin S wait on X',
         'library cache lock',
         'library cache pin',
         'latch: shared pool',
         'log file sync',
         'db file sequential read',
         'db file scattered read',
         'gc buffer busy acquire',
         'gc cr multi block request',
         'read by other session'
       )
ORDER BY s.seconds_in_wait DESC;

4D. Top Resource-Consuming SQLs from JDBC Right Now

-- Top 15 SQLs by buffer gets from active JDBC sessions
SELECT sq.sql_id,
       sq.plan_hash_value,
       sq.executions,
       ROUND(sq.elapsed_time / 1e6, 2)          AS total_elapsed_sec,
       ROUND(sq.elapsed_time / NULLIF(sq.executions, 0) / 1e6, 3) AS per_exec_sec,
       ROUND(sq.cpu_time / 1e6, 2)              AS total_cpu_sec,
       sq.buffer_gets,
       sq.disk_reads,
       sq.rows_processed,
       SUBSTR(sq.sql_text, 1, 250)              AS sql_snippet
FROM   v$sql sq
WHERE  sq.sql_id IN (
         SELECT DISTINCT s.sql_id
         FROM   v$session s
         WHERE  s.program LIKE '%JDBC%'
         AND    s.status = 'ACTIVE'
         AND    s.sql_id IS NOT NULL
       )
ORDER BY sq.buffer_gets DESC
FETCH FIRST 15 ROWS ONLY;

4E. Open Cursor Leak Detection (ORA-01000 Prevention)

-- Sessions with dangerously high open cursor count
SELECT s.sid, s.serial#, s.machine, s.module,
       COUNT(*) AS open_cursors,
       SUM(CASE WHEN oc.sql_id = s.sql_id THEN 1 ELSE 0 END) AS current_sql_cursors
FROM   v$open_cursor oc
JOIN   v$session s ON oc.sid = s.sid
WHERE  s.program LIKE '%JDBC%'
GROUP BY s.sid, s.serial#, s.machine, s.module, s.sql_id
HAVING COUNT(*) > 250
ORDER BY open_cursors DESC;

📍 STEP 5 — Wait Event Summary (What's the Database Bottleneck?)

-- Top wait events from JDBC oacore sessions — real-time bottleneck
SELECT s.wait_class,
       s.event,
       COUNT(*) AS session_count,
       ROUND(AVG(s.seconds_in_wait), 1)        AS avg_wait_sec,
       ROUND(MAX(s.seconds_in_wait), 1)        AS max_wait_sec,
       LISTAGG(DISTINCT s.sql_id, ', ')
         WITHIN GROUP (ORDER BY s.sql_id)     AS sql_ids_involved
FROM   v$session s
WHERE  s.program LIKE '%JDBC%'
AND    s.status = 'ACTIVE'
AND    s.wait_class <> 'Idle'
GROUP BY s.wait_class, s.event
ORDER BY session_count DESC;

Wait Event	What It Means	Action
enq: TX - row lock	Row-level lock contention	Identify blocker SID, check for uncommitted DML
cursor: pin S wait on X	Hard parsing contention	Check for literal SQL, missing bind variables
db file sequential read	Single-block I/O (index reads)	Check for missing indexes or stale stats
gc buffer busy acquire	RAC inter-instance block transfer	Check for hot blocks, sequence contention
log file sync	Commit wait (redo write)	Check redo log I/O performance, log switch frequency
library cache lock/pin	DDL or invalid-object contention	Check for concurrent DDL or compilations
read by other session	Buffer cache contention	Multiple sessions reading same blocks

📍 STEP 6 — OACore JVM Health from OS Level

6A. OACore Process Memory & CPU Snapshot

# Memory (RSS in MB) and CPU for all oacore JVMs
ps -eo pid,rss,%cpu,%mem,etime,args | grep "[o]acore" | \
awk '{printf "PID: %-7s | RSS: %6d MB | CPU: %5s%% | MEM: %5s%% | UP: %s\n", $1, $2/1024, $3, $4, $5}'

6B. GC & OutOfMemory Monitoring

# Real-time tail on GC activity across all oacore logs
tail -f $EBS_DOMAIN_HOME/servers/oacore_server*/logs/oacore_server*-diagnostic.log | \
grep -iE "GC|Heap|OutOfMemory|STUCK|BEA-000337|BEA-000802"

6C. Thread Dump — Stuck Thread Analysis

# Take 3 thread dumps 10 seconds apart (golden rule for stuck thread diagnosis)
for i in 1 2 3; do
  echo "=== Thread Dump #$i at $(date) ==="
  kill -3 <oacore_pid>
  sleep 10
done
echo "Done. Check oacore managed server .out log for thread dumps."

💡 Tip: Look for threads stuck on socketRead0 (JDBC call stuck), weblogic.socket.Muxer (connection queue), or ExecuteThread: stuck — these map directly to the BEA-000337 warnings in the server log.

📍 STEP 7 — One-Shot Health Dashboard Query

Run this single query at any point during the load test to get an instant snapshot:

-- ====================================================
-- LOAD TEST HEALTH DASHBOARD — Run As SYS or APPS
-- ====================================================
SELECT '1. Frontend Users (ICX)'        AS metric,
       TO_CHAR((SELECT COUNT(DISTINCT user_id)
                FROM icx_sessions
                WHERE disabled_flag = 'N'
                AND last_connect > SYSDATE - 1/24)) AS value
FROM dual
UNION ALL
SELECT '2. Total DB Sessions',
       TO_CHAR(COUNT(*))
FROM v$session WHERE type = 'USER'
UNION ALL
SELECT '3. Total JDBC Sessions',
       TO_CHAR(COUNT(*))
FROM v$session WHERE program LIKE '%JDBC Thin%'
UNION ALL
SELECT '4. Active JDBC (Non-Idle)',
       TO_CHAR(COUNT(*))
FROM v$session WHERE program LIKE '%JDBC Thin%'
AND status = 'ACTIVE' AND wait_class <> 'Idle'
UNION ALL
SELECT '5. Blocked Sessions',
       TO_CHAR(COUNT(*))
FROM v$session WHERE blocking_session IS NOT NULL
AND program LIKE '%JDBC%'
UNION ALL
SELECT '6. Stuck Sessions (>60s)',
       TO_CHAR(COUNT(*))
FROM v$session WHERE program LIKE '%JDBC%'
AND status = 'ACTIVE' AND last_call_et > 60
UNION ALL
SELECT '7. Sessions Waiting >30s',
       TO_CHAR(COUNT(*))
FROM v$session WHERE program LIKE '%JDBC%'
AND status = 'ACTIVE' AND seconds_in_wait > 30
AND wait_class <> 'Idle'
UNION ALL
SELECT '8. Distinct Active SQLs',
       TO_CHAR(COUNT(DISTINCT sql_id))
FROM v$session WHERE program LIKE '%JDBC%'
AND status = 'ACTIVE' AND sql_id IS NOT NULL;

✅ Sample Output During a Healthy 500-User Load Test:

1. Frontend Users (ICX)      : 487
2. Total DB Sessions         : 312
3. Total JDBC Sessions       : 180
4. Active JDBC (Non-Idle)    : 35
5. Blocked Sessions          : 0
6. Stuck Sessions (>60s)     : 0
7. Sessions Waiting >30s     : 2
8. Distinct Active SQLs      : 18

📍 STEP 8 — Escalation Decision Tree

🔴 When to Escalate — Red Flags During Load Test:

➊ JDBC Pool Exhaustion: Active JDBC = MaxCapacity → Increase pool size or investigate long-running SQLs

➋ Blocked Session Count > 5: Multiple waiters on same blocker → Identify blocker SID and coordinate with functional team

➌ Stuck Sessions > 60s Growing: Sessions not completing → Take 3 thread dumps 10 sec apart → Check SQL plan, waits

➍ enq: TX - row lock Dominant: Application-level lock design issue → Report to dev team with SQL IDs

➎ cursor: pin S wait on X: Hard parse storm → Check cursor_sharing, literal SQLs, shared pool size

➏ GC / Heap Warnings in Logs: JVM memory exhaustion → May need to bounce oacore or tune -Xmx

➐ ORA-01000 (Max Open Cursors): Cursor leak in application → Immediate escalation to development

➑ Active JDBC > 60% of Total: Database cannot keep up → AWR snap, check SQL regression, I/O subsystem

💡 Pro Tip: Take an AWR snapshot at the start and end of each load test run:
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
Then generate the AWR diff report between baseline and load test for the definitive performance comparison.

🏷️ Tags

Oracle EBS 12.2 Load Testing Apps DBA OACore JDBC Pool v$session ICX Sessions Blocked Sessions Wait Events Performance Monitoring WebLogic 12c Oracle 19c RAC Stuck Threads SQL Tuning Thread Dump