lock等待排查

1. 查看当前数据库阻塞会话,并生成kill session语句

prompt 查看当前数据库阻塞会话
set line 500
set pages 1000
set verify off
col level for 99
col username for a20
col sql_id for a20
col status for a10
col event for a40
col wait_class for a20
col program for a30
column osuser format a10
column machine format a25
column logon_time format a20

WITH s AS (SELECT * FROM gv$session)
    SELECT LEVEL,
           LPAD (' ', (LEVEL - 1) * 5, ' ') || NVL (s.username, '(oracle)')
              AS username,
           s.inst_id,
           s.osuser,
           s.sid,
           s.serial#,
           s.type,
           s.lockwait,
           s.machine,
           s.program,
           TO_CHAR (s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
           s.sql_id,
           s.sql_exec_id,
           s.sql_exec_start,
           s.status,
           s.event,
           s.wait_class,
           s.seconds_in_wait,
           s.prev_sql_id,
           s.prev_exec_id,
           s.prev_exec_start,
           s.blocking_session,
           s.blocking_session_status,
           'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||s.inst_id || ''' immediate;' kill_session_script
      FROM s
     WHERE    LEVEL > 1
           OR EXISTS
                 (SELECT 1
                    FROM s s2
                   WHERE s2.blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

2. 通过 v$access 查看正在访问某个对象的session，并生成kill语句

SELECT /*+ rule */
       DISTINCT
       s.username,
       s.inst_id,
       s.sid,
       s.serial#,
       s.TYPE,     -- 执行kill时注意类型是否后台进程
       s.osuser,
       s.status,
       s.event,
       s.program,
       s.machine,
       S.EVENT,
       s.sql_id,
       s.sql_exec_id,
       S.SQL_EXEC_START,
       s.prev_sql_id,
       s.prev_exec_id,
       s.prev_exec_start,
       S.FINAL_BLOCKING_SESSION,
       S.FINAL_BLOCKING_SESSION_STATUS,
       s.logon_time,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ',@'|| s.inst_id|| ''' immediate;' kill_session_script,
       'kill -9 ' || p.spid kill_process_script
  FROM gv$session s, gv$access a, gv$process p
 WHERE     s.inst_id = a.inst_id
       AND s.sid = a.sid
       AND p.addr = s.paddr
       AND s.TYPE = 'USER'  ---过滤掉后台进程
       AND a.object = UPPER ('mes_laser_iface_pkg')   ---输入object_name
       --and event like '%event_name%' and status = 'ACTIVE';
   
----           

SELECT /*+rule+*/
         'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate;' kill_script
        FROM gv$session s
 WHERE     s.sid IN (SELECT /*+rule */
                           SID
                       FROM gv$access
                      WHERE object = UPPER ('TBLODMSNINFO_TEMP'))  ---object_name
       AND TYPE = 'USER';
----
SELECT/*+rule*/ * FROM v$access t WHERE t.OWNER='VMANAGE' AND OBJECT='GET_REFERENCECMP_F';

3. 查看对象被哪个session锁住，锁的类型，session正在执行的sql

prompt 查看锁住某个对象的会话
  SELECT p.inst_id,
         p.spid,
         p.BACKGROUND,
         l.session_id sid,
         s.serial#,
         s.username,
         s.osuser,
         s.status,
         s.event,
         o.object_name,
         l.locked_mode,
         s.program,
         s.logon_time,
         s.sql_id
    FROM gv$process p,
         gv$locked_object l,
         dba_objects o,
         gv$session s
   WHERE     l.object_id = o.object_id
         AND l.session_id = s.sid
         AND p.addr = s.paddr
         AND o.object_name = upper('&obj_name')  ---输入object_name
ORDER BY sid, s.serial#;
 

4. kill正在执行某个sql对应的会话：

set linesize 260 pagesize 10000
select
    b.inst_id,
    b.username,
    b.type,
   'alter system kill session ''' || b.sid || ',' || b.serial# || ',@' ||b.inst_id || ''' immediate;' kill_session_script,
   'kill -9 ' || a.spid
 from gv$process a, gv$session b
where a.addr = b.paddr
   and a.background is null
   and b.type = 'USER'
   and b.sql_id = '&sql_id'  ---输入sql_id
   and b.status = 'ACTIVE';

5. 所有含有关键字“LOCAL=NO”的进程是Oracle数据库中远程连接进程的共同特点，因此通过以下命令可以kill掉所有的进程

ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

6. ddl locked

-- For RAC, you should execute this script on both instances.

prompt "--------------------------------------------------------------------------------------"
set lines 200 pages 1000

col KGLNAOWN for a10 
col KGLNAOBJ for a60
col KGLHDNSD for a40
SELECT KGLNAOWN,KGLNAOBJ,KGLHDNSD 
  FROM x$kglob
 WHERE kglhdadr in( select P1RAW from v$session_wait where event like 'library cache lock%');

7. 查看等待library cahe事件的session信息

prompt "--------------------------------------------------------------------------------------"
col event for a50
col program for a50
col username for a10
  select /*+ rule */ sid,serial#,username,sql_id,status,event,program from v$session where paddr in (
       SELECT s.paddr
        FROM x$kglpn p, gv$session s
       WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0
   and kglpnhdl in ( select p1raw  from gv$session_wait where event  in ('library cache pin','library cache lock','library cache load lock')));

8. 生成kill 等待library cahe事件的session语句

col event for a50
col program for a50
col username for a10
  select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||
       s.inst_id || ''' immediate;' kill_session_script from (select 
  /*+ rule */ inst_id,sid,serial#,username,sql_id,status,event,program from gv$session where paddr in (
       SELECT s.paddr
        FROM x$kglpn p, gv$session s
       WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0
   and kglpnhdl in ( select p1raw  from gv$session_wait where event  in ('library cache pin','library cache lock','library cache load lock')))) s;
   

9. 通过dba_ddl_locks视图查看持有某个对象的ddl锁的sesion，并生成kill 语句

SELECT DISTINCT
          'alter system kill session '''
       || s.sid
       || ','
       || s.serial#
       || ',@'
       || s.inst_id
       || ''' immediate;'
          kill_session_script,
       d.*,
       s.*
  FROM gv$session s, dba_ddl_locks d
 WHERE     d.name = UPPER ('GET_REFERENCECMP_F')
       AND s.sid = d.session_id
       AND s.TYPE = 'USER';

10. 通过 V$DB_OBJECT_CACHE 查看某个对象在library cache里的信息,

V$DB_OBJECT_CACHE displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

SELECT * FROM  V$DB_OBJECT_CACHE WHERE NAME='GET_REFERENCECMP_F' and LOCKS!='0';

11. 根据工号查看session

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where osuser=11111 and sql_id='dzbgyptw8m3sf';

12. 查看哪些对象被锁

SELECT L.*,O.OBJECT_NAME,
       S.SID,
       S.SERIAL#,
       P.SPID,
       S.PROGRAM,
       S.USERNAME,
       S.MACHINE,
       S.PORT,
       S.LOGON_TIME,
       SQ.SQL_FULLTEXT
  FROM V$LOCKED_OBJECT L,
       DBA_OBJECTS O,
       V$SESSION S,
       V$PROCESS P,
       V$SQL SQ
 WHERE     L.OBJECT_ID = O.OBJECT_ID
       AND L.SESSION_ID = S.SID
       AND S.PADDR = P.ADDR
       AND S.SQL_ADDRESS = SQ.ADDRESS;
       
       


  SELECT t2.username,
         t2.sid,
         t2.serial#,
         t2.logon_time,
         tr.addr tx_addr,
         tr.start_time tx_start_time,
         o.object_name,
         t1.*,
         t2.sql_id,
         s.sql_text
    FROM v$locked_object t1,
         v$session t2,
         dba_objects o,
         v$transaction tr,
         v$sql s
   WHERE     t1.session_id = t2.sid
         AND o.object_id = t1.object_id
         AND tr.addr = t2.taddr
         AND s.sql_id = t2.sql_id
ORDER BY t2.logon_time;