Info on Killing Session / Process

This is an answer given by Tom on Killing session:-


Hi Tom

When we do alter system kill 'sid, serial#'. Are we killing the user process or the 
server process? Because sometimes i see that after the session is killed the query is still running in the sessions even shows KILLED sometimes takes days to disappear! Sometimes we got some problems with large batch jobs in testing enviroment, we killed from sql plus but the job keeps on going!
What happens if we kill the server process with kill -9 instead of issueing alter system 
kill?


cheers
cheers 


Toms Answer:-

The job isn't "going on" it is actually "going back" -- it is rolling back.

If the job took an hour to get where it is, it might take even LONGER to "undo" what its done.

You can monitor how the killed session is doing as far as rolling back is concerned in v$transaction via the used_ublk column.


Consider this example. In a schema "big_table" i started a delete against a 1,000,000 row indexed table. Partway through -- I killed it. This is what you'll see (you need to be more selective on the query against v$transaction of course, I just had one active DML session going)

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

USED_UBLK
----------
5750

1 row selected.

it generated that much undo so far...

ops$tkyte@ORA817DEV.US.ORACLE.COM> @showsql

USERNAME SID_SERIAL STATUS MODULE ACTION 
--------------- --------------- ---------- --------------- --------------- 
OPS$TKYTE '7,665' ACTIVE 01@ showsql.sql 

BIG_TABLE '8,1082' ACTIVE SQL*Plus 


2 rows selected.

BIG_TABLE(8,1082) ospid = 13903 command = 7 program = sqlplus@aria-dev (TNS V1-V3) 
dedicated server=13904
Monday 10:24 Monday 10:38 last et = 822
delete from big_table

I kill it and get the message "it is marked for kill". That means the server heard us and is in the process of rolling back but it'll take a while

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system kill session '8,1082';
alter system kill session '8,1082'
*
ERROR at line 1:
ORA-00031: session marked for kill

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';

USERNAME STATUS
-------------------- ----------
BIG_TABLE KILLED

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

USED_UBLK
----------
5327

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

USED_UBLK
----------
5202

1 row selected.

we can watch the used_ublk shrink and even use that to estimate when it'll be done

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

USED_UBLK
----------
4470

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

USED_UBLK
----------
2914

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username 
= 'BIG_TABLE';

USERNAME STATUS
-------------------- ----------
BIG_TABLE KILLED

1 row selected.

session is still there...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

USED_UBLK
----------
2262

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

USED_UBLK
----------
1430

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

USED_UBLK
----------
1103

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username 
= 'BIG_TABLE';

USERNAME STATUS
-------------------- ----------
BIG_TABLE KILLED

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

USED_UBLK
----------
1048

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

USED_UBLK
----------
489

1 row selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

no rows selected

now we are done rolling back and...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username 
= 'BIG_TABLE';

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

the session is gone


If you kill -9, it might appear that the session is gone "faster" but not really. We still have to roll back (obviously). We might be doing on demand recovery of the blocks which makes it "appear" faster but we still have to recover the transaction. kill -9 is not something I recommend, especially if you use the above technique to see it rolling back.