Database Tuning (MOSC)

MOSC Banner

Can blocking_session in v$session reflect X mode holder of the mutex --"cursor: pin S wait on X" ?

edited Mar 16, 2016 4:20PM in Database Tuning (MOSC) 5 commentsAnswered

When "cursor: pin S wait on X" event occurs I always need to quickly find the blocker.  MOS has taught us many ways to find it. Here is the easiest one I think.

snippet from MOS 1298015.1

  • In 11g onwards session holding the mutex is shown in the BLOCKING_SESSION column of <View:V$SESSION> for the waiting session.
    One can find the blocking session using SQL of the form

SELECT SQL_ID, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS

   FROM v$session

  WHERE SID=&SID_OF_WAITING_SESSION;

But in practice, I found that it didn't work the blocking_session column in v$session was always null even when "cursor: pin S wait on X" happened.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center