Re: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs

From: Jonathan Lewis <>
Date: Sun, 17 Feb 2008 10:10:43 -0000
Message-ID: <00bb01c8714d$5b6c3de0$0200a8c0@Primary>

Insert into table - presumably with primary key index. It doesn't matter what you insert or where, the root block of the primary key index has to be hit as the PK entry is made.

Don't assume, by the way, that the TOP 'SQL by gets' is the direct culprit; consider the top few - it's a strong indicator, but look at what the SQL is doing, and look at the 'SQL by executions' as well before jumping to conclusions.

In your case, the top by gets is an INSERT. Consider, for example, an insert on a simple, large, table with 5 indexes; this is likely to do something like 20 gets (3 for each index that has to be updated, plus a few to allow for the table and undo). The nature of the SQL tells you something about the significance of the number of gets.

Another point to consider when looking at the TOP sql statements - maybe you have some double-counting going on. Is it at the top because it calls a pl/sql function in it's 'where' clause, or in the select, or values, clauses which runs another SQL statement that is doing most of the buffer gets.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "VIVEK_SHARMA" <> To: "Christian Antognini" <> Cc: <> Sent: Sunday, February 17, 2008 7:08 AM Subject: RE: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs

Thanks Chris, folks

In the Benchmark Run for Batch processes, Setting "_write_clones" to "0" in init.ora severely affected the performance Adversely & was removed thereafter.

Cause of the "latch: cache buffers chains" is believed to be "INSERT into IDT Table" as this SQL appears on TOP in the section of the "ordered by gets" of the Statspack Report.

The IDT Table has 64 HASH partitions partitioned on field Account(A/c) Number. This is a Bank Accounts Interest processing Program & 1 Record gets inserted per Account into IDT Table per Run. Since Account Number is unique per account, could the INSERTs with different A/c Numbers still be accessing the SAME Block?

Any Other ideas? ... Will share the Statspack Report, if needed.



-- Received on Sun Feb 17 2008 - 04:10:43 CST

Original text of this message