Disconnect vs Kill

Printable View

08-05-2008, 12:46 PM
dbbyleo

Disconnect vs Kill

Could someone explain the difference between 'alter session kill ...' vs 'alter session disconnect ...'?

I had a database where connection from the app server exceed process limits. I found that many, many of the connection has been idle for over 200 hours.

So I decided to first 'kill' the sessions. I could then see in v$session that these sessions have a 'Killed', but they linger in the v$session. But also, I see all the O/S processes still there.

Then I decided to use 'disconnect'. This seemed to be really effective ... removing the session from v$session AND also removeing all the O/S process.

So I guess my reason question is ... why use 'kill' at all?
08-05-2008, 01:03 PM
davey23uk

kill removes al locks and stops any running processes, the session remains because it is still alive, it is a graceful way for the client to get a nice message
08-05-2008, 03:05 PM
dbbyleo

Do killed session eventially get "cleanup"? I mean ... long after the session is killed will the session stay in the v$session view? But more importantly, how long after will the O/S processes stay. I'm assuming as long as the O/S processes are there (when I do a ps -ef), then Oracle is registering that many current process, which mean it could still be affecting the process limit parameter. if that right?
08-06-2008, 01:27 AM
simply_dba

killed sessions remain as long as the rollback is incomplete.
08-06-2008, 03:07 AM
PAVB

Quote:

Originally Posted by dbbyleo

Do killed session eventially get "cleanup"? I mean ... long after the session is killed will the session stay in the v$session view? But more importantly, how long after will the O/S processes stay. I'm assuming as long as the O/S processes are there (when I do a ps -ef), then Oracle is registering that many current process, which mean it could still be affecting the process limit parameter. if that right?

In a perfect world after rollback is complete your session would get cleanup but in our imperfect world you might find that one or both of your Oracle Session and O/S Process appears to stay there forever.

One of the possible causes for this behavior works like this... Client got abort, DBA kills Oracle session, Oracle session remains in "KILLED" status -or is gone after a while, O/S process still out there -alive and kicking.

Usually you can solve this issue by tracing back the offending O/S Process -use PID and kill -9 PID them. In normal circumstances that would take care of both your Oracle session and your O/S process.
08-07-2008, 12:42 AM
gtcol

Quote:

Originally Posted by dbbyleo

Then I decided to use 'disconnect'. This seemed to be really effective ... removing the session from v$session AND also removeing all the O/S process.

So I guess my reason question is ... why use 'kill' at all?

I don't believe it always works, meaning 'disconnect' doesn't always remove the session from v$session. 'disconnect' just closes the connection the session is using; the v$session entry will not go away unless the rollback is complete or the session is killed on OS level.

gtcol