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.
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.
Solution:
Option 1: Disable Automatic memory management by setting SGA_TARGET=0.
Option 2: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.
Option 3:
Apply patch:
For 10.2.0.4 apply Patch:7189722
For 11g apply Patch:9267837
The bug fix is included in 11.2.0.2 patchset
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.
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.
Solution:
Option 1: Disable Automatic memory management by setting SGA_TARGET=0.
Option 2: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.
Option 3:
Apply patch:
For 10.2.0.4 apply Patch:7189722
For 11g apply Patch:9267837
The bug fix is included in 11.2.0.2 patchset
Comments