Friday, May 8, 2026

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.

Saturday, April 18, 2026

Oracle EBS 12.2 — Morning Health Check Checklist

Apps DBA EBS 12.2.x Monitoring Fresher + Experienced

🩺 Oracle EBS 12.2 — The Daily Morning Health Check
Every Apps DBA Must Run (Fresher to L4)

Environment: Oracle EBS 12.2.x · Oracle DB 19c · Solaris 11.4 SPARC · RAC · WebLogic 12c
Audience: Freshers joining Apps DBA teams + Experienced DBAs building a structured morning routine
Time to execute: 20–30 minutes · Do this before the first business user logs in

If you are a fresher stepping into an Oracle Apps DBA role for the first time, the first question you will face every morning is: "Is the environment healthy?" If you are an experienced DBA, you probably run a mental checklist already — but is it consistent, documented, and defensible if something goes wrong at 9 AM?

This post gives you a structured, repeatable morning health check — with the exact commands, SQL scripts, and warning signs — covering all critical layers of Oracle EBS 12.2: Database, Application tier, Concurrent Manager, WebLogic, RMAN, and OS-level checks on Solaris.

Bookmark it. Run it every morning. Your on-call stress will drop significantly.

📋 What This Post Covers

Why a structured health check matters (and what happens without one)
Layer 1 — Database tier: Alert log, tablespace, sessions
Layer 2 — Application tier: EBS services, autoconfig, fs1/fs2
Layer 3 — Concurrent Manager and batch jobs
Layer 4 — WebLogic / AdminServer health
Layer 5 — RMAN backup status
Layer 6 — OS and Solaris checks
The complete morning checklist (print-ready)
Quick-reference scripts in one place

1. Why This Matters — The Cost of No Morning Check

Consider this scenario: a DBA starts their shift at 8 AM. No health check. At 10 AM, a user calls — EBS login is broken. Investigation reveals the OHS process crashed at 6:45 AM. The concurrent manager has been rejecting jobs since 7:00 AM. RMAN backup failed last night but nobody noticed.

A 20-minute morning health check at 8 AM catches all three of these before the business starts. It also gives you a baseline snapshot — so if something breaks at noon, you know exactly what changed since morning.

💡 For Freshers: Your manager will ask "did you check the environment this morning?" A documented health check means your answer is always "yes — and here is what I found." This single habit differentiates a junior DBA from a reliable one within your first 90 days.

⚠️ For Experienced DBAs: If your morning check is informal and in your head, it is not repeatable by your team across APAC / EMEA / Americas shifts. Document it, standardise it, share it. That is the difference between individual knowledge and team resilience.

2. Layer 1 — Database Tier

2.1 — Check the DB Alert Log (First Priority, Always)

The DB alert log is the single most important file in your EBS environment. Check it before anything else. Any ORA- error here is a potential incident.

🎓 Fresher Explanation

The alert log is a running diary that Oracle writes for every database event — startup, shutdown, errors, checkpoints, log switches. It lives on the DB server (not the app server). Your first job every morning is to read the last 200 lines and look for anything that starts with ORA-.

# Find the alert log path (Solaris)
find $ORACLE_BASE/diag/rdbms -name "alert_*.log" 2>/dev/null

# Tail the last 200 lines
tail -200 $ORACLE_BASE/diag/rdbms/<DBNAME>/<SID>/trace/alert_<SID>.log

# Filter for ORA- errors only (the quick check)
grep "ORA-" $ORACLE_BASE/diag/rdbms/<DBNAME>/<SID>/trace/alert_<SID>.log | tail -30

# Check for errors from last 24 hours only
awk '/'"$(date -d 'yesterday' '+%a %b')"'/,0' alert_<SID>.log | grep "ORA-"

ORA- Error	What It Means	Priority
`ORA-00600`	Internal Oracle error — raise SR immediately	🔴 P1
`ORA-07445`	Exception encountered — core dump likely	🔴 P1
`ORA-04031`	Shared pool / SGA memory insufficient	🔴 P1
`ORA-01555`	Snapshot too old — UNDO retention issue	🟡 P2
`ORA-00060`	Deadlock detected — check application logic	🟡 P2
`ORA-01652`	TEMP tablespace full	🟡 P2
`ORA-00257`	Archiver error — archive log destination full	🔴 P1

2.2 — Tablespace Usage

A tablespace hitting 90%+ before business hours starts is a disaster waiting to happen. EBS SYSTEM, APPS_TS_TX_DATA, APPS_TS_SEED, and UNDOTBS are the critical ones.

-- Tablespace usage — flag anything above 85%
SELECT
    df.tablespace_name,
    ROUND(df.total_mb) total_mb,
    ROUND(df.total_mb - NVL(fs.free_mb, 0)) used_mb,
    ROUND((1 - NVL(fs.free_mb, 0) / df.total_mb) * 100, 1) pct_used,
    df.autoextend
FROM
    (SELECT tablespace_name,
            SUM(bytes) / 1048576 total_mb,
            MAX(CASE autoextensible WHEN 'YES' THEN 'YES' ELSE 'NO' END) autoextend
     FROM   dba_data_files
     GROUP  BY tablespace_name) df,
    (SELECT tablespace_name, SUM(bytes) / 1048576 free_mb
     FROM   dba_free_space
     GROUP  BY tablespace_name) fs
WHERE  df.tablespace_name = fs.tablespace_name (+)
AND   (1 - NVL(fs.free_mb, 0) / df.total_mb) * 100 > 75
ORDER  BY pct_used DESC;

⚡ Experienced DBA Note

On EBS 12.2 with online patching, APPS_UNDOTS1 can spike dramatically during ADOP apply phase. If you see it above 80% on a patch day morning — proactively extend before the patch window opens, not during it.

2.3 — Active Session Count and Long-Running Queries

-- Session overview: active vs inactive
SELECT inst_id, status, COUNT(*) sessions
FROM   gv$session
WHERE  type = 'USER'
GROUP  BY inst_id, status
ORDER  BY inst_id, status;

-- Long-running queries (over 10 minutes)
SELECT
    s.inst_id, s.sid, s.serial#, s.username,
    s.module, s.status,
    ROUND(s.last_call_et / 60, 1) mins_running,
    SUBSTR(q.sql_text, 1, 60) sql_text
FROM   gv$session s, gv$sql q
WHERE  s.sql_id = q.sql_id
AND    s.status = 'ACTIVE'
AND    s.last_call_et > 600
AND    s.type = 'USER'
ORDER  BY s.last_call_et DESC;

3. Layer 2 — Application Tier: EBS Services

3.1 — Are All EBS Services Running?

EBS 12.2 has a specific service start order. If any service is down when users arrive, you will face immediate calls. Check this from the application tier server as the applmgr user.

🎓 Fresher Explanation

Oracle EBS 12.2 runs as multiple services layered on top of each other: OHS (web entry point) → WebLogic (Java tier) → Database. If OHS is down, users see a browser error. If WebLogic is down, they see an EBS error page. If the Concurrent Manager is down, batch jobs stop silently. You need to check all three every morning.

# Check all EBS services status (run as applmgr)
source /path/to/<CONTEXT_NAME>.env
$ADMIN_SCRIPTS_HOME/adstatus.sh

# Check individual service status
$ADMIN_SCRIPTS_HOME/adopmnctl.sh status   # OHS / Oracle HTTP Server
$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh status  # WebLogic AdminServer
$ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh status  # WLS Managed Servers
$ADMIN_SCRIPTS_HOME/adcmctl.sh status    # Concurrent Manager

# Quick process check on Solaris
ps -ef | grep -E "httpd|java|FNDLIBR|FNDSM" | grep -v grep

3.2 — Verify the Correct Filesystem is Active (fs1 / fs2)

This is EBS 12.2 specific and catches more DBAs off guard than anything else. After ADOP patching, the run edition switches between fs1 and fs2. Always confirm which is active.

# Check which filesystem is the current run edition
cat $EBS_DOMAIN_HOME/applications/EBSapps_instance/controlfile/adinfo_*.xml | \
    grep -i "run_edition_home"

# Or directly from the context file
grep "s_current_base" $CONTEXT_FILE

⚠️ Watch out for this: If ADOP cleanup did not complete after the last patch cycle, both fs1 and fs2 may show as partially configured. Run adop phase=status to confirm the current ADOP state before assuming the environment is clean.

4. Layer 3 — Concurrent Manager (CM)

The Concurrent Manager is the heartbeat of EBS batch processing. If it is not running, or if requests are piling up in Pending/Standby status, business processes stop silently — and users notice hours later.

4.1 — CM Manager Status

-- Check all manager statuses
SELECT
    c.concurrent_queue_name       manager_name,
    c.user_concurrent_queue_name  display_name,
    c.enabled_flag,
    r.running_processes,
    r.max_processes,
    r.node_name
FROM   fnd_concurrent_queues_vl c,
       fnd_concurrent_queue_size r
WHERE  c.concurrent_queue_id = r.concurrent_queue_id (+)
AND    c.application_id      = r.application_id (+)
ORDER  BY c.concurrent_queue_name;

4.2 — Pending and Stuck Requests (The Most Common Morning Issue)

-- Count of requests by phase and status
SELECT
    phase_code  phase,
    status_code status,
    COUNT(*)    cnt
FROM   fnd_concurrent_requests
WHERE  phase_code IN ('P', 'R')   -- Pending and Running
GROUP  BY phase_code, status_code
ORDER  BY phase_code, status_code;

-- Requests stuck Pending for more than 30 minutes
SELECT
    r.request_id,
    r.concurrent_program_name  program,
    p.user_concurrent_program_name display_name,
    r.requested_start_date,
    ROUND((SYSDATE - r.requested_start_date) * 1440) mins_pending,
    r.phase_code, r.status_code
FROM   fnd_concurrent_requests r,
       fnd_concurrent_programs_vl p
WHERE  r.concurrent_program_id = p.concurrent_program_id (+)
AND    r.phase_code  = 'P'
AND    r.status_code = 'I'  -- Normal (Pending/Normal = stuck)
AND    (SYSDATE - r.requested_start_date) * 1440 > 30
ORDER  BY r.requested_start_date;

⚡ Experienced DBA Note

If you see a large number of Pending/Standby requests with no active ICM (Internal Concurrent Manager) process, the Conflict Resolution Manager has likely stalled. Check the ICM log at $APPLCSF/$APPLLOG/ for FNDLIBR errors before bouncing the CM. A blind bounce without reading the ICM log will not fix the root cause.

5. Layer 4 — WebLogic / AdminServer Health

WebLogic AdminServer memory exhaustion is one of the most common EBS 12.2 production issues — especially in environments where the heap size was not tuned after initial installation. A quick morning check takes 60 seconds and prevents emergency 2 AM restarts.

5.1 — AdminServer Process and Memory

# Check AdminServer JVM heap usage (Solaris)
ps -ef | grep AdminServer | grep -v grep | \
    awk '{print "PID:", $2, "RSS(MB):", $6/1024}'

# Check WLS process and port (AdminServer default: 7001)
netstat -an | grep 7001

# Tail the AdminServer log for recent errors
tail -100 $DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log | \
    grep -iE "error|exception|OutOfMemory|BEA-"

5.2 — JDBC Connection Pool Check (via WLST)

# Quick WLST snippet to check JDBC pool health
cd $FMW_HOME/wlserver/common/bin
./wlst.sh <<EOF
connect('weblogic','<password>','t3://<host>:7001')
serverRuntime()
cd('JDBCServiceRuntime/<server_name>/JDBCDataSourceRuntimeMBeans')
ls()
EOF

💡 If AdminServer RSS memory (from ps) is above 3 GB and climbing steadily every morning, your heap settings need tuning. Standard recommendation for EBS 12.2: set -Xms2048m -Xmx4096m in setDomainEnv.sh and schedule a monthly AdminServer restart in a low-traffic window.

6. Layer 5 — RMAN Backup Status

If last night's backup failed and you did not catch it this morning, you are running production EBS without a current backup. That is an unacceptable risk. This check takes 2 minutes.

-- Check last backup status (last 24 hours)
SELECT
    session_key,
    input_type,
    status,
    TO_CHAR(start_time, 'DD-MON-YYYY HH24:MI') start_time,
    TO_CHAR(end_time,   'DD-MON-YYYY HH24:MI') end_time,
    ROUND(elapsed_seconds / 60) elapsed_mins,
    ROUND(output_bytes / 1073741824, 2) output_gb
FROM   v$rman_backup_job_details
WHERE  start_time > SYSDATE - 1
ORDER  BY start_time DESC;

-- Archive log gap check (critical for standby or recovery)
SELECT
    sequence#, first_time, next_time, applied, deleted
FROM   v$archived_log
WHERE  first_time > SYSDATE - 1
AND    standby_dest = 'NO'
ORDER  BY sequence#;

RMAN Status	Meaning	Action
`COMPLETED`	Backup successful	No action needed ✅
`COMPLETED WITH WARNINGS`	Backup ran but some files skipped	Check output — usually safe, but investigate
`FAILED`	Backup did not complete	Check RMAN log immediately, re-run or escalate
No rows returned	Backup never ran or scheduler issue	Check RMAN script cron job — treat as FAILED

7. Layer 6 — OS-Level Checks (Solaris 11.4 SPARC)

7.1 — Disk and Mount Point Health

# Disk usage — flag anything above 85%
df -h | awk 'NR==1 || $5+0 > 85'

# Check NFS mounts are still alive (critical in EBS)
mount | grep nfs
df -k | grep -v Filesystem | awk '$6 ~ /nfs/'

# Check for zombie or stuck processes
ps -ef | awk '$8 == "Z"'

# Archive log destination — must not be above 80%
df -h /u03/archivelogs  # adjust path to your arch dest

7.2 — System Load and Memory

# Current system load average (1, 5, 15 min)
uptime

# Memory usage on Solaris
prtconf | grep "Memory size"
vmstat 1 3   # watch 'si/so' for swapping — must be 0 on healthy system

# Check for any core dumps from last 24 hours
find /var/core -name core -newer /tmp -ls 2>/dev/null

⚠️ Solaris-specific: On SPARC systems, vmstat columns differ from Linux. Watch the sr (scan rate) column — if it is consistently above 200 and pi/po (page in/out) is non-zero, the system is under memory pressure. For EBS, this will show up as slow Forms loading and WebLogic response delays before it becomes obvious to users.

8. The Complete Morning Checklist (Print-Ready)

Use this as your daily sign-off. Document your results in your shift handover notes every morning.

📋 DB Alert Log — No new ORA- errors since last check
💾 Tablespace Usage — All critical tablespaces below 85%
🔒 UNDO / TEMP — Not exhausted, autoextend functioning
🔗 Archive Log Destination — Below 80%, no gaps in sequence
👥 Active Sessions — No unexpected spike; no long-runners (>10 min)
🌐 EBS Services — OHS, AdminServer, Managed Servers all UP
📂 fs1/fs2 Edition — Run edition confirmed; no dangling ADOP phase
⚙️ Concurrent Manager — ICM running; no Pending/Standby queue buildup
☕ WebLogic AdminServer — No OutOfMemoryError in last 24 hrs
💿 RMAN Backup — Last backup status: COMPLETED
🖥️ Disk Space (OS) — No filesystem above 85%
🔗 NFS Mounts — All EBS-related NFS mounts alive
📊 System Load — Load average within normal range; no swapping

✅ You are done. If all 13 checks are green, the environment is healthy and you can start your shift with confidence. Document your check in the shift handover — even two lines is enough: "Health check complete 08:15. All services up. RMAN backup completed. No ORA- errors. One tablespace at 83% — monitoring."

9. One Shell Script to Run Everything

Here is a lightweight shell script that combines the OS-level checks into a single morning summary. Run it as applmgr from the app tier. Add DB checks to cron separately (requires DB connectivity).

#!/bin/ksh
# ebs_morning_check.sh
# Run as: applmgr user on application tier
# Oracle EBS 12.2.x — Morning Health Summary

source /path/to/<CONTEXT_NAME>.env

echo "═══════════════════════════════════════"
echo "  EBS MORNING HEALTH CHECK — $(date '+%d-%b-%Y %H:%M')"
echo "═══════════════════════════════════════"

echo "\n📂 DISK USAGE (>70% shown):"
df -h | awk 'NR==1 || ($5+0) > 70 {print}'

echo "\n🔗 NFS MOUNTS:"
mount | grep nfs | awk '{print " ✓", $1, "→", $3}'

echo "\n⚙️  EBS SERVICES:"
$ADMIN_SCRIPTS_HOME/adstatus.sh 2>/dev/null | grep -iE "running|stopped|dead"

echo "\n📊 SYSTEM LOAD:"
uptime

echo "\n🔍 ALERT LOG - Last ORA- errors:"
grep "ORA-" $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log 2>/dev/null | \
    tail -5

echo "\n═══════════════════════════════════════"
echo "  Check complete. Run SQL checks from DB server."
echo "═══════════════════════════════════════"

10. Summary

A structured morning health check is not optional in a 24×7 EBS production support role — it is professional table stakes. For freshers, it gives you a daily framework that builds environmental awareness faster than anything else. For experienced DBAs, documenting it makes your knowledge reproducible across shifts and geography.

Run it consistently. Document your findings. Your 9 AM incidents will become 8 AM catches — and your manager will notice the difference before any appraisal conversation starts.

💡 Next in this series: Oracle EBS 12.2 ADOP Phase Failures — How to Diagnose and Recover Without a Full Rollback · Oracle Apps DBA — Freshers' Guide to Your First Clone Operation

Tags: Oracle EBS Apps DBA EBS 12.2.x Health Check Monitoring Concurrent Manager WebLogic RMAN Solaris Fresher Guide Shell Script SQL ADOP RAC

Friday, May 8, 2026

Monitoring the Production

🏗️ Understanding the Flow — Where Do Users Connect?

🏷️ Tags

OACORE Monitoring

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

📌 List All OACore Processes with CPU and Memory

📌 Heap Usage Summary — RSS in MB

📌 Real-Time GC Log Monitoring

🔗 2. Active OACore Sessions & Connections — Database Side

📌 Current OACore Sessions Hitting the Database

📌 Session Count Breakdown per App Node

🏊 3. JDBC Connection Pool Saturation

🐌 4. Long-Running & Blocking SQL from OACore

📌 Top SQLs by Elapsed Time from OACore Sessions

📌 Stuck OACore Queries (> 60 Seconds)

⏳ 5. Wait Events & Contention from OACore

📌 Top Wait Events — Real-Time Bottleneck Detection

📌 Enqueue / Lock Contention — Blocking Chains

📊 6. OACore Managed Server & Thread Status

📌 DMS Metrics via SQL

📌 Thread Dump for Stuck Thread Analysis

🔓 7. Shared Pool & Cursor Pressure

📋 8. Quick Health Dashboard — Single Pane Query

🚨 Key Thresholds & Red Flags

🏷️ Tags

SQL Plan Management Explained Via Kitchen Analogy

🍽️ SQL Plan Management: Explained via Restaurant Kitchen Analogy

🗺️ The Complete Analogy Map

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

🔍 Key V$SQL Query — Find Heavy SQLs

📡 Step 02 — Capturing Plans & Writing the Recipe

🔧 Load Plans — Auto Capture + Manual Bulk Load

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

🔍 Query Baselines — Inspect Your SPM Store

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

🎯 Step 04 — SQL Profile & The Specialist Seasoning

🔧 SQL Tuning Advisor — Create & Accept a SQL Profile

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

🔧 AWR Plan Hash Drift Detection Query

🗂️ Key Dictionary Views — Quick Reference

📌 SPM Command Cheat Sheet

✅ Summary — The Complete Analogy in One Line

Saturday, April 18, 2026

Oracle EBS 12.2 — Morning Health Check Checklist

🩺 Oracle EBS 12.2 — The Daily Morning Health CheckEvery Apps DBA Must Run (Fresher to L4)

1. Why This Matters — The Cost of No Morning Check

2. Layer 1 — Database Tier

2.1 — Check the DB Alert Log (First Priority, Always)

2.2 — Tablespace Usage

2.3 — Active Session Count and Long-Running Queries

3. Layer 2 — Application Tier: EBS Services

3.1 — Are All EBS Services Running?

3.2 — Verify the Correct Filesystem is Active (fs1 / fs2)

4. Layer 3 — Concurrent Manager (CM)

4.1 — CM Manager Status

4.2 — Pending and Stuck Requests (The Most Common Morning Issue)

5. Layer 4 — WebLogic / AdminServer Health

5.1 — AdminServer Process and Memory

5.2 — JDBC Connection Pool Check (via WLST)

6. Layer 5 — RMAN Backup Status

7. Layer 6 — OS-Level Checks (Solaris 11.4 SPARC)

7.1 — Disk and Mount Point Health

7.2 — System Load and Memory

8. The Complete Morning Checklist (Print-Ready)

9. One Shell Script to Run Everything

10. Summary

🍽️ SQL Plan Management:
Explained via Restaurant Kitchen Analogy

🩺 Oracle EBS 12.2 — The Daily Morning Health Check
Every Apps DBA Must Run (Fresher to L4)