|
背景：数据库出现死锁会话飙升的情况通过下列预计可以快速定位常见的锁，快速干预处理，恢复数据库性能。通过下列语句长期运维?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_time
FROM v$locked_object l,all_objects o,v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid
ORDER 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 t3
where t1.session_id=t2.sid And t2.paddr = t3.addr order by t2.logon_time
附日常会话查询语句：
--所有会话信息
Select From v$sessionSelect Count() From v$session--会话关键信息Select USERNAME,status,state,MACHINE,logon_time From V$SESSION Order By username,MACHINE
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
