kkslce [KKSCHLPIN2] mutex contention on hard parse of PQ with binds

Fairly odd event. After an upgrade to 12.1.0.2 we've been seeing very high "cursor: pin S wait on X" waits. But here's the catch - they only happen under very special (and seemingly odd) circumstances:

• Query runs in parallel
• Query uses bind variables
• Query is hard parsed

If we run the same query in serial, or with literal values, or we re-use a cursor, no waits are seen.

The waits are huge (up to 0.3 seconds) and are seen on the PX slaves only. Say we're using 8 slaves, 4 of them will show these waits. 4 won't. And only if we're using binds. No binds, no waits.

Does anyone have an explanation for what could be happening?

I've already tried disabling some of the new fancy adaptive features, and most bind related features (peeking, px bind peeking, bind value captures, etc). Heck, it even happens with opt features set to 11.2.

On a very similar database, that's still on 11.2.0.3, we're also seeing the same behavior. The key difference being that the waits are very short - less than 0.01 seconds. But they're still there, and I don't see why they would be in the first place. What causes Oracle to pin that mutex, if a bind variable is used in the query?

A bug has been filed. So we'll see what comes out of that. It would seem odd that we'd be the first ones to hit this, it's not exactly exotic to run a parallel query with binds.

Stefan

--
http://www.freelists.org/webpage/oracle-l