General slowness with high "sleeps" on "cache buffers chains" latch seen (Doc ID 2973268.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

General slowness reported either for specific SQL statements or whole batch jobs with no obvious source of the slowness.

A specific SQL might be identified (from the top section of the Elapsed Time of the AWR report). If this turns out to have the same execution plan (Confirm with SQLTXPLAIN) and is processing the same number of rows and no other obvious cause of system slowness is seen, you may have encountered this problem.

In the below example we see 10 billion Gets on the Buffer Cache, with 82 million misses. While these numbers are high, Buffer Gets can be very high on a busy system. The number of Sleeps however is too high for a one hour period. It is not unusual to get some Sleeps when processes clash on a specific Latch, but if too many processes are doing this there might be generalized slowness.

Changes

Either the Buffer Cache is under pressure because it has been reduced in size or there may be some specific SQL or a group of SQL statements which are concentrating activity on a few specific areas of the Buffer Cache. The Buffer Cache may have reduced in size due to deliberate action or because Shared Pool expansion has occurred. If the Buffer Cache has not reduced in size it may be that the number of Buffer Gets per hour have increased to the point where the Buffer Cache Latch cannot keep up and has to sleep regularly to obtain the latch.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.