Kill Oracle session

We know that in the Oracle database, a process can be terminated by kill session. The basic syntax structure is as follows:

Alter system kill session'Sid, serial #';

 

The kill session will be marked as killed, and Oracle will clear the process during the next touch of the user.

We found that when a session is killed, the paddr of the session is modified. If multiple sessions are killed, the paddr of multiple sessions is changed to the same process address:

SQL> select saddr, Sid, serial #, paddr, username, status from V $ session where username is not null;   Saddr Sid serial # paddr username status -------------------------------------------------------------------------- 542e0e6c 11 314 542b70e8 eygle inactive 542e5044 18 662 542b6d38 sys active     SQL> alter system kill session '123 ';   System altered.   SQL> select saddr, Sid, serial #, paddr, username, status from V $ session where username is not null;   Saddr Sid serial # paddr username status -------------------------------------------------------------------------- 542e0e6c 11 314 542d6bd4 eygle killed 542e5044 18 662 542b6d38 sys active     SQL> select saddr, Sid, serial #, paddr, username, status from V $ session where username is not null;   Saddr Sid serial # paddr username status -------------------------------------------------------------------------- 542e0e6c 11 314 542d6bd4 eygle killed 542e2aa4 14 397 542b7498 eqsp inactive 542e5044 18 662 542b6d38 sys active   SQL> alter system kill session '123 ';   System altered.   SQL> select saddr, Sid, serial #, paddr, username, status from V $ session where username is not null;   Saddr Sid serial # paddr username status -------------------------------------------------------------------------- 542e0e6c 11 314 542d6bd4 eygle killed 542e2aa4 14 397 542d6bd4 eqsp killed 542e5044 18 662 542b6d38 sys active

 

In this case, resources cannot be released. We need to query spids and kill these processes at the operating system level. however, due to the current V $ session. paddr has changed, so we cannot get the spid through the Association of V $ session and V $ process.

What else can we do?

Let's take a look at the following query in detail:

SQL> select S. username, S. status, 2 x. ADDR, X. ksllapsc, X. ksllapsn, X. ksllaspo, X. ksl1_1r, X. ksllrtyp, 3 decode (bitand (X. ksuprflg, 2), 0, null, 1) 4 From x $ ksupr X, V $ session s 5 where S. paddr (+) = x. ADDR 6 and bitand (ksspaflg, 1 )! = 0;     Username status ADDR ksllapsc ksllapsn ksllaspo ksl1_1r KS d ------------------------------------------------------------------------------------------- 542b44a8 0 0 0 Active 542b4858 1 14 24069 0 1 Active 542b4c08 26 16 15901 0 1 Active 542b4fb8 7 46 24083 0 1 Active 542b5368 12 12 15 24081 0 1 Active 542b5718 15 46 24083 0 1 Active 542b5ac8-79 4 15923 0 1 Active 542b5e78 50 16 24085 0 1 Active 542b6228 754 15 24081 0 1 Active 542b65d8 1 14 24069 0 1 Active 542b6988 2 30 14571 0 1   Username status ADDR ksllapsc ksllapsn ksllaspo ksl1_1r KS d ------------------------------------------------------------------------------------------- Sys active 542b6d38 2 8 24071 0 542b70e8 1 15 24081 195 EV 542b7498 1 15 24081 195 EV Sys inactive 542b7848 0 0 0 Sys inactive 542b7bf8 1 15 24081 195 EV   16 rows selected.

 

 

We note that the red text marks the process address of the killed process.

To simplify the process, the following concepts are used:

SQL> select P. ADDR from V $ PROCESS p where PID <> 1 2 minus 3 select S. paddr from V $ session S; ADDR -------- 542b70e8 542b7498

 

OK. Now we get the process address, and we can find the spid in V $ process. Then we can use kill or orakill to kill these processes at the system level.

Actually, I guess:

After kill a session in Oracle, Oracle simply points the paddr of the session to the same virtual address.

At this time, the V $ process and the V $ session are lost, and the process is interrupted.

Oracle then waits for pmon to clear these sessions. Therefore, it usually takes a long time to wait for a session marked as killed to exit.

If the kill process re-executes the task, the system immediately receives a message indicating that the process is interrupted. At this time, Oracle immediately starts pmon.

To clear the session. This is treated as an exception interrupt.

 

 

 

In many cases, a session is always active, so that the CPU is always in the active state. Although the session is killed, it cannot end the thread. The kill session is only a kill process, but the thread is always active. A real kill thread is required to solve the high CPU usage problem.

OS: Windows2003

Orakill is used by orakill Sid spid. Sid indicates the Instance name and spid indicates the thread number.

How can I get this spid ??

The following statements are used to search for spids.

Select spid, osuser, S. Program from

V $ PROCESS p, V $ session s where P. ADDR = S. paddr

And S. Sid = xxx (XXX is the session Sid. Input it by yourself .)

Orakill instance_name spid ---------- this command is executed on the operating system interface;

In this way, the CPU usage is high.