OACORE

🔍 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;

📋 What to look for: Sessions with high 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;

📋 What to look for: If one OACORE machine shows a disproportionately high session count compared to others, that node may be overloaded or stuck. Check if WebLogic connection pool on that node needs recycling.

---

📌 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

---

💡 Side Note — result_cache_max_size
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;

Verify against your SGA allocation and Oracle Support Note for EBS 12.2 on 19c.

✅ Quick Investigation Workflow

1. Run Query 2 first — get the count/status snapshot per OACORE node
2. Run Query 3 — identify dominant wait events per node
3. Run Query 1 — drill into the actual SQL on ACTIVE sessions of the problem node
4. Cross-reference with module names to isolate whether the load is from UI users, concurrent programs, or integrations

Environment: Oracle EBS 12.2.x · Oracle DB 19c · RAC · Solaris 11.4 SPARC
Views Used: GV$SESSION · GV$SQL
Tags: Oracle EBS, Apps DBA, OACORE, JDBC, Performance Tuning, WebLogic, GV$SESSION