[Oracle] Kill Session experience

[Oracle] Kill Session experience is summarized in the daily maintenance of Oracle. We often encounter the following two situations that require DBA kill session: 1. the App complained that their application hang was in use 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 finish running 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 my 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 cannot be used, you can execute kill-9 at the OS level and use the above methods to frequently encounter problems: for sessions that have been running for a long time, it is not a matter of time to kill it. Sometimes it takes a long time to wait. At this time, it is only silly to wait there. When the App asks "does the session disappear? 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 and found a 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 statement: A105024 @ O02DMS1> alter system kill session '00000048 '; 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 those sessions that require a long rollback to be KILLED, after the alter system kill session is executed, its status changes to KILLED, for example: select username, status from v $ session where sid = 524; sid status ------------- ---------- 524 KILLED 2. how long does the KILLED session 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 actual 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 no USED_UBLK return, meaning the rollback has ended. 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 in the killed State: select username, status from v $ session where sid = 524; sid status -------------------- 524 KILLED on the client side, if you try to execute any statement, a ORA-00028 will appear: SQL> select instance_name from v $ instance; select instance_name fro M v $ instance * ERROR at line 1: ORA-00028: your session has been killed at this time session will disappear: select username, status from v $ session where sid = 524; no rows selected 4. the difference 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; kill-9 allows pmon to clear the session. Kill-9 is faster than alter system kill session because pmon performs some parallel processing to make rollback faster, but kill-9 is not recommended, because: 1. directly kill the process at the OS level, and there is an unknown risk; 2. misoperations are easy. If you kill key Oracle processes (such as smon), Database downtime may occur. 3. transaction rollback progress cannot be monitored. Therefore, do not use kill-9 unless you have.