背景：数据库出现死锁会话飙升的情况通过下列预计可以快速定位常见的锁，快速干预处理，恢复数据库性能。通过下列语句长期运维?T以上数据库?个，屡试不爽。

一、查询出死锁的SID等信息

SELECT l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,s.terminal,o.object_name,s.logon_timeFROM v$locked_object l, all_objects o, v$session sWHERE l.object_id = o.object_id AND l.session_id = s.sidORDER BY sid, s.serial#;

二、根据SID定位阻塞语句

SELECT /+ PUSH_SUBQ / Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time, SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status FROM V$sqlarea WHERE Address = (SELECT Sql_Address FROM V$session WHERE Sid = ？ );

三、杀死锁

--杀死锁（数据库层次--适合不太紧急场合）select 'alter system kill session '||chr(39)||t2.sid||','||t2.serial#||chr(39)||'immediate;'from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time

--杀死锁（操作系统层次--适合紧急场合）

select 'kill -9 '||t3.spid from v$locked_object t1,v$session t2 , v$process t3where t1.session_id=t2.sid And t2.paddr = t3.addr order by t2.logon_time

附日常会话查询语句：

--所有会话信息Select From v$session

Select Count() From v$session--会话关键信息Select USERNAME,status,state,MACHINE,logon_time From V$SESSION Order By username,MACHINE