Research on Kill session in Oracle (from Eagle)

Itpub Link:

Http://www.itpub.net/235873.html

We know that in an Oracle database, a process can be terminated by a kill session with the basic syntax structure:

Alter system kill session 'sid,serial#';

When the session is killed, the status will be marked as killed,oracle the process will be cleared when the user next touch.

We find that when a session is killed, the paddr of the session is modified and if multiple sessions are killed, then multiple sessions
Paddr are changed to the same process address:

Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null; Saddr SID serial# paddr USERNAME STATUS----------------------------------------- ---------------------------------542e0e6c 11 314542b70e8Eygle inactive542e5044 662 542b6d38 SYS activesql&gt ; Alter system kill session ' 11,314 '; System altered. Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null; Saddr SID serial# paddr USERNAME STATUS----------------------------------------- ---------------------------------542e0e6c 11 314542d6bd4 Eygle killed542e5044 662 542b6d38 SYS activesql> Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null; Saddr SID serial# paddr USERNAME STATUS-----------------------------------------  ---------------------------------542e0e6c 11 314542d6bd4  eygle killed542e2aa4 397 542b7498  eqsp INACTIVE5 42E5044 662 542b6d38 SYS activesql> alter system kill session ' 14,397 '; System altered. Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null; Saddr SID serial# paddr USERNAME STATUS----------------------------------------- ---------------------------------542e0e6c 314 542d6 BD4  eygle killed542e2aa4 397 542d6bd4  eqsp killed542e5044 662 542b6d38 SYS ACTIVE    

In this case, most of the time, resources cannot be freed, and we need to query the SPID to kill these processes at the operating system level.

But because V$SESSION.PADDR has changed at this point, we cannot get the SPID through v$session and v$process associations.

What else can I do?

Let's look at the following query:

  Sql> SELECT S.username,s.status, 2 X.addr,x.ksllapsc,x.ksllapsn,x.ksllaspo,x.ksllid1r,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 ksllid1r KS D----------------------- --------------------------------------------------------------------542b44 A8 0 0 0 ACTIVE 542b4858 1 14 240    0 1 ACTIVE 542b4c08 26 16 15901 0                               1 ACTIVE 542b4fb8 7 46 24083 0 1 Active 542b5368 24081 0 1 active 542B 5718 15 46 24083 0 1 ACTIVE 542B5AC8 79 4 15923 0                               1 ACTIVE 542b5e78 50 16 24085 0 1 Active 542b6228 754 24081 0 1 active 542b6         5d8 1 24069 0 1 ACTIVE 542b6988 2 14571 0 1USERNAME STATUS ADDR ksllapsc ksllapsn ksllaspo Kslli                            D1R KS D-------------------------------------------------------------------------------------------SYS ACTIVE 542b6d38 2 8 24071 0 542b70e8 1 24081 195 EV 542b7498 1 24081 195 EVSYS inacti         VE 542b7848 0 0 0SYS INACTIVE 542b7bf8 1          24081 195 EV16 rows selected. 

We note that the red mark is the process address of the process that was killed.

Simplifying a point is actually the following concept:

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 that we have the process address, we can find the SPID in v$process, and then we can use kill or orakill to kill these processes at the system level.

In fact, I guess:

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

At this point v$process and v$session lose relevance, and the process is interrupted.

Oracle then waits for Pmon to clear the sessions. So it usually takes a long time to wait for a session that is marked as killed to exit.

If the process is killed at this time, re-attempt to perform the task, you will immediately receive a prompt for progress interrupt, and process exits, at which time Oracle will start Pmon
To clear the session. This is handled as an exception interrupt.

-the end-

Research on Kill session in Oracle (from Eagle)