Oracle – My KILL’ed Session Continues To Stay On In KILLED Status, Why?

 

The v$session view

If you do not know already, to check who is connected and since when, what their connection status is etc., you would use dynamic view v$session.

The view has the following columns:

SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, USERNAME, COMMAND,
OWNERID, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME,
OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS,
SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH,
ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#,
ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, LOGON_TIME, LAST_CALL_ET,
PDML_ENABLED, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER,
RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS,
CURRENT_QUEUE_DURATION, CLIENT_IDENTIFIER

You would kill the required session by issuing

ALTER SYSTEM KILL SESSION 'sid,serial#';

Note: The IMMEDIATE option of the above command just returns control to you immediately. There is no difference in the underlying KILL operation and its after-effects!

2 Reasons why a session stays in KILLED status

The view has a lot of useful information. In this post, we are not so much concerned with the view itself but the status displayed by the view for an underlying session.

In particular, when a session is killed by a DBA, the session’s STATUS changes to KILLED. When you query v$session for your SID & SERIAL# (which uniquely constitute a session), the status may remain in “KILLED” status. There are a couple of reasons why this is happens

1. The client (e.g., SQL*Plus) was not closed or did not issue anything to reconnect with the database, at which point Oracle would have said “ORA-00028: your session has been killed”
2. The session is KILLED but PMON (Oracle background process – Process Monitor), has to cleanup and the work might involve rolling back changes

If you are trying to perform operations that involve getting an exclusive lock on the object(s) involved, you may get this error:
ORA-00054: resource busy and acquire with NOWAIT specified

More about why a session stays in KILLED status

So, how do you get around the error or make the entry in v$session with the “KILLED” status disappear? It depends on which of the two situations is causing the entry to stay on.

• If the reason for session “hanging around” in “KILLED” status is because of #1 above:
• Close the client software that you requested your DBA to kill. Alternatively, try issuing another command that tries to make a connection to the database. This should result in the error – ORA-00028: your session has been killed.
• If the client S/W is not on your PC but on a server for example, close or kill the OS process associated with the session.
• After doing the above, if the session is still showing up in v$session with “KILLED” status, it is because of #2 above
• You have to wait until Oracle finishes the rollback and cleanup necessary

If the reason is #2, the following query, which shows UNDO usage by session should show the usage by the session in KILLED status.

SELECT s.username,
       s.SID,
       s.serial#,
       s.logon_time,
       t.xidusn,
       t.ubafil,
       t.ubablk,
       t.used_ublk,
       t.start_time AS txn_start_time,
       t.status,
       ROUND (t.used_ublk * TO_NUMBER (x.VALUE) / 1024 / 1024, 2) || ' Mb'
          "Undo"
  FROM v$session s, v$transaction t, v$parameter x
WHERE s.saddr = t.ses_addr AND x.name = 'db_block_size'

What if I restart the instance?

I can tell you right away that you are making the problem worse by restarting. It still has to ROLLBACK and bring the objects involved to a consistent state before letting them go. By restarting, you will be interfering and interrupting that and adding more time delays!

Can you do anything about it? No, just sit tight and monitor!

As PMON cleanup/rollback continues, the size of UNDO used by the session should keep going down. When it gets to zero, yes, you guessed it – your session in KILLED status will disappear from v$session. Use this in coordination with the other tip from before that showed how to query the overall UNDO usage at the system level.

The serial# is also a key part of the query here when you monitor. Oracle reuses SID values. For example, session with SID 17 that a few minutes ago, was running a query and then disconnected, can have the SID value be used by another session but with a different Serial#. Until the next database restart, SID+Serial# combination will remain unique.

Can I do something to be proactive? Absolutely

Yes, the DBA can tell you what statement is being executed or where the connection is made from etc, but he may not know what application the session belongs to. You could help your DBA by using DBMS_APPLICATION_INFO.SET_CLIENT_INFO in your entry-point procedure of your application to set the CLIENT_INFO so that it shows up as part of the v$Session.client_info column and the DBA can then tell you that such and such an application is the one whose ROLLBACK is in progress. There are also additional instrumentation options available in the package that should be fully taken advantage of.

PROCEDURE DBMS_APPLICATION_INFO.SET_CLIENT_INFO
    (client_info IN VARCHAR2);

Assuming you here have instrumented your code, v$session.client_info column has the name of the application. To find all the sessions for an application(s), simple issue this query:

SELECT * FROM v$session
WHERE client_info IS NOT NULL;

In my organization, I instituted the instrumentation part so that everyone knows exactly what is running.

When you request us to kill a session, you can be more knowledgeable about your request by first looking the session status and then giving the DBA’s the SID/Serial#. Once killed, you can monitor when the session has been completely flushed out of the system or if it is not, you now know why!

Further Reading: https://oracle-base.com/articles/misc/killing-oracle-sessions

Advertisement