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.