Is there any way to make Oracle clear dead connections faster. Sometimes, DBMS_SESSION.IS_SESSION_ALIVE says that the session is alive even when the session has crashed.
PMON wakes up regularily to clear the resources held by dead users. Can we make it do that more often. Is there a parameter for this..??
session inactive DOES NOT MEAN session is dead.
(probably only killed or not found session is really gone)
(Try to use sqlnet feature 'dead connection detection' to set timeout for oracle to marke session dead when client connection is dead.)
The issue here is that it is difficult to define what is dead session. One definition comes to timeout (like in sqlnet's dead connection feature), but then what has to be the timeout: too long is bad, but to small risks to produce wrong results.
So it is better to reduce UNEXPECTED session crashes or impact the crashes has on the system.
For example, if locking of resources by crashed sessions is a concern, then moving processes to PL/SQL at DBengine or to midle tire is a good option. A 7x24 project I am involved has no DML/COMMITS/ROLLBACKS at client app. at all - everything is done on server side - no problems because of dead client happens. There is a problem regarding locking of rows for update for concurent access by different users: try to minimize such issues if possible or provide some facilities to identify the locking session and allow to kill the dead session manually.
I had already tried the SQLNET feature of dead connection detection. I tried setting SQLNET.EXPIRE_TIME, but that does not seem to help. The delay which we see is still there. It takes Oracle some time to recognize that the session is dead and returning a FALSE from DBMS_SESSION.IS_SESSION_ALIVE.
Yes, as you righly pointed out and INACTIVE session does not mean that it is dead. We are only concerned with applications which have crashed.
Actually in our application we are using logical locking of rows. When a user takes a row for updating, we update a column in the row which signifies the sessionid of the person who has locked that row. When that user does a save the sessionid in the row is cleared. If that user crashes in between then that session id is left in that row. So when some other person tries to do something to that row and finds a sessionid there, we check to see if that session id is still alive(That is where DBMS_SESSION.IS_SESSION_ALIVE comes into picture) to check if the lock is a legitimate one.
Now the actual problem faced by us is that DBMS_SESSION.IS_SESSION_ALIVE still returns TRUE for sessions which have crashed for for as long as 5 minutes after the crash sometimes. I was looking if there was any way by which Oracle allows you to tune the PMON into doing this clearing more frequently.
Regarding logical locking of rows:
0. PMON iswaking every 3 secs, but only every 20 wakeup it checks for dead sessions. On unix folks advise to kill the dead process unders consideration with kill cmd and then PMON will clear it quite soon...
1. dbms_lock can be used to have non-transactional locks, but I think they will not be released faster than is_session_alive returns session is not alive.
2. I quess that your users are not scattered all arround a world, but sit in one control room. If this is the case, then they can found out that application crashed and use FORCE UNLOCK feature (which has to be coded)
3. if point 2 is not OK, then consider to implement checking of dead client: kind of whatchdog.
In a simple form it could be client application's timer generating eg. dbms_alerts every 15 secs or so. In case a long query is running or so, the v$sesion status = active can be checked before deciding on action.