PROMPT

PROMPT

PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PROMPT------ /) (\

PROMPT------ .-._((,~~.))_.-,

PROMPT------ `-. @@ ,-'

PROMPT------ / ,o--o. \

PROMPT------ ( ( .__. ) )

PROMPT------ ) `----' (

PROMPT------ / \

PROMPT------ / \

PROMPT------ / \

PROMPT------ "The Silly Cow"

PROMPT----- Script: blocking_local.com

PROMPT----- Author: Prashant 'The FatDBA'

PROMPT----- Version: V1.0 (Date: 04-02-2012)

PROMPT-----

PROMPT-----

PROMPT-----

clear break

clear col

ttitle off

btitle off

set linesize 150 trimspool on

set pagesize 70

set echo off heading on feedback on

col blocking_sql_id format a12 head 'BLOCKING|SQL_ID'

col session_id format 999999 head 'SID'

col event format a42 head 'EVENT'

col session_state format a12 head 'SESSION|STATE'

col time_waited format 999,999.99 head 'TIME|WAITED|SECONDS'

col sample_time format a25 head 'SAMPLE TIME'

with blockers as (

select distinct blocking_session session_id, blocking_session_serial# session_serial#, sample_id

from v$active_session_history

--from dba_hist_active_sess_history

where blocking_session is not null

),

blocked as (

select distinct

ash.sample_id

, ash.session_id

, ash.session_serial#

, ash.blocking_session

, ash.blocking_session_serial#

, ash.time_waited

, max(ash.time_waited) over ( partition by ash.session_id, ash.session_serial#) max_time_waited

from v$active_session_history ash

--from dba_hist_active_sess_history ash

join blockers blkr on ash.blocking_session = blkr.session_id

and ash.blocking_session_serial# = blkr.session_serial#

and ash.sample_id = blkr.sample_id

where ash.session_state = 'WAITING'

and ash.event = 'enq: TX - row lock contention'

--group by ash.session_id, ash.session_serial#, ash.blocking_session, ash.blocking_session_serial#

)

select blkd.sample_id

, blkr.sample_time

, blkd.session_id

, blkd.session_serial#

, blkd.blocking_session

, blkd.blocking_session_serial#

, blkd.time_waited / 100 time_waited

, blkr.sql_id

, blkr.session_state

, blkr.event

from blocked blkd

join v$active_session_history blkr

on blkr.session_id = blkd.blocking_session

and blkr.session_serial# = blkd.blocking_session_serial#

and blkr.session_id = blkd.blocking_session

and blkr.session_serial# = blkd.blocking_session_serial#

and blkr.sample_id = blkd.sample_id

where blkd.time_waited = blkd.max_time_waited

order by blkd.sample_id, blkd.session_id