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

  • From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 09 Apr 2013 14:39:44 +0200

Thanks, that looks promising.
This part here:

> 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]
>



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


Other related posts: