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

How to find my Windows is 32-bit or 64-bit?

How to find my Windows is 32-bit or 64-bit? To find the answer to this question, follow this link http://support.f-secure.com/enu/home/supportissue/fsis2007/before_install_q07.shtml How to find the OS block size of my Windows OS? Microsoft defines Cluster as “In data storage, the smallest amount of disk space that can be allocated to hold a file. All file systems used by Windows organize hard disks based on clusters, which consist of one or more contiguous sectors.” To find the OS block size: Right Click on “My Computer” icon on the desktop Click on “Manage” Expand “Storage” and select “Disk Defragmenter” Press the “Analyze” button Once your windows completes the analysis, it presents you with a report. Press “View Report” button The block size is indicated by “Cluster Size”. In my case it’s 4K. How to set the OS block size on my Windows OS? Open the Windows Explorer. Right Click on your “C drive” and click on “Format”. A window is presented with couple of options. The OS block size is displayed by a drop-down list labeled “Allocation unit size”.