I am facing an issue from past few days in my application which is runnning for few months now: I have configured connection pool in WebSphere Application Server with no of maximum connections to 20, min connection 1, reap time 180 sec, unused time 1800 sec, Aged timeout 0 sec in data source(oracle) for my application. From past few days we are seeing an issue where suddenly in mornings we start getting timeouts in application which then follows a strange behavior where we don't get any exception but Callable Statement used to run a procedure return zero results for all executions even though data is there in DB.
There were no exceptions in System Out or System Err logs at the time when issue starts occuring. Only exception we could see was one Stale Connection Exception and a database session kill exception
java.sql.SQLRecoverableException: ORA-00028: your session has been killed
ORA-00028: your session has been killed
But this happened 6 hours before the timeouts in application.
Also On checking the no of connections in database we can see only 9 sessions were there although we have configured connection pool to 20. Used below query to check TOTAL_CON:
SELECT s.machine
, s.username
, count(decode(s.STATUS, 'ACTIVE', 1)) as active_con
, count(decode(s.STATUS, 'INACTIVE', 1)) as inactive_con
, count(*) as total_con
FROM v$session s
WHERE type <> 'BACKGROUND'
GROUP BY username, machine
ORDER BY total_con DESC;
We did a WAS server restart and that resolved the issue and also increased the no of sessions in database to 20.
So I have 2 questions: 1. what can cause ORA-00028 from application side as database team says no session was killed from their side. 2. Is it normal that no of total sessions in database are less than no of max connections configured in datasource and if not what can cause this.