Home > Troubleshooting > Identifying PID/SPID for killed session in 11g

Identifying PID/SPID for killed session in 11g

Many times I  killed session with alter system kill session command ( or Toad )
and then session would just hang without dying.Then when I tried to find which OS process is related to killed session I could not identify it directly by using v$session and v$process view and joining them using addr column.According to Oracle this is expected.Here is explanation:

When a session is killed, the session state object(and all the child state objects under the session state object) move out from under the original parent process state object, and are placed under the pseudo process state object (which is expected, given the parent/child process mechanism on Unix). PMON will clean up all the state objects found under the pseudo process state object. That explains why PADDR changes in V$SESSION when a session is killed. New PADDR you are seeing in v$SESSION is the address of the pseudo process state object. This shows up in system state under PSEUDO PROCESS for group DEFAULT: V$PROCESS still maintains the record of the original parent process.

So to finally get rid of that session I had to use different workarounds.One of the workaround was to eliminate PSEUDO processes.

select spid, program
from v$process where program != ‘PSEUDO’
and addr not in
(select paddr from v$session);  and of course avoid killing background processes

select spid, program from v$process
where program!= ‘PSEUDO’
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess);

Thanks to 11g  there are two new columns in v$session view :

CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process

CREATOR_ADDR is the column that can be joined with the PADDR column in V$PROCESS to uniquely identify
the killed process corresponding to the former session.

So new query would look like:

select * from v$process where addr=(select creator_addr from v$session where sid=< sid used in alter system kill session command >);

In addition to these columns there are views

V$DETACHED_SESSION

V$PROCESS_GROUP

which can help in resolving this issue.

About these ads
Categories: Troubleshooting
  1. Dion Cho
    July 29, 2009 at 3:39 am

    Miladin.

    Nice finding and thanks for the finding.

  2. July 29, 2009 at 2:42 pm

    Miladin,

    Excellent finding.

    Thanks
    Ittichai

  3. July 29, 2009 at 3:34 pm

    :)

  4. August 4, 2009 at 2:41 pm

    Great, this is a really missed feature in pre-11g.

  5. Shriram
    August 16, 2009 at 8:06 am

    Excellent info, Thanks a lot!

  6. Vipen Koul
    September 17, 2009 at 1:40 am

    If you run

    select spid, program from v$process
    where program!= ‘PSEUDO’
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess);

    or the other equivalent query

    you also get 2 or more processes listed as D000 and S000, which are dispatcher and shared server process, did you kill those ?

  7. Aswin Shankar
    May 17, 2012 at 11:43 pm

    That was really great it helped a lot please keep posting .

  1. July 29, 2009 at 3:22 am
  2. August 1, 2009 at 1:53 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 48 other followers

%d bloggers like this: