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

From: Marko Sutic <marko.sutic_at_gmail.com>
Date: Fri, 12 Apr 2013 12:46:46 +0200
Message-ID: <CAMD6WPeK6v-BLXWOEKd3rL_7bhD=5Oaz6N38T3+u1iRPXYmPgQ_at_mail.gmail.com>

We have experienced problems with "cursor: pin S wait on X" on 11.1.0.7 version.
Every few weeks session hanged with status "ACTIVE" and "SQL*Net message from client" wait event.
When another session tried to execute the same sql statement it finished waiting for "cursor: pin S wait on X".

If you don't kill that first session at OS level it's just matter of time when queue of waiting sessions will grow to maximum number of sessions. All of them with the same wait event - "cursor: pin S wait on X".

I've tried to reproduce this problem but no luck with that. Also, we have Automatic memory management disabled for this database.

I don't know why Oracle keeps that "ACTIVE" session for indefinitely with "SQL*Net message from client" wait event. This problem is probably triggered with some network issues, temporary lost contact with client.
Maybe Oracle is unable to clean this session cause of it's "ACTIVE" status.

Even if you kill that session from Oracle it is only marked as KILLED (never cleaned) and "cursor: pin S wait on X" is still held. Only workaround is to kill such process from OS level.

Anybody noticed such behavior with "cursor: pin S wait on X"?

Regards,
Marko Sutic

On Tue, Apr 9, 2013 at 2:39 PM, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>wrote:

> 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]
> >
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Fri Apr 12 2013 - 12:46:46 CEST