- 打开ProcessExplorer,找到oracle进程
- 双击oracle进程,如图所示,打开"Threads"页签,按照cpu进行降序排列,观察该线程对应的TID和Service
- Service内填写的是以"OracleService"开头的即为Oracle服务的线程,记录下它TID
- 在sqlDeveloper中执行sql脚本,查找TID对应的sql语句(SQL_TEXT),判断该线程是否是因为sql语句造成cpu大量资源占用,若不是则查找另外的线程
select * from v$sql
where sql_id in (
select sql_id from gv$session where paddr in (
select addr from gv$process where program =
'ORACLE.EXE (SHAD)'
and spid = XXXX(TID对应的值)
)
);
- 若该线程是我们所需要清除的线程,则执行以下脚本,查找该sql对应的sid和SERIAL#
select gv$session.* from gv$session where paddr in (
select addr from gv$process where program =
'ORACLE.EXE (SHAD)'
and spid = XXXX(TID对应的值)
)
- 根据查找到的sid和SERIAL#对该sql对应的session进行清除,还原cpu资源
ALTER SYSTEM KILL SESSION
'XXXX(sid对应的值),XXXX(SERIAL#对应的值)'
;
ProcessExplorer下载地址:http://pan.baidu.com/s/1jG9T83c