High “cursor: pin S wait on X” waits?

Posted in: Oracle, Technical Track

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

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

1 Comment. Leave new

Hanumantha Rao
July 8, 2016 4:45 am

Hi, In my environment we have the default value of TRUE for that parameter. We are in 12.1.0.2.0. However, still we are facing some performance issues with Cursor:Pin wait events. Is there any way we can control this by changing the CURSOR_SHARING parameter to FORCE to resolve this?

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *