During performance or load testing on Oracle E-Business Suite 12.2, the oacore managed servers bear the heaviest user load — they serve every OAF page, self-service flow, and API call. From an Apps DBA perspective, you need real-time visibility into JVM health, JDBC pool saturation, SQL performance, wait events, and locking — all from the backend.

This post is a ready-to-use toolkit of SQL queries and OS commands you can run during a live load test to catch problems before they snowball.

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

🖥️ 1. OACore JVM Process & Memory — OS Level

📌 List All OACore Processes with CPU and Memory

# List all oacore JVM processes with resource usage
ps -ef | grep oacore | grep -v grep | awk '{print $2}' | while read pid; do
  echo "=== PID: $pid ==="
  ps -o pid,ppid,%cpu,%mem,vsz,rss,etime,args -p $pid
done

📌 Heap Usage Summary — RSS in MB

# Quick memory snapshot (RSS in MB) for all oacore processes
ps -eo pid,rss,args | grep "[o]acore" | awk '{printf "PID: %s | RSS: %d MB | %s\n", $1, $2/1024, $3}'

📌 Real-Time GC Log Monitoring

# Tail GC logs for heap pressure and OutOfMemory events
tail -f $EBS_DOMAIN_HOME/servers/oacore_server*/logs/oacore_server*-diagnostic.log | grep -i "GC\|Heap\|OutOfMemory"

🔗 2. Active OACore Sessions & Connections — Database Side

📌 Current OACore Sessions Hitting the Database

-- Active oacore sessions in the database
SELECT s.sid, s.serial#, s.username, s.status,
       s.machine, s.program, s.module, s.action,
       s.sql_id, s.last_call_et AS idle_secs,
       s.event, s.wait_class,
       p.spid AS os_pid
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.program LIKE '%JDBC%'
AND    s.module LIKE '%oacore%'
ORDER BY s.last_call_et;

📌 Session Count Breakdown per App Node

-- Session counts by status and machine (oacore app nodes)
SELECT s.machine, s.status, s.module,
       COUNT(*) AS session_count
FROM   v$session s
WHERE  s.program LIKE '%JDBC%'
AND    (s.module LIKE '%oacore%' OR s.module LIKE '%OA%' OR s.action LIKE '%OA%')
GROUP BY s.machine, s.status, s.module
ORDER BY session_count DESC;

🏊 3. JDBC Connection Pool Saturation

-- JDBC pool usage per machine — watch for spikes during load test
SELECT machine,
       COUNT(*) AS total_jdbc_sessions,
       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
FROM   v$session
WHERE  program LIKE '%JDBC Thin Client%'
GROUP BY machine
ORDER BY total_jdbc_sessions DESC;
💡 Tip: Compare the active count against your MaxCapacity in the oacore JDBC datasource. If they're equal or within 5, the pool is saturated — users will start seeing timeouts.

🐌 4. Long-Running & Blocking SQL from OACore

📌 Top SQLs by Elapsed Time from OACore Sessions

-- Top SQL by elapsed time from oacore JDBC sessions
SELECT sq.sql_id, sq.plan_hash_value,
       sq.executions,
       ROUND(sq.elapsed_time/1e6, 2) AS elapsed_sec,
       ROUND(sq.cpu_time/1e6, 2)     AS cpu_sec,
       sq.buffer_gets,
       sq.disk_reads,
       sq.rows_processed,
       SUBSTR(sq.sql_text, 1, 200) AS sql_text_snippet
FROM   v$session s
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.module LIKE '%oacore%' OR s.module LIKE '%OA%')
ORDER BY sq.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

📌 Stuck OACore Queries (> 60 Seconds)

-- Long-running oacore queries > 60 seconds
SELECT s.sid, s.serial#, s.sql_id,
       s.last_call_et AS running_secs,
       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
AND    (s.module LIKE '%oacore%' OR s.module LIKE '%OA%')
ORDER BY s.last_call_et DESC;

⏳ 5. Wait Events & Contention from OACore

📌 Top Wait Events — Real-Time Bottleneck Detection

-- Wait event distribution for oacore sessions
SELECT s.event, s.wait_class,
       COUNT(*) AS waiters,
       ROUND(AVG(s.wait_time_micro)/1e6, 3) AS avg_wait_sec,
       ROUND(MAX(s.wait_time_micro)/1e6, 3) AS max_wait_sec
FROM   v$session s
WHERE  s.program LIKE '%JDBC%'
AND    s.status = 'ACTIVE'
AND    s.wait_class <> 'Idle'
GROUP BY s.event, s.wait_class
ORDER BY waiters DESC;

📌 Enqueue / Lock Contention — Blocking Chains

-- Blocking chains affecting oacore JDBC sessions
SELECT l.sid AS blocker_sid,
       s1.serial# AS blocker_serial,
       s1.sql_id AS blocker_sql,
       w.sid AS waiter_sid,
       s2.serial# AS waiter_serial,
       s2.sql_id AS waiter_sql,
       s2.seconds_in_wait
FROM   v$lock l
JOIN   v$lock w ON l.id1 = w.id1 AND l.id2 = w.id2
       AND l.block = 1 AND w.request > 0
JOIN   v$session s1 ON l.sid = s1.sid
JOIN   v$session s2 ON w.sid = s2.sid
WHERE  s2.program LIKE '%JDBC%';

📊 6. OACore Managed Server & Thread Status

📌 DMS Metrics via SQL

-- OACore DMS servlet response times (if FND OAM tables are populated)
SELECT metric_short_name, value, collection_timestamp
FROM   fnd_oam_metval
WHERE  metric_short_name IN ('oacore_heap_free','oacore_heap_max',
                              'oacore_active_sessions','oacore_request_count')
AND    collection_timestamp > SYSDATE - 1/24
ORDER BY collection_timestamp DESC;

📌 Thread Dump for Stuck Thread Analysis

# Take a thread dump (replace <oacore_pid> with actual PID)
kill -3 <oacore_pid>

# Check the managed server log for the thread dump output
grep -A 500 "Full thread dump" $EBS_DOMAIN_HOME/servers/oacore_server1/logs/oacore_server1.out | head -600

# Or via WLST:
# $FMW_HOME/oracle_common/common/bin/wlst.sh
# connect('weblogic','<pwd>','t3://localhost:<oacore_port>')
# threadDump()

🔓 7. Shared Pool & Cursor Pressure

-- Open cursor count per oacore session (watch for ORA-01000)
SELECT s.sid, s.serial#, s.machine,
       COUNT(*) AS open_cursors
FROM   v$open_cursor oc
JOIN   v$session s ON oc.sid = s.sid
WHERE  s.program LIKE '%JDBC%'
AND    (s.module LIKE '%oacore%' OR s.module LIKE '%OA%')
GROUP BY s.sid, s.serial#, s.machine
HAVING COUNT(*) > 200
ORDER BY open_cursors DESC;

📋 8. Quick Health Dashboard — Single Pane Query

Run this single query to get a snapshot of all critical oacore metrics in one shot:

-- OACORE LOAD TEST HEALTH DASHBOARD
SELECT 'Total JDBC Sessions'     AS metric, TO_CHAR(COUNT(*)) AS value
FROM v$session WHERE program LIKE '%JDBC Thin%'
UNION ALL
SELECT 'Active JDBC Sessions',   TO_CHAR(COUNT(*))
FROM v$session WHERE program LIKE '%JDBC Thin%' AND status='ACTIVE'
UNION ALL
SELECT 'Blocked Sessions',       TO_CHAR(COUNT(*))
FROM v$session WHERE blocking_session IS NOT NULL AND program LIKE '%JDBC%'
UNION ALL
SELECT 'Sessions Waiting > 30s', TO_CHAR(COUNT(*))
FROM v$session WHERE program LIKE '%JDBC%' AND status='ACTIVE'
AND last_call_et > 30 AND wait_class <> 'Idle'
UNION ALL
SELECT '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;

🚨 Key Thresholds & Red Flags

🔴 Immediate Escalation Triggers During Load Test:

1. OACore JVM RSS > 4–6 GB (depends on your -Xmx setting)
2. JDBC active sessions = MaxCapacity (pool exhaustion → user timeouts)
3. last_call_et consistently > 30–60 seconds (stuck requests)
4. Wait events shift from Idle to Application or Concurrency class
5. Open cursors per session > 300 (ORA-01000 risk)
6. enq: TX - row lock contention dominating wait events
7. cursor: pin S wait on X appearing frequently
8. Frequent Full GC events or OutOfMemoryError in diagnostic logs
💡 Pro Tip: Wrap the Health Dashboard query (Section 8) in a shell loop with watch -n 30 or schedule via cron every 30 seconds for continuous monitoring throughout the load test window. Combine with vmstat 5 and iostat -x 5 on the app-tier nodes for full-stack visibility.

🏷️ Tags

Oracle EBS 12.2 OACore Load Testing Apps DBA WebLogic 12c JDBC Pool Performance Monitoring v$session Wait Events SQL Tuning JVM Monitoring Oracle 19c