Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 性能优化

Oracle blocking session monitor

查询锁的有用语句,适用于10g以上

select *

  from gv$lock
   where (id1, id2, type) in
  (select id1, id2, type from gv$lock where request > 0);

select inst_id, sid, blocking_instance, blocking_session
  from gv$session
 where blocking_instance is not null
   and blocking_session is not null
 order by 1, 2;

with lk as
 (select blocking_instance || '.' || blocking_session blocker,
         inst_id || '.' || sid waiter
    from gv$session
   where blocking_instance is not null
     and blocking_session is not null)
select lpad('  ', 2 * (level - 1)) || waiter lock_tree
  from (select *
          from lk
        union all
        select distinct 'root', blocker
          from lk
         where blocker not in (select waiter from lk))
connect by prior waiter = blocker
 start with blocker = 'root';
 LOCK_TREE
---------------------------------------------
2.1107
  1.1252
    1.1113
    1.1158
    2.1011
    2.633
  1.1299
  1.1447
  1.388
  2.1348
  2.392

set serverout on
declare
  sess varchar2(20);
  sessinfo varchar2(100);
begin
  for i in
    (with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
       from gv$session where blocking_instance is not null and blocking_session is not null)
     select distinct blocker from lk where blocker not in (select waiter from lk)
    )
  loop
    select regexp_substr(i.blocker,'[0-9]+$')||','||serial# ||',@' || regexp_substr(i.blocker,'[0-9]+'),
      substr(username||':'||program,1,29) into sess, sessinfo
    from gv$session where inst_id = regexp_substr(i.blocker,'[0-9]+') and sid = regexp_substr(i.blocker,'[0-9]+$');
    dbms_output.put_line(sessinfo || ' ' || 'alter system kill session ''' || sess || ''' immediate;');
  end loop;
end;
/


关键词:lock perf oracle 

相关文章

Exadata最权威最完整的学习资料
Oracle数据库C函数解析
Exadata上收集Cell节点的日志
MySQL Cookbook for Oracle DBA
Full Transportable Export/Import(fxtts)
Oracle自治数据仓库云
expdp on physical standby
Oracle Database 12c之后DataGuard环境中的PDB操作
How to generate AWR for Standby Databases
Oracle DataGuard in 18c & 19c
通过gDBClone快速的构建开发测试环境
Oracle Database Express Edition 18c发布了
Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com