Shulou(Shulou.com)06/01 Report--

Before 11g, usually after our database hang resides, we will do hang analyze to analyze the database. After 11g, we can diagnose the database hang and contention through a new view v$wait_chains. In this version of 11gR1, Oracle implements a function through the diag process, making a local hang analyze every 3 seconds and a hang analyze of global every 10 seconds. This information is stored in memory, which Oracle calls "hang analysis cache". This part of memory information plays a very important role in diagnosing hang and contention in our database. And the database also has some features and tools that also need to use this memory area. Such as Hang Management, Resource Manager Idle Blocker Kill, SQL Tune Hang Avoidance and pmon cleanup, as well as external tools such as Procwatcher.

Because there is a field called final_blocking_session in 11gR2's v$session view, this field can see the top blocker. The final blocker is generally on top of the wait_chain. This kind of session will cause problems. Let's first take a look at the general query. First, randomly create a situation in which two session update a row together.

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.spidrecoery nullrecording'

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.