DBMS_SESSION.IS_SESSION_ALIVE

Hi all,

We have a requirement where we want to check if a user session is valid or not. For this we are doing it as follows :

When a person logs on through our app, we select its session_id as follows :

select dbms_session.unique_Session_id from dual;

We store this in a session table.

Now when admin app needs to chech whether all the entries in the session table are still valid we do it as follows :

dbms_Session.is_session_alive(uniquesessionid)

Question : This works but sometimes, there is some delay(event 10 min sometimes). Oracle still returns the session as alive even after the app has crashed.

1. Is there a method by which to tune this, so that Oracle detects the dead connections quickly..?
2. Is the method we have chosen above the best way to do it..?

Thanks in advance
regards
Puneet Sachdev

Hi,

If u need to check the status of a session , query the status column in the V$session view. It will show "active" when the session is active or else it shows "killed" or "inactive".

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..??

Be careful. V$SESSION returns inactive for connections made through web-enabled forms run through OAS, even for active connections.

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.