My Oracle Support Banner

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

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 19.18.0.0.0 and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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