Re: Identify blocking session for cursor: pin S wait on X when database is hung

From: Tanel Poder <tanel_at_poderc.com>
Date: Mon, 28 Feb 2011 23:10:34 +0200
Message-ID: <AANLkTikBL9F_3FWPKCKuu_ff29PvykUYDZw+s9siLn+v_at_mail.gmail.com>

In addition to what Tim said, the P1 would tell you the hash value of the library cache object for which mutex you waited for, P2 would tell you who was the blocker if there was a single exclusive one, but you'll need to extract the blocker info out of the upper half bytes of the p2 value.

I just uploaded one of my old presentations about Latch & Mutex contention troubleshooting - the stuff about mutexes & wait interface is on slides 18-21:

http://www.slideshare.net/tanelp/oracle-latch-and-mutex-contention-troubleshooting

Also, should the DBA_HIST views not contain the data you need (in case the AWR flush itself got hung too or the DB was restarted too soon after the hang) then next time you can use sqlplus -prelim (see Mark's email with the link to Jeremiah Wilton's blog) and use a systemstate dump to find the blocker (which may take long time btw):

http://el-caro.blogspot.com/2007/10/identifying-mutex-holder.html

There are some other options like dumping the in-memory ASH buffers (ORADEBUG ASHDUMP) or reading X$ contents directly (available since 11g) using ORADEBUG DIRECT_ACCESS SELECT * FROM x$ksdhg_chains;

--
Tanel Poder
Blog - http://blog.tanelpoder.com
Seminars - http://tech.e2sn.com/oracle-training-seminars

On Mon, Feb 28, 2011 at 9:28 PM, Tim Gorman <tim_at_evdbt.com> wrote:



>  Sanjeev,

>

> Query DBA_HIST_ACTIVE_SESS_HISTORY where EVENT = 'cursor: pin S wait on X'

> and use the information provided there.  What might be most useful will be

> the relationship between SESSION_ID/SESSION_SERIAL# and

> BLOCK_SESSION_ID/BLOCK_SESSION_SERIAL#, as well as the P1, P2, and P3 value

> associated with the wait event, as well as the SQL_ID.

>

> Have fun!

>

> Tim Gorman

> consultant -> Evergreen Database Technologies, Inc.

> postal     => 13087 West Cedar Drive #225, Lakewood CO 80228

> website    => http://www.EvDBT.com/

> email      => Tim_at_EvDBT.com

> mobile     => +1-303-885-4526

> fax        => +1-303-484-3608

> Lost Data? => http://www.ora600.be/ for info about DUDE...

>

>


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 28 2011 - 15:10:34 CST