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 !!!

4 comments:

Anonymous said...

Nice one.

Anonymous said...

We too were hit by this bug in Oracle 10.2.0.2

Strange Things said...

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.

maclean said...

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/