Re: Understanding "cursor: pin S wait on X"

> The characteristics of the workload has changed. For example a batch Job has been added in an OLTP environment
> or there has been an increase of activity in a certain application area that requires memory changes.

seems to describe our situation (an increase of activity)

We also did see several resize operations of the shared pool - although a lot less (4) compared to the figures in the document.

> Should have also mentioned there are a variety of bugs that can cause this behavior.
> To see the full list of potential bugs for your version of the DB, check out the note:
> WAITEVENT: "cursor: pin S wait on X" Reference Note [ID 1298015.1]

We are on 11.2.0.3 so that only leaves 5 bugs from the list. None of them seems to be particularly relevant for our situation though (maybe 14295250, but I'm not sure).

I will pass this on to the DBAs of the hosting company. Thanks again for the pointers.

Kind regards
Thomas

Job Miller, 09.04.2013 14:21:
> For a 30GB SGA turn off AMM.
>
> * A spike in "cursor: pin S wait on X" or "library cache lock" waits may be seen.
> This is more likely to be seen in an OLTP environment where both shared pool and buffer cache are in demand.
> * The problem will happen randomly and intermittently.
> * In extreme examples the database can appear to hang and you may receive related timeout symptoms such as "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" if no movement occurs for a threshold period. S
>
> Workaround:
>
> Disable Automatic memory management by setting SGA_TARGET=0.
>
>
> High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity [ID 742599.1]
>

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