今天在群里和MECKEY讨论KILL SESSIONS的问题，结果群里出先了两派，一是我和大家都同意先试着KILL SESSION然后再KILL SPID，二是MECKEY坚持说应该先KILL SPID，然后再KILL SESSION，并且还真找出了一个英文文档，说是从METALINK上下的，下面就是这段资料，虽然这段资料和MECKEY的说法不完全一样，但也有参考价值，共同参考：
Solution Description:
====================
As part of its responsibilities, PMON cleans up old process connections -
connections where the parent process terminated abnormally, connections that
have been unintentionally interrupted (as with network failures), and
connections that Oracle has terminated due to an internal problem.
Remember, connection information is stored in the views V$SESSION and
V$PROCESS. As part of this cleanup, PMON willonly rollback a certain number
of transactions for a given connection. This number is determined by the
CLEANUP_ROLLBACK_ENTRIES parameter in the INIT.ORA file. For example, if a
process goes rogue and generates, say, 1000 uncommitted updates to the
database, it will rollback CLEANUP_ROLLBACK_ENTRIES transactions each time its
periodic timer expires. By default, this value is 20.
This means that it will rollback 20 updates each wake-up period. At the rate
of 20 each time, and a wake-up period of 60 seconds (at 3 seconds, it cleans
up the connections latches and at 60 it cleans up the remaining stuff), it
could take PMON as long as 50 minutes to rollback all of the entries.
Meanwhile, all of the locks the remaining updates hold are still in effect,
preventing other users from updating those rows.
Since PMON has other database maintenance responsibilities, it takes even
longer to get through all 1000 of the updates. So, to help PMON (not to
mention yourself) get through the updates as quickly as possible, you can add
this parameter to your INIT.ORA file. As with anything, this must be handled
carefully. By allowing PMON to rollback more updates each time around its
processing loop, it will take longer to get around to working on its other
responsibilities - i.e. processing other users requests. So, there is a
performance consideration.
How does this relate to killing a session? If you were to kill a process
(using alter system kill session) similar to theone in the above example,
Oracle will keep the connection information around until ALL of the updates
made through the connection have been cleaned up - i.e. until all outstanding
transactions performed by the rogue process has been cleaned up. This is done
to maintain the relationship between the creator and the manipulated data (we
don't want to rollback the wrong information). If Oracle can cleanup a
connection quicker, then the connection information can be removed from the
V$SESSION and V$PROCESS tables sooner.
PLEASE NOTE:
============
If you must kill a session and want the connection information to go away
(without bouncing your instance), the order of events is important. Since
Oracle has a mechanism for dead connection detection and connection cleanup,
make this happen first - i.e. kill the users process first. Then, wait for 3
to 4 minutes beyond the dead connection detection value and query V$SESSION
for the connection information.
If the information is still there, query the V$LOCK table to see if the
connection has any outstanding locks (PMON hasn't finished cleaning up the
users information) with the following query: SELECT COUNT(*) FROM V$LOCK WHERE
SID='sid', where 'sid' is the sid identified in V$SESSION for the connection.
This count will be WAY more than the number of actual updates (an insert
generates two locks -one DML andone for the transaction).
The point is if this is a nonzero value, PMON hasn't finished rolling back the
users updates. If you perform this query again after a few minutes (PMON has
had at least a couple of iterations to clean up the users data), you should
see the count getting smaller. If the value isn't getting smaller, the next
step is to user ALTER SYSTEM KILL SESSION to delete the connection at the
database level. Perform the same steps again - query V$SESSION, then V$LOCK,
and see if progress is being made in removing the connection information.
If the count from V$LOCK is zero and the users connection information has not
gone away, then theonLY way this information will be removed is be taking the
instance down.
ANOTHER NOTE:
If the session is first killed with ALTER SYSTEM KILL SESSION and the
corresponding users process does not terminate normally, then the killed
session will continue to appear in V$SESSION during the life of the instance.
The reason for this is that the session state object cannot be cleaned out
until the associated user process clears the pointer to it. Since the session
abnormally aborted, this will not happen.
Reference documentation:
Solution Description:
====================
As part of its responsibilities, PMON cleans up old process connections -
connections where the parent process terminated abnormally, connections that
have been unintentionally interrupted (as with network failures), and
connections that Oracle has terminated due to an internal problem.
Remember, connection information is stored in the views V$SESSION and
V$PROCESS. As part of this cleanup, PMON willonly rollback a certain number
of transactions for a given connection. This number is determined by the
CLEANUP_ROLLBACK_ENTRIES parameter in the INIT.ORA file. For example, if a
process goes rogue and generates, say, 1000 uncommitted updates to the
database, it will rollback CLEANUP_ROLLBACK_ENTRIES transactions each time its
periodic timer expires. By default, this value is 20.
This means that it will rollback 20 updates each wake-up period. At the rate
of 20 each time, and a wake-up period of 60 seconds (at 3 seconds, it cleans
up the connections latches and at 60 it cleans up the remaining stuff), it
could take PMON as long as 50 minutes to rollback all of the entries.
Meanwhile, all of the locks the remaining updates hold are still in effect,
preventing other users from updating those rows.
Since PMON has other database maintenance responsibilities, it takes even
longer to get through all 1000 of the updates. So, to help PMON (not to
mention yourself) get through the updates as quickly as possible, you can add
this parameter to your INIT.ORA file. As with anything, this must be handled
carefully. By allowing PMON to rollback more updates each time around its
processing loop, it will take longer to get around to working on its other
responsibilities - i.e. processing other users requests. So, there is a
performance consideration.
How does this relate to killing a session? If you were to kill a process
(using alter system kill session) similar to theone in the above example,
Oracle will keep the connection information around until ALL of the updates
made through the connection have been cleaned up - i.e. until all outstanding
transactions performed by the rogue process has been cleaned up. This is done
to maintain the relationship between the creator and the manipulated data (we
don't want to rollback the wrong information). If Oracle can cleanup a
connection quicker, then the connection information can be removed from the
V$SESSION and V$PROCESS tables sooner.
PLEASE NOTE:
============
If you must kill a session and want the connection information to go away
(without bouncing your instance), the order of events is important. Since
Oracle has a mechanism for dead connection detection and connection cleanup,
make this happen first - i.e. kill the users process first. Then, wait for 3
to 4 minutes beyond the dead connection detection value and query V$SESSION
for the connection information.
If the information is still there, query the V$LOCK table to see if the
connection has any outstanding locks (PMON hasn't finished cleaning up the
users information) with the following query: SELECT COUNT(*) FROM V$LOCK WHERE
SID='sid', where 'sid' is the sid identified in V$SESSION for the connection.
This count will be WAY more than the number of actual updates (an insert
generates two locks -one DML andone for the transaction).
The point is if this is a nonzero value, PMON hasn't finished rolling back the
users updates. If you perform this query again after a few minutes (PMON has
had at least a couple of iterations to clean up the users data), you should
see the count getting smaller. If the value isn't getting smaller, the next
step is to user ALTER SYSTEM KILL SESSION to delete the connection at the
database level. Perform the same steps again - query V$SESSION, then V$LOCK,
and see if progress is being made in removing the connection information.
If the count from V$LOCK is zero and the users connection information has not
gone away, then theonLY way this information will be removed is be taking the
instance down.
ANOTHER NOTE:
If the session is first killed with ALTER SYSTEM KILL SESSION and the
corresponding users process does not terminate normally, then the killed
session will continue to appear in V$SESSION during the life of the instance.
The reason for this is that the session state object cannot be cleaned out
until the associated user process clears the pointer to it. Since the session
abnormally aborted, this will not happen.
Reference documentation: