🔍 What Is Each OACORE Node Doing? — Oracle EBS JDBC Session Investigation
Oracle EBS 12.2 · Apps DBA · Performance Tuning · GV$SESSION
In Oracle EBS 12.2 environments, OACORE (Oracle Application Core) is the WebLogic managed server responsible for serving the core EBS application tier. Each OACORE node maintains a pool of JDBC connections into the database. When performance degrades or sessions spike, DBAs need to quickly identify what each OACORE node is actually doing — not just how many connections exist.
This post covers three focused SQL scripts to investigate OACORE JDBC sessions across all RAC instances using GV$SESSION and GV$SQL.
📌 Query 1 — Active SQL Being Executed Per OACORE Right Now
This is your first-response query. It joins GV$SESSION with GV$SQL to show the actual SQL text being executed by each active OACORE session at this moment.
set lines 200 pages 500 col machine for a30 col module for a40 col sql_text for a60 col status for a10 col username for a15 select s.machine, s.module, s.status, s.username, s.sid, s.serial#, s.last_call_et, substr(q.sql_text,1,60) sql_text from gv$session s, gv$sql q where s.program like '%JDBC%' and s.sql_id = q.sql_id and s.sql_child_number = q.child_number and s.status = 'ACTIVE' order by s.machine, s.last_call_et desc;
last_call_et (seconds since last DB call) indicate long-running transactions. The machine column tells you which OACORE node the session originated from.
📌 Query 2 — Session Count Breakdown Per OACORE Node and Module
This improved aggregation query gives you a breakdown by instance, OACORE node, module, and status — showing exactly how many sessions are active vs. waiting per node. Much more useful than a plain count(*).
select s.inst_id, s.machine, s.module, s.status, count(*) sessions, sum(case when s.state = 'WAITING' then 1 else 0 end) waiting, sum(case when s.status = 'ACTIVE' then 1 else 0 end) active from gv$session s where s.program like '%JDBC%' group by s.inst_id, s.machine, s.module, s.status order by s.machine, sessions desc;
📌 Query 3 — Wait Events Per OACORE Node
Knowing what sessions are waiting on is critical for diagnosing whether the bottleneck is I/O, locking, network, or CPU. This query filters out SQL*Net waits (normal idle waits) and surfaces the real bottlenecks per OACORE node.
select s.machine, s.event, count(*) cnt from gv$session s where s.program like '%JDBC%' and s.state = 'WAITING' and s.event not like 'SQL*Net%' group by s.machine, s.event order by s.machine, cnt desc;
Common Wait Events and Their Meaning
| Wait Event | What It Means | Action |
|---|---|---|
enq: TX - row lock contention |
Session blocked by another session's uncommitted DML | Find blocking session, consider killing or committing |
db file sequential read |
Single block I/O — index scans, possible FTS | Review execution plans, check for missing indexes |
db file scattered read |
Full table / index scans reading multiple blocks | Tune SQL, consider parallel or partitioning |
latch: shared pool |
Hard parsing pressure on shared pool | Enable cursor sharing, review literal SQL |
library cache lock |
DDL or recompilation blocking parse operations | Find DDL session and evaluate impact |
gc buffer busy acquire |
RAC interconnect contention — cross-instance block transfer | Review service affinity, check interconnect health |
⚠️ Bonus: Module Reference — What the Module Column Tells You
| Module Value | Meaning |
|---|---|
FNDCPGSC |
Concurrent Manager connections — Generic Service Controller |
Oracle Applications |
Standard EBS UI user sessions via Forms or OAF |
e-Business Suite |
Self-service / OAF framework sessions |
JDBC Thin Client |
Generic JDBC — third-party integrations, custom code |
null / blank |
Background or unregistered connection — worth investigating |
If your current
result_cache_max_size is around 184 MB (193298432 bytes) but Oracle 19c recommends 600M for EBS 12.2 environments, that is a tuning gap. You can update this dynamically:
alter system set result_cache_max_size = 600M scope=both;
✅ Quick Investigation Workflow
- Run Query 2 first — get the count/status snapshot per OACORE node
- Run Query 3 — identify dominant wait events per node
- Run Query 1 — drill into the actual SQL on ACTIVE sessions of the problem node
- Cross-reference with module names to isolate whether the load is from UI users, concurrent programs, or integrations
Views Used: GV$SESSION · GV$SQL
Tags: Oracle EBS, Apps DBA, OACORE, JDBC, Performance Tuning, WebLogic, GV$SESSION
