Oracle pl/sql 死锁解决办法
查询当前数据库锁的sql:
Select a.Session_Id, c.Serial#, a.Locked_Mode, b.Object_Id, b.Object_Name, b.Object_Type, c.Logon_Time, a.Oracle_Username, a.Os_User_Name, b.Owner, a.Process From V$locked_Object a, Dba_Objects b, V$session c Where b.Object_Id = a.Object_Id And a.Session_Id = c.Sid;
select 'alter system kill session ''' ||a.session_id||','||c.serial#||''''
from v$locked_object a, dba_objects b, v$session c
where b.object_id = a.object_id
and a.session_id = c.sid
and b.owner='BZFZ_YC_IA';
查询造成锁的SQL:
Select Sql_Text From V$session, V$sqltext_With_Newlines Where Decode(V$session.Sql_Hash_Value, 0, Prev_Hash_Value, Sql_Hash_Value) = V$sqltext_With_Newlines.Hash_Value And V$session.Sid = #session_Id# Order By Piece; --其中 #SESSION_ID# 为锁的会话ID --如果确定为死锁,可以使用下面语句杀死死锁
杀掉死锁:
ALTER SYSTEM KILL SESSION '对应SID,对应SERIAL'; ALTER SYSTEM DISCONNECT SESSION '对应SID,对应SERIAL' IMMEDIATE;
1.查看总消耗时间最多的前10条SQL语句:
Select * From (Select v.Sql_Id, v.Child_Number, v.Sql_Text, v.Elapsed_Time, v.Cpu_Time, v.Disk_Reads, Rank() Over(Order By v.Elapsed_Time Desc) Elapsed_Rank From V$sql v) a Where Elapsed_Rank <= 10;
2.查看CPU消耗时间最多的前10条SQL语句:
Select * From (Select v.Sql_Id, v.Child_Number, v.Sql_Text, v.Elapsed_Time, v.Cpu_Time, v.Disk_Reads, Rank() Over(Order By v.Cpu_Time Desc) Elapsed_Rank From V$sql v) a Where Elapsed_Rank <= 10;
3.查看消耗磁盘读取最多的前10条SQL语句:
Select * From (Select v.Sql_Id, v.Child_Number, v.Sql_Text, v.Elapsed_Time, v.Cpu_Time, v.Disk_Reads, Rank() Over(Order By v.Disk_Reads Desc) Elapsed_Rank From V$sql v) a Where Elapsed_Rank <= 10;