How Inefficient SQL Statements in Oracle Cause Contention for Cache Buffers Chains Latches

Inefficient SQL statements are the main cause of cache buffers chains latch contentions. When mixed with high concurrency, the time spent on latch free waits can be rather significant. This is a familiar scene in environments where the application opens multiple concurrent sessions that execute the same inefficient SQL statements that go after the same data set.

You will do well if you keep these three things in mind:

• Every logical read requires a latch get operation and a CPU.
• The only way to get out of the latch get routine is to get the latch.
• Only one process can own a cache buffers chains latch at any one time, and the latch covers many data blocks, some of which may be needed by another process. (Again, as mentioned in a previous note, Oracle9i Database allows the cache buffers chains latches to be shared for read-only.)

Naturally, fewer logical reads means fewer latch get operations, and thus reduces latch competition, which translates to better performance. Therefore, you must identify the SQL statements that contend for the cache buffers chains latches and tune them to reduce the number of logical reads. SQL statements with high BUFFER_GETS (logical reads) per EXECUTIONS are the main culprits.

NOTE

Many DBAs make a grave mistake by simply increasing the number of cache buffers chains latches with the _DB_BLOCKS_HASH_LATCHES parameter without first optimizing the SQL statements. While additional latches may provide some relief, these DBAs treat the symptom without fixing the problem.

NOTE

We experienced a lot of cache buffers chains latch contentions after upgrading a database from Oracle8.1.7.4 to Oracle9.2.0.5 on the Sun Solaris platform. The new optimizer was generating and using poor execution plans for the application. Several hidden optimizer-related parameters that are by default disabled in Oracle8i Database are enabled Oracle9i Database. The problem was fixed by unsetting some of the parameters. If you are having the same problem, the best way is to handle it through Oracle Support.