:

“WAITING FOR BUFFER QUEUE TO SHRINK”

The Problem

I am running Oracle database 11.2.0.2, patchset 10 on a Windows Server 2003 platform. I have set-up and configured Oracle’s Change Data Capture (CDC), and I am running an asynchronous downstream capture process. One of the developers complained that the capture data change didn’t appear to be running correctly because it was not capturing any changes. The first place that I usually look is in the v$streams_capture view. When I looked in v$streams_capture there was a wait event showing – “WAITING FOR BUFFER QUEUE TO SHRINK”.

Upon further investigation I found the below information in the Oracle documentation

1. Run the following queries at the database that contains the outbound server:

Query the V$PROPAGATION_RECEIVER view.:

SELECT STATE
FROM V$PROPAGATION_RECEIVER;

If the state is WAITING FOR MEMORY, then consider increasing the Streams pool size.

Query the V$STREAMS_POOL_STATISTICS view:

SELECT TOTAL_MEMORY_ALLOCATED/CURRENT_SIZE
FROM V$STREAMS_POOL_STATISTICS;

If the value returned is .90 or greater, then consider increasing the Streams pool size.

If the outbound server receives changes from a capture process that is running on the same database, then query the V$STREAMS_CAPTURE view.:

SELECT STATE
FROM V$STREAMS_CAPTURE;

If the state is WAITING FOR BUFFER QUEUE TO SHRINK, then increase the Streams pool size.

The Cause

I think this was just an initial configuration issue whereby I had not allocated a sufficient amount of memory for the streams_pool_size.

The Solution

ALTER SYSTEM SET streams_pool_size=1G SCOPE=both;

Like it, share it...

Category: Change Data Capture


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *