final_blocking_session

admin

1. set pages 1000
   
2. set lines 120
   
3. set heading off
   
4. column w_proc format a50 tru
   
5. column instance format a20 tru
   
6. column inst format a28 tru
   
7. column wait_event format a50 tru
   
8. column p1 format a16 tru
   
9. column p2 format a16 tru
   
10. column p3 format a15 tru
    
11. column Seconds format a50 tru
    
12. column sincelw format a50 tru
    
13. column blocker_proc format a50 tru
    
14. column fblocker_proc format a50 tru
    
15. column waiters format a50 tru
    
16. column chain_signature format a100 wra
    
17. column blocker_chain format a100 wra
    
18. 
    
19. SELECT *
    
20. FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
    
21. 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
    
22. ' from Instance '||blocker_instance BLOCKER_PROC,
    
23. 'Number of waiters: '||num_waiters waiters,
    
24. 'Final Blocking Process: '||decode(p.spid,null,'<none>',
    
25. p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
    
26. 'Program: '||p.program image,
    
27. 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
    
28. 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
    
29. 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
    
30. '<none>',blocker_chain_id) blocker_chain
    
31. FROM v$wait_chains wc,
    
32. gv$session s,
    
33. gv$session bs,
    
34. gv$instance i,
    
35. gv$process p
    
36. WHERE wc.instance = i.instance_number (+)
    
37. AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
    
38. and wc.sess_serial# = s.serial# (+))
    
39. AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
    
40. AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
    
41. AND ( num_waiters > 0
    
42. OR ( blocker_osid IS NOT NULL
    
43. AND in_wait_secs > 10 ) )
    
44. ORDER BY chain_id,
    
45. num_waiters DESC)
    
46. WHERE ROWNUM < 101;

复制代码