Troubleshooting JDBC Session Performance in Oracle E-Business Suite

 

Deep Dive: Troubleshooting JDBC Session Performance in Oracle E-Business Suite

As an Apps DBA or Architect, one of the most common (and high-pressure) tickets you’ll receive is: "The application is slow, and we’ve identified a heavy Process ID (PID) on the database server."

In the world of Oracle EBS and middleware, JDBC connections are the lifeblood of the application tier. However, tracing an OS-level process back to a specific SQL statement or a blocking lock can be a needle-in-a-haystack operation.

This guide provides a structured, deep-dive approach to diagnosing and resolving JDBC performance bottlenecks using standard gv$session views.

---

Phase 1: From OS Process to SQL Text

When you have a Linux/Unix PID consuming high CPU, the first step is to bridge the gap between the Operating System and the Database.

1. Map the OS PID to JDBC Sessions

This query filters specifically for JDBC programs to isolate application tier traffic.

SQL

SELECT s.inst_id, s.sid, s.serial#, s.username, s.status, s.process, s.machine, 
       s.program, s.module, s.action, s.client_identifier, s.sql_id, 
       s.prev_sql_id, s.event, s.last_call_et
FROM gv$session s
WHERE s.process = '&OS_PID'
AND   s.program LIKE '%JDBC%'
ORDER BY s.last_call_et DESC;

2. Identifying the "Real" SQL_ID

Often, a session might be between calls, or the SQL_ID might appear null in gv$session while it transitions. We use a CASE statement to grab the PREV_SQL_ID if the current one is empty, ensuring we catch the last active statement.

SQL

SELECT s.inst_id, s.sid, s.sql_id, s.prev_sql_id,
       CASE
         WHEN s.sql_id IS NOT NULL THEN s.sql_id
         WHEN s.prev_sql_id IS NOT NULL THEN s.prev_sql_id
       END AS real_sql_id,
       s.event, s.last_call_et
FROM gv$session s
WHERE s.process = '&OS_PID'
AND   s.program LIKE '%JDBC%';

3. Extracting the SQL Text

Now, let’s join with gv$sql to see exactly what code is being executed. This is critical for identifying whether the load is coming from a standard EBS module or a custom OAF/ADF extension.

SQL

WITH sess AS (
    SELECT s.inst_id, s.sid, s.serial#, s.username, 
           CASE WHEN s.sql_id IS NOT NULL THEN s.sql_id ELSE s.prev_sql_id END AS real_sql_id
    FROM gv$session s
    WHERE s.process = '&OS_PID' AND s.program LIKE '%JDBC%'
)
SELECT sess.*, SUBSTR(q.sql_text,1,3000) AS sql_text
FROM sess
LEFT JOIN gv$sql q ON q.inst_id = sess.inst_id AND q.sql_id = sess.real_sql_id;

---

Phase 2: Monitoring Aggregate Activity

Sometimes the issue isn't a single PID, but a "storm" of JDBC connections hitting the same resource.

4. JDBC SQL Activity Summary

This provides a high-level view of which modules or SQL_IDs are hogging the most connections.

SQL

WITH sess AS (
    SELECT s.inst_id, s.status, s.client_identifier, s.module,
           CASE WHEN s.sql_id IS NOT NULL THEN s.sql_id ELSE s.prev_sql_id END AS real_sql_id
    FROM gv$session s
    WHERE s.program LIKE '%JDBC%'
)
SELECT NVL(real_sql_id,'NO SQL') AS sql_id, module, COUNT(*) AS session_count,
       SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_count
FROM sess
GROUP BY real_sql_id, module
ORDER BY session_count DESC;

5. Active Sessions & Wait Details

If the application is hanging, use this to see the WAIT_CLASS and EVENT. Is it User I/O (slow disks) or Application (locking)?

SQL

SELECT s.sid, s.serial#, s.username, s.event, s.wait_class, s.seconds_in_wait
FROM gv$session s
WHERE s.program LIKE '%JDBC%' AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

---

Phase 3: Resolving Contention and Execution Plans

If your session is stuck, it’s usually because someone else is holding the door shut.

6. Finding the Blocker

This query creates a direct map between the "victim" (blocked) and the "culprit" (blocker).

SQL

SELECT blocked.sid AS blocked_sid, blocker.sid AS blocker_sid,
       blocker.username AS blocker_user, blocker.sql_id AS blocker_sql_id,
       blocked.event AS blocked_event
FROM gv$session blocked
JOIN gv$session blocker ON blocker.sid = blocked.blocking_session 
                       AND blocker.inst_id = blocked.blocking_instance
WHERE blocked.blocking_session IS NOT NULL;

7. Performance Deep Dive & Execution Plan

Once you have the SQL_ID from the steps above, check its historical performance and its current execution plan.

Performance Metrics:

SQL

SELECT executions, elapsed_time/1000000 AS elapsed_sec, buffer_gets, disk_reads
FROM gv$sql WHERE sql_id = '&SQL_ID';

Execution Plan:

SQL

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

---

Phase 4: Remediation

If a session is identified as a runaway process and has been cleared by the business owners for termination, use this generator to ensure you kill the correct session across RAC instances.

8. Kill Session Command Generator

SQL

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id || ''' IMMEDIATE;' AS kill_command
FROM gv$session
WHERE sid = '&SID' AND inst_id = '&INST_ID';

Architect's Note: Always investigate the Root Cause before killing. Is it a missing index? Stale statistics? Terminating a session is a band-aid; fixing the SQL or the locking logic is the cure.