High "cursor: pin S wait on X" waits?
If your system meets any of the following criteria: - Oracle 12.1.0.2.0 or higher - Partitioned tables - Parallel query heavily used - Bind variables in use, as they should be and, you're seeing unusually high "cursor: pin S wait on X" waits, then you may want to know that this week, patch 21834574 was released. To give you a little bit of background information; the issue is with a new code path introduced in Oracle version 12, which is related to some of the brand new infrastructure life-cycle management (ILM) functionality. This ILM feature - whether you are intentionally using any ILM functionality or not - causes delays when a new child cursor is created in the library cache. On a client's system we have observed waits of up to 0.3 seconds. In a nutshell, here's what's happening: - A new parallelized query using bind variables is hard parsed. - Oracle creates a new cursor, and adds a child cursor. - Each of your PX slaves will then wait for the following event: "cursor: pin S wait on X". - The mutex being waited on is of type Cursor Pin at the location "kkslce [KKSCHLPIN2]". This is normal and these waits can't be fully avoided. But what's not normal is that the mutex is being waited on for a quarter of a second in each slave session. Oracle has now released a patch which implements a new _fix_control which can be set to enable the fix once the patch is applied. As always, please consult with Oracle Support before applying this patch and setting this parameter, to make sure that you really are seeing this particular bug. There are others in the current versions of Oracle which share very similar symptoms, and the only way to be certain is to double-check with Oracle support or development. Happy patching! Discover more about our expertise in the world of Oracle.
Share this
You May Also Like
These Related Stories
SQL Server In-Place Upgrade Failed: Wait on the Database Engine Recovery Handle Failed
![](https://www.pythian.com/hubfs/Imported_Blog_Media/Upgrade-Failed-1.png)
SQL Server In-Place Upgrade Failed: Wait on the Database Engine Recovery Handle Failed
Jun 29, 2022
4
min read
Watch Out When Running Out of Disk Space With InnoDB Group Replication Cluster
Watch Out When Running Out of Disk Space With InnoDB Group Replication Cluster
Feb 9, 2021
4
min read
Change of database passwords through OEM
Change of database passwords through OEM
May 4, 2017
3
min read
No Comments Yet
Let us know what you think