为何kill掉session后，server列变成pseudo

为何kill掉session后，server列变成pseudo  

2011-11-09 10:59:20|  分类：Oracle Tuning |字号 订阅

转自：http://unix-cd.com/vc/www/16/2007-10/9283.html
现象
When an inactive session is killed via the alter system kill session command,
the process continues in v$session as KILLED and program as PSEUDO.

  Select username,sid,serial#,status from v$session;

  USERNAME                              SID    SERIAL# STATUS
  ------------------------------ ---------- ---------- --------
  SYSTEM7                                         1964 ACTIVE
  SCOTT                                  10       1087 INACTIVE
  KARI                                   11        106 INACTIVE
  --> alter system kill session '11,106';
  USERNA SPID      PROCESS   STATUS   PROGRAM          PROGRAM
  ------ --------- --------- -------- ---------------  ---------------
  KARI             299:232   KILLED   D:\WINNT\Profil  PSEUDO
                                    es\All Users\St
                                      art Menu\Programs\

原理

When an inactive session has been terminated, STATUS in the view V$SESSION is
"KILLED." ,but its row in the v$session view is not removed until you try to
use that session again and "ORA-00028 your  session has been killed" is reported,
then it is removed.  Until that point, it is marked as killed in the status column
 and pseudo in the server column.

If an active session cannot be interrupted (for example, it is performing
network I/O or rolling back a transaction), the session cannot be terminated
until the operation completes.  In this case, the session holds all resources
until it is terminated.  Additionally, the session that issues the ALTER SYSTEM
statement to terminate a session waits up to 60 seconds for the session to
be terminated; if the operation that cannot be interrupted continues past
one minute, the issuer of the ALTER SYSTEM statement receives a message
indicating that the session has been "marked" to be terminated. A session
marked to be terminated is indicated in V$SESSION with a status of "KILLED"
and a server that is something other than PSEUDO i.e. dedicated,shared, is a active sessions performing a network I/O or rolling back a transaction.

NOTE:  Killing the pseudo process (seen at the O/S) can lead to an orphan process
process in Oracle as well.   We recommned killing the Oracle process (found in
V$PROCESS) first and then kill at the O/S if necessary.

杀掉session
You can shutdown and restart the database or use the ORAKILL utility to kill
threads.

Oracle has provided an ORAKILL utility that will kill shadow threads.  Each
user's connection is represented by a thread in the Oracle process.  If a
user's session is killed, then their Oracle session is killed - not the thread.
Oracle has provided an ORAKILL utility which can be passed a thread ID and will
kill the specified thread.

To make sure you do not kill a background process (which would crash your
database), you must perform a select to ensure you get the correct thread.

---
select p.spid "OS Thread", b.name "Name-User", s.osuser, s.program
   from v$process p, v$session s, v$bgprocess b
      where p.addr = s.paddr
      and p.addr = b.paddr UNION ALL
          select p.spid "OS Thread", s.username "Name-User", s.osuser, s.program
             from v$process p, v$session s
                where p.addr = s.paddr
                and s.username is not null;
---
      
This will list all Shadow processes and backgound processes.
Each shadow process will show the thread ID - this is what must be killed via
the ORAKILL utility.

PMON进程

what happens to a session while it is in the KILLED PSEUDO state?

What's happening is that PMON periodically checks to see if any sessions have been killed. If it finds one, it attempts to rollback the transaction for that session
(that was in progress when it was killed).  The reason this can take a long time is
because PMON may have more than one transaction to rollback at a time
(if other sessions have been killed, or if processes have died etc).
Thus, it may take a while to finally cleanup the killed session and have it
disappear from the session monitor.  The system i/o monitor correctly shows
the reads and writes being performed by PMON in order to rollback the
session's transaction.
      
PMON will not delete the session object itself until the client connected to
that session notices that it has been killed.  Therefore, the sequence of
events is:
      
1) alter system kill session is issued - the STATUS of the session object in
   V$SESSION becomes KILLED, its server becomes PSEUDO.
      
2) PMON cleans up the *resources* allocated to the session
   (i.e., rolls back its transaction, releases its locks, etc).
      
3) the entry in V$SESSION remains there until the client of that session (the
   client is the process associated with the OSUSER,MACHINE,PROCESS columns in
   the V$SESSION view) tries to do another request.
      
4) the client attempts another SQL statement and gets back ORA-28.
      
5) PMON can now remove the entry from V$SESSION.
   This behavior is necessary because the client still has pointers to the
   session object even though the session has been killed.  Therefore, the
   object cannot be deleted until the client is no longer pointing at it.