Saturday, June 30, 2007
“cursor: pin S wait on X” Wait Event in Oracle 10g Release 2
After a pleasant weekend, when I returned back to work I found one of our development databases on Oracle 10g was in a hung state. Later, upon investigation I found 23 sessions were waiting on “cursor: pin S wait on X” wait event. Searching on Oracle Metalink, I came to know that this is a bug in Oracle 10.2.0.3 on all platforms. The note explains that this could happen when you typically use DBMS_STATS to gather statistics. We had a job scheduled to run on the weekend to gather statistics. I identified the session which was gathering statistics and killed that session both from Oracle and OS. Soon after that everything was back to normal. A patch is available on Oracle Metalink which addresses this issue. References from Metalink: Subject: 10.2.0.3 Patch Set - Availability and Known Issues Note:401435.1 Last Revision Date: 14-JUN-2007 Subject: Bug 5907779 - Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS) Note:5907779.8 Last Revision Date: 10-APR-2007 Happy reading !!!