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

OACORE Monitoring

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

SQL Plan Management Explained Via Kitchen Analogy

📌 Oracle 19c · EBS 12.2 · Performance Tuning

🍽️ SQL Plan Management:
Explained via Restaurant Kitchen Analogy

Oracle Apps DBA Lead  |  appsdbastuff.blogspot.com

🧠 SQL Plan Management (SPM) is Oracle's mechanism to prevent plan regressions — it captures, verifies, and enforces known-good execution plans so the optimizer never silently switches to a worse one. If you've ever scratched your head trying to explain baselines and profiles to a junior DBA, try this: it's exactly how a professional kitchen manages its recipes. This post walks through every SPM concept with a parallel restaurant analogy — side by side, step by step.

🗺️ The Complete Analogy Map

Before we dive deep, here's the full mapping at a glance:

Step	🔵 Oracle / SPM Concept	🍽️ Restaurant Analogy	Why It Maps
01	Cursor Cache — Optimizer picks plan	Chef decides how to cook the dish	Both choose the "best method" from available options at parse/order time
02	Capturing Plans — Auto / LOAD_PLANS	Chef writes down the recipe	Good method recorded so it can be reproduced consistently
03	SQL Plan Baseline — ACCEPTED store	Official approved menu recipe book	Only stamped, verified plans/recipes are used — no ad-hoc improvisation
◆	Decision gate — Plan regressed?	Expeditor quality check — dish pass/fail	Gate controls whether the plan/dish proceeds or gets sent back
04	SQL Profile — cardinality fix	Specialist seasoning — corrects without rewriting recipe	Auxiliary correction applied on top of the existing plan/recipe
05	AWR / ASH / SQL Monitor	Food critic + kitchen CCTV	Continuous measurement that feeds back into the improvement cycle

Now let's go through each step in detail.

🗃️ Step 01 — Cursor Cache & The Chef's Decision

STEP 01

🗃️

Cursor Cache / Library Cache

Hard parse → optimizer evaluates all access paths → cheapest plan loaded

🔵 Oracle Concept

V$SQL holds every parsed query — SQL_ID, PLAN_HASH_VALUE, elapsed time, executions

Optimizer evaluates FTS, index range scan, hash join, nested loop → picks lowest-cost plan

Bind variable peeking on first parse; adaptive cursor sharing for skewed histograms

SPM intercepts: if baseline exists, optimizer MUST use an ACCEPTED plan from it

🍽️ Restaurant Analogy

Guest places an order = Application submits a SQL query

Chef's mental process "how do I cook this?" = Optimizer generating the execution plan

Multiple cooking methods (grill / bake / fry) = multiple access paths (index / FTS / hash join)

Chef's recipe binder checked first = SPM baseline consulted before new plan is used

🔍 Key V$SQL Query — Find Heavy SQLs

-- Top 10 SQLs by elapsed time with plan hash — starting point for SPM
SELECT sql_id,
       plan_hash_value,
       ROUND(elapsed_time/NULLIF(executions,0)/1e6,2)  elapsed_sec_per_exec,
       executions,
       buffer_gets,
       SUBSTR(sql_text,1,80)                            sql_preview
  FROM  v$sql
 WHERE  executions > 10
 ORDER BY elapsed_time DESC
 FETCH FIRST 10 ROWS ONLY;

📡 Step 02 — Capturing Plans & Writing the Recipe

STEP 02

📡

Capturing Plans into SPM

Auto capture · bulk load from cursor cache or AWR · staging table for transport

🔵 Oracle Concept

Auto capture: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE — every repeatable SQL gets its plan saved automatically

Manual load: DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE — target specific SQL_IDs from V$SQL

AWR load: DBMS_SPM.LOAD_PLANS_FROM_AWR — pull historical plans from DBA_HIST_SQLSTAT

New plan for same SQL signature → ENABLED but NOT ACCEPTED until evolution approves it

🍽️ Restaurant Analogy

Auto capture = chef reflexively jotting down the method every time a popular dish turns out perfectly

Manual load = head chef deliberately documents a recipe after a guest praises it

AWR load = reviewing last month's kitchen log to find which method produced the best-rated dish

New recipe variation noted separately — not yet on the approved menu until trialled and approved

🔧 Load Plans — Auto Capture + Manual Bulk Load

-- Enable automatic capture (set at instance or session level)
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

-- Manual bulk load from cursor cache for a specific SQL_ID
DECLARE
  l_count  PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
               sql_id        => '&your_sql_id',
               plan_hash_value => NULL   -- NULL = load ALL plans for this SQL
             );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_count);
END;
/

-- Load from AWR (useful for plans no longer in cursor cache)
DECLARE
  l_count  PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.LOAD_PLANS_FROM_AWR(
               begin_snap => 12000,
               end_snap   => 12050,
               basic_filter => 'sql_id = ''&your_sql_id'''
             );
  DBMS_OUTPUT.PUT_LINE('Plans loaded from AWR: ' || l_count);
END;
/

📋 Step 03 — SQL Plan Baseline & The Official Recipe Book

STEP 03

📋

SQL Plan Baseline — The Accepted Plan Store

DBA_SQL_PLAN_BASELINES · SYSAUX · ENABLED / ACCEPTED / FIXED / REPRODUCED flags

🔵 Oracle Concept

ACCEPTED=YES: optimizer ONLY uses these plans — all others are tried but fall back here

FIXED=YES: overrides cost model changes, stats recompute, patch upgrades — hard lock

Evolution: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE promotes a new plan only if ≥1.5× faster

Baseline survives DB bounces, ADOP patch cycles, stats recomputes — plan stability guaranteed

🍽️ Restaurant Analogy

ACCEPTED recipe = dish is on the official printed menu; every cook follows the exact steps

FIXED=YES = "signature dish" locked by the head chef — no substitutions, no improvisation ever

Evolution = new preparation method trialled on back-kitchen; only promoted if significantly faster

Recipe survives staff changes, seasonal ingredient swaps — consistent guest experience every visit

🔍 Query Baselines — Inspect Your SPM Store

-- View all baselines for a specific SQL
SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed,
       reproduced,
       origin,
       created,
       last_executed,
       description
  FROM  dba_sql_plan_baselines
 WHERE  sql_text LIKE '%your_table_or_keyword%'
 ORDER BY created DESC;

-- Pin the known-good plan (set FIXED=YES) — the "signature dish" lock
DECLARE
  l_cnt  PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle    => 'SQL_&handle_value',
             plan_name     => 'SQL_PLAN_&plan_value',
             attribute_name  => 'fixed',
             attribute_value => 'YES'
           );
  DBMS_OUTPUT.PUT_LINE('Plans altered: ' || l_cnt);
END;
/

-- Evolve (promote a new plan if it's provably 1.5x faster)
DECLARE
  l_rep  CLOB;
BEGIN
  l_rep := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
             sql_handle  => 'SQL_&handle_value',
             verify      => 'YES',
             commit      => 'YES'
           );
  DBMS_OUTPUT.PUT_LINE(l_rep);
END;
/

◆ Decision Gate — Does the Plan Perform? Did the Dish Pass QC?

◆ Quality Gate — Accept or Reject

✅ YES — Plan Accepted / Dish Passes

• Oracle: Accepted baseline plan used for all executions
• FIXED=YES pins it through upgrades and stats recomputes
• Monitor V$SQL_PLAN_MONITOR + DBA_HIST_SQLSTAT trends
• Auto-evolution promotes proven-better plans (1.5× rule)
• Kitchen: Expeditor approves — correct taste, temp, plating
• Dish dispatched to table within target SLA

⚠️ NO — Plan Regressed / Dish Rejected

• Oracle: Disable bad plan — DBMS_SPM.ALTER_SQL_PLAN_BASELINE
• FIXED=YES on last known-good plan — immediate protection
• Collect 10053 trace + AWR Diff Report for root cause
• Escalate: SQL Profile or SQLT hint injection
• Kitchen: Chef remakes — root cause logged (overcook / wrong mod)
• Waiter updates table; SLA breach flagged in shift log

-- Disable a bad/regressed baseline plan immediately
DECLARE
  l_cnt  PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle      => 'SQL_&handle_value',
             plan_name       => 'SQL_PLAN_&bad_plan',
             attribute_name  => 'enabled',
             attribute_value => 'NO'   -- disable the regressed plan
           );
  DBMS_OUTPUT.PUT_LINE('Disabled plans: ' || l_cnt);
END;
/

🎯 Step 04 — SQL Profile & The Specialist Seasoning

STEP 04

🎯

SQL Profile — Auxiliary Correction Object

Corrects cardinality errors · no SQL text change · stored by signature in SYSAUX

🔵 Oracle Concept

STA-generated: DBMS_SQLTUNE.EXECUTE_TUNING_TASK → ACCEPT_SQL_PROFILE on recommendation

Fixes stale statistics misestimates — optimizer receives corrected cardinality hints

Stored in SYSAUX by SQL signature — applies across environments, survives reparsing

Profile + Baseline together: profile corrects cardinality → baseline enforces plan shape

🍽️ Restaurant Analogy

Stale stats = chef misjudged how salty the stock was — dish is off, but the recipe itself is fine

SQL Profile = specialist's seasoning note clipped to the recipe card — corrects without rewrite

Seasoning note transfers between kitchens (UAT → PROD) like a transportable profile

Both seasoning note (profile) + official recipe (baseline) applied together = consistent dish

🔧 SQL Tuning Advisor — Create & Accept a SQL Profile

-- Step 1: Create a tuning task for the problematic SQL
DECLARE
  l_task  VARCHAR2(100);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
              sql_id        => '&your_sql_id',
              scope         => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
              time_limit    => 300,           -- 5 minutes
              task_name     => 'TUNE_&your_sql_id',
              description   => 'Profile for regressed SQL'
            );
  DBMS_OUTPUT.PUT_LINE('Task: ' || l_task);
END;
/

-- Step 2: Execute the tuning task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TUNE_&your_sql_id');

-- Step 3: Review the recommendation
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_&your_sql_id') FROM dual;

-- Step 4: Accept the SQL Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
       task_name    => 'TUNE_&your_sql_id',
       replace      => TRUE,
       force_match  => TRUE    -- apply across similar SQL with different literals
     );

-- Verify profile is active
SELECT name, sql_text, status, force_matching
  FROM  dba_sql_profiles
 WHERE  name LIKE 'SYS_SQLPROF%'
 ORDER BY created DESC;

📊 Step 05 — Logging, AWR & The Food Critic Review

STEP 05

📊

Performance Logging & Monitoring

AWR · ASH · SQL Monitor · EBS FND logs · DBMS_SCHEDULER alert on plan hash drift

🔵 Oracle Tools → 🍽️ Restaurant Analogy

AWR (DBA_HIST_SQLSTAT) = Nightly food critic score — compares elapsed_time_delta today vs. last week's baseline

ASH (V$ACTIVE_SESSION_HISTORY) = Table-side observer watching where time is spent — grill, plating, or waiting for ingredients?

SQL Monitor (V$SQL_PLAN_MONITOR) = Live kitchen CCTV — see exactly which station is bottlenecked right now, with row counts per step

EBS FND_LOG_MESSAGES / AD_CONCURRENT_PROGRAMS = Restaurant POS order log — request_id, elapsed, phase cross-referenced with kitchen KOT times

DBMS_SCHEDULER alert on PLAN_HASH_VALUE change = Automated alarm if today's dish takes 3× longer → kitchen manager alerted instantly

🔧 AWR Plan Hash Drift Detection Query

-- Detect plan hash changes for a SQL over the last 7 days
SELECT s.snap_id,
       TO_CHAR(sn.end_interval_time, 'DD-MON HH24:MI')   snap_time,
       q.plan_hash_value,
       ROUND(q.elapsed_time_delta / NULLIF(q.executions_delta,0) / 1e6, 2) elapsed_sec,
       q.executions_delta                                    execs,
       q.buffer_gets_delta
  FROM  dba_hist_sqlstat   q
  JOIN  dba_hist_snapshot  sn  ON sn.snap_id = q.snap_id
                                AND sn.dbid    = q.dbid
                                AND sn.instance_number = q.instance_number
 WHERE  q.sql_id            = '&your_sql_id'
   AND  sn.end_interval_time > SYSDATE - 7
 ORDER BY s.snap_id;

-- Real-time SQL Monitor for active long-running query
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id      => '&your_sql_id',
         report_level=> 'ALL',
         type        => 'TEXT'
       ) FROM dual;

🗂️ Key Dictionary Views — Quick Reference

DBA_SQL_PLAN_BASELINES

SQL_HANDLE · PLAN_NAME · ENABLED · ACCEPTED · FIXED · ORIGIN · CREATED · LAST_EXECUTED

DBA_SQL_PROFILES

NAME · STATUS · FORCE_MATCHING · CREATED · LAST_MODIFIED · SQL_TEXT

DBA_HIST_SQLSTAT

SQL_ID · PLAN_HASH_VALUE · SNAP_ID · ELAPSED_TIME_DELTA · BUFFER_GETS_DELTA · EXECUTIONS_DELTA

V$SQL_PLAN_MONITOR

SQL_ID · SQL_EXEC_ID · PLAN_LINE_ID · OUTPUT_ROWS · ELAPSED_TIME (real-time per step)

DBA_ADVISOR_FINDINGS

TASK_NAME · TYPE · MESSAGE · BENEFIT_TYPE — SQL Tuning Advisor recommendations

V$ACTIVE_SESSION_HISTORY

SQL_ID · EVENT · WAIT_CLASS · SESSION_STATE · SAMPLE_TIME — real-time ASH samples

📌 SPM Command Cheat Sheet

DBMS_SPM Procedure	🔵 Oracle Purpose	🍽️ Kitchen Equivalent
LOAD_PLANS_FROM_CURSOR_CACHE	Capture live plan from shared pool	Write down today's successful recipe
LOAD_PLANS_FROM_AWR	Recover historical plan from AWR	Dig out last month's kitchen log
ALTER_SQL_PLAN_BASELINE (FIXED=YES)	Hard-lock a plan — overrides optimizer	Head chef stamps recipe — no deviations
ALTER_SQL_PLAN_BASELINE (ENABLED=NO)	Disable a bad/regressed plan	Pull bad recipe off the menu immediately
EVOLVE_SQL_PLAN_BASELINE	Promote new plan if ≥1.5× faster	Upgrade recipe only after blind taste test proves it better
PACK_STGTAB_BASELINE	Export baselines to staging table	Print recipe book for other branch kitchens
UNPACK_STGTAB_BASELINE	Import baselines from staging table	New kitchen receives and adopts the recipe book
DROP_SQL_PLAN_BASELINE	Permanently delete a baseline	Remove discontinued dish from recipe archive

💡 Pro Tip — EBS 12.2 on Oracle 19c RAC

In EBS 12.2 environments, always capture baselines after a successful ADOP patch cycle on the run edition, not the patch edition. Plans captured on patch FS may differ due to the different APPS schema state. Use FIXED=YES sparingly — only for confirmed production-critical SQLs like concurrent program queries, AR/AP period-end batch SQLs, and MSC/VCP data collection queries where plan instability has caused SLA breaches.

✅ Summary — The Complete Analogy in One Line

SPM is Oracle's kitchen management system: the cursor cache is where the chef decides how to cook, capturing plans is writing the recipe down, the baseline is the approved recipe book (with FIXED=YES as the signature dish lock), the SQL Profile is the specialist's seasoning note that corrects the recipe without rewriting it, and AWR/ASH/SQL Monitor is your food critic, CCTV, and shift log — all feeding back into the next improvement cycle.

The beauty of SPM is that it enforces plan stability the same way a great kitchen enforces quality: no improvisation on the night of service. New ideas are always welcome, but they go through rigorous testing before they reach the guest's table.