[Oracle] Kill session experience

During routine Oracle maintenance, DBAs often need to kill sessions in the following two situations:
1. The app complained that their application had hung and found that their session was blocked by other sessions in the database. In this case, the other sessions should be killed so that the app can run out in time.

2. When the app is halfway through, it suddenly decides not to run. The DBA needs to kill their session.

The following is a frequently used kill session method:

1. At the database level, use the statement alter system kill 'sid, serial #'

2. In some special cases, if method 1 is unavailable, kill-9 is executed at the OS level.

The problem that is often encountered when using the above method is: for those sessions that have been running for a long time, they cannot be killed at once. Sometimes they have to wait for a long time. At this time, they can only be silly, when the app asks "is the session gone? If it does not disappear, how long will it take ?" I can't give them the exact answer when I wait for a question, until today I read an article in ask Tom.ArticleThen find the solution.

Http://asktom.oracle.com/pls/asktom/f? P = 100: 11: 0: p11_question_id: 4974573906087

1. Alter system kill 'sid, serial # 'What will this statement do?

When you execute the following statements:

A105024 @ o02dms1> alter system kill session '2017 48 ';

System altered.

The result of system altered is always returned quickly. Many people mistakenly think that the session has been killed at this time. In fact, this statement is just a command to tell the session: you are killed. If the session is idle at this time, it can end immediately, but if the session is doing a transaction at this time, it needs to roll back the transaction before it ends, and release the lock.

Therefore, for sessions that require a long rollback to be killed, after the alter system kill session is executed, the state of the session becomes killed, for example:

Select username, status from V $ session where Sid = 524;

Sid status

-------------------------

524 killed

2. How long does it take for a session in the killed state to disappear?

How long does it take to evaluate the killed state of a session to disappear? We can query used_ublk in V $ transcation. For example, if the current value of used_ublks is 400 and 400 is left after 1 minute, You can estimate that this session will require about 500/(400) = 4 minutes to end. The following is an example:

Select used_ublk from V $ transaction;

Used_ublk

----------

2262

1 row selected.

SQL>/

Used_ublk

----------

1430

1 row selected.

SQL>/

Used_ublk

----------

1103

1 row selected.

Always monitor the value of used_ublk, as long as it is not equal to 0, the session is still in the killed status:

Select username, status from V $ session where Sid = 524;

Sid status

-------------------------

524 killed

Select used_ublk from V $ transaction;

Used_ublk

----------

1048

1 row selected.

SQL>/

Used_ublk

----------

489

1 row selected.

SQL>/

No rows selected

If no used_ublk is returned, the rollback is completed.

3. Why the used_ublk value is already 0, and the session still displays the killed status?

This is because the session will disappear after the client receives the ORA-00028 error, in other words, even if the session has been rolled back, but if the client has not confirmed, the session is still in the killed State, for example:

Select used_ublk from V $ transaction;

No rows selected

Although used_ublk is already 0, the session is still killed:

Select username, status from V $ session where Sid = 524;

Sid status

-------------------------

524 killed

On the client side, attempting to execute any statement will result in a ORA-00028:

SQL> select instance_name from V $ instance;

Select instance_name from V $ instance

*

Error at line 1:

ORA-00028: Your session has been killed

In this case, the session will disappear:

Select username, status from V $ session where Sid = 524;

No rows selected

4. Differences between kill-9 and alter system kill session

The principle of alter system kill session is to let the session kill itself, that is, to roll back the session itself; and kill-9 is to let the pmon clear the session.

Kill-9 seems faster than alter system kill session because pmon performs some parallel processing to make rollback faster,

However, kill-9 is not recommended because:

1. directly kill the process at the OS level, with unknown risks;

2. misoperations are easy. If key Oracle processes (such as SMON) are killed, database downtime may occur;

3. The transaction rollback progress cannot be monitored.

Therefore, do not use kill-9 unless you have.