Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.1.0 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.2.0.1.0

Symptoms

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. See:

Document 278316.1 Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "

Changes

Automatic Memory Management is used. This is actively resizing he shared pool
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.

Cause

Alternating frequent shrink and grow of the buffer cache and shared pool may be seen with automatic memory management enabled causing various waits in sessions and concurrency issues.
The frequent resize of the shared pool and buffer cache is causing contention so that waits on "cursor: pin S wait on X" and "library cache lock" may be seen.

The resize operations may be see through querying V$SGA_RESIZE_OPS as follows:

set linesize 90
set pagesize 60
column component format a25
column Final format 99,999,999,999
column Started format A25

SELECT COMPONENT ,OPER_TYPE,FINAL_SIZE Final,to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$SGA_RESIZE_OPS;

COMPONENT                 OPER_TYPE     Final            Started
------------------------- ------------- ---------------- --------------------
DEFAULT buffer cache      SHRINK    17,548,967,936 10/06/2008 07:56:28
shared pool GROW             2,197,815,296 10/06/2008 07:56:28
DEFAULT buffer cache      GROW            17,649,631,232 10/06/2008 07:54:25
shared pool SHRINK           2,097,152,000 10/06/2008 07:54:25

Note:
-----
The shared pool shrunk at 7:54:25 and within 2 minutes it grew at 7:56:28
The default buffer cache grew at 7:54:25 and again shrunk at 7:56:28.
You can also see resize operations every 30 seconds

COMPONENT                 OPER_TYPE     Final            Started
------------------------- ------------- ---------------- --------------------
shared pool               GROW             2,130,706,432 10/06/2008 06:49:20
DEFAULT buffer cache      GROW            17,649,631,232 10/06/2008 06:49:14
DEFAULT buffer cache      SHRINK          17,632,854,016 10/06/2008 06:49:14
shared pool               GROW             2,113,929,216 10/06/2008 06:49:14
shared pool               SHRINK           2,097,152,000 10/06/2008 06:49:14
DEFAULT buffer cache      SHRINK          17,599,299,584 10/06/2008 06:47:44
DEFAULT buffer cache      SHRINK          17,616,076,800 10/06/2008 06:47:44
shared pool               GROW             2,147,483,648 10/06/2008 06:47:44
shared pool               GROW             2,130,706,432 10/06/2008 06:47:44
DEFAULT buffer cache      GROW            17,649,631,232 10/06/2008 06:47:43
DEFAULT buffer cache      SHRINK          17,632,854,016 10/06/2008 06:47:43

When there are lots of shrinks and grows it is often useful to see a summary of the information which can be obtained by running the following query:

select component,oper_type,count(1)
FROM V$SGA_RESIZE_OPS
group by component,oper_type;

COMPONENT                     OPER_TYPE     COUNT(1)
----------------------------- ------------- ----------
shared pool                   GROW                 94
DEFAULT buffer cache          SHRINK               94
shared pool                   SHRINK              306
DEFAULT buffer cache          GROW                306

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.
This does not include in-progress operations.

The Following query will likely return a row.

select * from v$sgastat where name = 'KGH: NO ACCESS';

POOL        NAME                       BYTES
------------   -------------------------- ------------
shared pool KGH: NO ACCESS    216572480

Get an Ash report for a small time frame. (for example for 2 minutes) during the time the problem was seen.

Check whether Top Events include "cursor: pin S wait on X" or "library cache lock".

Check the section Activity Over Time and check the Slot Time (Duration)

The times of cursor: pin S wait on X or library cache lock will be close to the time when the resize happened.

DBA_HIST_SGASTAT displays detailed historical information on the system global area (SGA).

select a.begin_interval_time,a.end_interval_time, b.bytes
from WRM$_SNAPSHOT A, DBA_HIST_SGASTAT B
where a.snap_id = b.snap_id
and b.pool = 'shared pool' and b.name = 'KGH: NO ACCESS'
order by 1;

KGH: NO ACCESS chunks are owned by the buffer cache and indicate a partial transfer between buffer cache and shared pool.

Solution

Disable Automatic memory management by setting SGA_TARGET=0.

or

Following parameter cna be set dynamically:
alter system set "_memory_broker_stat_interval"=999;

This will increase the time between resize to at least 999 seconds and thereby reducing the number of resize operations.

"_memory_broker_stat_interval" is in seconds and is by default 30 seconds.
You can set _memory_broker_stat_interval to a larger value.
This should be done on all RAC nodes.

or

Apply patch:
For 10.2.0.4 apply Patch:7189722
For 11g apply Patch:9267837

If Patch 9267837 is not available for your 11g version and platform, log a new Service Request so that a One-Off Back-port request can be created for you. Please also provide a list of the current patches applied to the Oracle Home (command: opatch lsinventory -detail), in order that conflicts with existing patches may be checked.

Note: The bug fix is included in 11.2.0.2 patchset

References

NOTE:1269139.1 - SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0)
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error [Video]
NOTE:6528336.8 - Bug 6528336 - Automatic SGA may repeatedly shrink / grow the shared pool
NOTE:7189722.8 - Bug 7189722 - Frequent grow/shrink SGA resize operations
NOTE:9267837.8 - Bug 9267837 - Auto-SGA policy may see larger resizes than needed
NOTE:278316.1 - Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "

ITPub博客

High 'cursor: pin S wait on X' and/or 'library cache lock' Waits.