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;