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 !!!
Subscribe to:
Post Comments (Atom)
4 comments:
Nice one.
We too were hit by this bug in Oracle 10.2.0.2
any reason why you wouldn't use the default Oracle 10g's gather statistics ? It's an incremental collection of statistics and works very well.
recently i have a problem with cursor pin s on X.Many session are waiting for Event in my customer’s AIX 10.2.0.4 instance.I looked for the session who was pining that cursor on X mode ,and saw it was waiting on event single-task message. It looks like reparse one cursor who includes dblink objects will cause this problem. And mutex is sitll immature in 10g. My article is here
http://www.youyus.com/2010/06/06/single-task-message-and-cursor-pin-s-on-x/
Post a Comment