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 

相关文章

在18c中通过ASM Flex DiskGroup克隆PDB
Oracle ASM Storage Limits
通过rpm包安装Oracle Database 18c
彻底搞懂Oracle RHP(Rapid Home Provisioning)
Oracle Database 18c (18.3.0) Upgrade and Migration hands-on Lab
Oracle数据库云服务
Oracle Graphpipe
How to config Oracle Member Cluster for database
Install Oracle Domain Service Cluster Step by Step
Oracle数据库备份云服务
Oracle RAC and Third Party Cloud
Open Source Languages and Oracle Database

联系我们

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