Bug “cursor: pin S wait on X” introduced in 10.2.0.3
Today, I noticed a hang of the session that was executing a simple MERGE statement(s) with
“cursor: pin S wait on X” wait event.
Killed the session and tried to shutdown immediate. Shutdown process hanged as well (only session with the status killed was present in V$SESSION prior to shutdown), with the message in alert.log:
Sat Jan 19 12:56:05 2008 Active call for process 2100 user 'SYSTEM' program 'ORACLE.EXE (SHAD)' Active call for process 1736 user 'SYSTEM' program 'ORACLE.EXE (SHAD)' SHUTDOWN: waiting for active calls to complete.
A quick search on Metalink revealed that this is a bug introduced in 10.2.0.3.
Note 5907779 – “Self deadlock hang on “cursor: pin S wait on X” (typically from DBMS_STATS)”
Note 401435.1 -“10.2.0.3 Patch Set – Availability and Known Issues”
The bug will be fixed in Patchset 10.2.0.4. For the time being you can use interim patch 5907779, if you’re not using Windows because patch for this platform is not available at the moment.
If the problem re-appear I’ll first check the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views, before killing the sessions.