温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

通过v$wait_chains视图诊断数据库hang和Contention

发布时间:2020-08-14 23:07:52 来源:ITPUB博客 阅读:243 作者:pentium 栏目:关系型数据库

11g之前,通常我们数据库hang住了之后,我们会对数据库做hang analyze来进行分析,在11g之后,我们可以通过一个新的视图v$wait_chains来诊断数据库hang和contention。在11gR1这个版本里面,Oracle通过diag进程实现了一个功能,每隔3秒做一次本地的hang analyze,每隔10秒做一次global的hang analyze。而这些信息会存放在内存里面,Oracle把这一块内存称作”hang analysis cache”。而这一部分内存信息,对我们数据库诊断hang和contention起着非常重要的作用。而数据库还有一些特性及工具也需要使用这块内存区域。比如Hang Management, Resource Manager Idle Blocker Kill, SQL Tune Hang Avoidance和pmon清除,还有一些外部工具如Procwatcher。

通过v$wait_chains视图诊断数据库hang和Contention

通过v$wait_chains视图诊断数据库hang和Contention


因为在11gR2的v$session视图中有一个字段叫final_blocking_session,这个字段能够去查看最上层的阻塞者。最终的blocker一般都处于wait_chain的顶端。这样的session才会引起问题。我们先来看看普通的查询.首先随便制造两个session共同更新一行的情况。

SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains;


set pages 1000

set lines 120

set heading off

column w_proc format a50 tru

column instance format a20 tru

column inst format a28 tru

column wait_event format a50 tru

column p1 format a16 tru

column p2 format a16 tru

column p3 format a15 tru

column Seconds format a50 tru

column sincelw format a50 tru

column blocker_proc format a50 tru

column fblocker_proc format a50 tru

column waiters format a50 tru

column chain_signature format a100 wra

column blocker_chain format a100 wra


SELECT * 

FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 

 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| 

 ' from Instance '||blocker_instance BLOCKER_PROC,

 'Number of waiters: '||num_waiters waiters,

 'Final Blocking Process: '||decode(p.spid,null,'',

 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 

 'Program: '||p.program image,

 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,

 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,

 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,

 '',blocker_chain_id) blocker_chain

FROM v$wait_chains wc,

 gv$session s,

 gv$session bs,

 gv$instance i,

 gv$process p

WHERE wc.instance = i.instance_number (+)

 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)

 and wc.sess_serial# = s.serial# (+))

 AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))

 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))

 AND ( num_waiters > 0

 OR ( blocker_osid IS NOT NULL

 AND in_wait_secs > 10 ) )

ORDER BY chain_id,

 num_waiters DESC)

WHERE ROWNUM < 101;


使用final_blocking_session字段,能查到最上端的阻塞进程。


set pages 1000

set lines 120

set heading off

column w_proc format a50 tru

column instance format a20 tru

column inst format a28 tru

column wait_event format a50 tru

column p1 format a16 tru

column p2 format a16 tru

column p3 format a15 tru

column Seconds format a50 tru

column sincelw format a50 tru

column blocker_proc format a50 tru

column fblocker_proc format a50 tru

column waiters format a50 tru

column chain_signature format a100 wra

column blocker_chain format a100 wra


SELECT * 

FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 

 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| 

 ' from Instance '||blocker_instance BLOCKER_PROC,

 'Number of waiters: '||num_waiters waiters,

 'Final Blocking Process: '||decode(p.spid,null,'',

 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 

 'Program: '||p.program image,

 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,

 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,

 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,

 '',blocker_chain_id) blocker_chain

FROM v$wait_chains wc,

 gv$session s,

 gv$session bs,

 gv$instance i,

 gv$process p

WHERE wc.instance = i.instance_number (+)

 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)

 and wc.sess_serial# = s.serial# (+))

 AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))

 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))

 AND ( num_waiters > 0

 OR ( blocker_osid IS NOT NULL

 AND in_wait_secs > 10 ) )

ORDER BY chain_id,

 num_waiters DESC)

WHERE ROWNUM < 101;

通过v$wait_chains视图诊断数据库hang和Contention

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI