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 

相关文章

Getting started with Oracle JDBC
How to generate AWR on PDB and ADG(12.2 afterwards)
Oracle ZDLRA (Zero Data Lost Recovery Appliance) Features
Oracle Database 18c On Exadata
Oracle Database 18c New Feature Guide
Migrate Oracle Database on AIX to Exadata
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数据安全

联系我们

韩朝阳



    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