Can blocking_session in v$session reflect X mode holder of the mutex --"cursor: pin S wait on X" ?
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.