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 

相关文章

Oracle数据库备份云服务
Oracle RAC and Third Party Cloud
Open Source Languages and Oracle Database
Oracle SQL Developer Command Line (SQLcl)
Oracle Database Multilingual Engine(MLE)
Database Features A-Z
Oracle JET
Do migration with Oracle SQL Developer
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

联系我们

韩朝阳



    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