今天公司碰到一个很倒霉的事,就是Kill的会话迟迟没有释放,导致全公司所有业务部分关联的表停滞,究其原因有很多,但主要原因个人认为:个别用户回话进程长期占用资源而不释放,导致Oracle进程占用了系统的大量资源,Oralce系统的效率变得很低。如果简单的关闭重启Oracle 实例,势必影响所有的用户。
Windows是一个基于线程的操作系统,而不是Linux基于进程的操作系统。整个Oracle的后台进程、用户进程等,在Windows 环境下,都包含在ORACLE.EXE这单独的一个体系进程中了,通过查看’任务管理器’――’进程’就可以看到。如果你不是使用MTS多线程服务器的模式,如果你Kill掉ORACLE.EXE这个进程,将导致整个Oracle实例关闭,如同使用Shutdown abort命令一样。
由于Windows自己没有提过一个专门用来Kill掉单个线程的工具,因此Oracle自己提供了一个基于字符界面的用来在Windows环境下强制Kill掉一个线程的工具――Orakill。
!!! 在Windows中如果使用alter system kill session 'sid,serial#'来清除会话,在执行之后该会话的状态会变为KILLED,但是有时候这个状态会保持很长时间,直到最后被清除。!!!
如果想更快地从内存中清理这个会话,那么可以在使用了alter system之后,再在Windows中使用Orakill实用程序(该程序随Oracle数据库同时安装)直接清除该会话的线程。
Orakill的使用方法如下:
Dos提示符下:>orakill sid thread
说明: sid-----Oracle的sid(SESSION_ID)号
thread-----Oracle的线程id(spid)号(select pro.spid from v$session ses,v$process pro where ses.sid=SID号 and ses.paddr=pro.addr;)
--查出死锁的详细信息:(包含:SID,SERIAL#,SPID,远端PC,执行的SQL语句,程序名
SELECT s.username,l.SESSION_ID as SID,s.SERIAL#,P.SPID,S.PROGRAM,S.MACHINE ,Q.SQL_FULLTEXT,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S,V$PROCESS P,V$SQL Q WHERE l.SESSION_ID=S.SID AND S.PADDR=P.ADDR AND
Q.HASH_VALUE=S.SQL_HASH_VALUE
--执行普通Kill
alter system kill session 'SID,SERIAL#'
--杀Linux进程
ps -ef|grep spid
--否则,在WIndows下执行杀Oracle线程:
orakill sid<Oracle实例> spid<前面查询语句的SPID信息>,例如:
set oracle_sid=YMS
orakill YMS 1142 --1142为SPID
还有其他原因需要优化,比如SQL调优。 但今天学到了如何彻底的在Windows下杀死Oracle线程的妙计。
一些官方指导:
一般情况下,在杀一个会话的时候,直接执行alter system kill session ‘sid,serial#’;
Killing Oracle Sessions
There are a number of ways to kill rogue sessions both within Oracle and externally.
- Identify the Session to be Killed
- ALTER SYSTEM KILL SESSION
- ALTER SYSTEM DISCONNECT SESSION
- The Windows Approach
- The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the [G]V$SESSION
and [G]V$PROCESS
views as follows.
SET LINESIZE 100 COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
SQL>
The SID
and SERIAL#
values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID
, shown when querying the GV$SESSION
view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION
command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE
clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC
value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.
ALTER SYSTEM DISCONNECT SESSION
The ALTER SYSTEM DISCONNECT SESSION
syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION
command which asks the session to kill itself, the DISCONNECT SESSION
command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION
command with the addition of the POST_TRANSACTION
clause. The SID
and SERIAL#
values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION
clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE
clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION
and IMMEDIATE
clauses can be used together, but the documentation states that in this case the IMMEDIATE
clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7'; alter system disconnect session '30,7' * ERROR at line 1: ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID
and SPID
values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID
into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID
matches the UNIX PROCESSID
shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
For more information see:
Hope this helps. Regards Tim...