Focus On Oracle

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


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

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 

相关文章

ORA-12514 During DataPump Export/Import In RAC
Oracle Active DataGuard 12.2新特性揭秘
Active Data Guard新功能演示
Oracle MAA汇总
Oracle Audit Vault and Database Firewall
全面了解Oracle 12c数据安全
Oracle SqlDeveloper很强悍
Oracle Exadata ADG & GDS
Retest Exadata IO Performance
如何在oracle 12c中正确的应用补丁?
Oracle实用的故障诊断工具
Exadata IO性能如何

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com