Re: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs
Date: Sun, 17 Feb 2008 20:38:06 +0900
I would investigate active session history.(through dba_hist_active_sess_history)
If CBC latch contention is that dominant, active session history would show sessions which are waiting for "latch: cache buffers chains" event and the corresponding SQL statements.
2008/2/17, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:
> 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" <VIVEK_SHARMA_at_infosys.com>
> To: "Christian Antognini" <Christian.Antognini_at_trivadis.com>
> Cc: <oracle-l_at_freelists.org>
> 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"
> init.ora severely affected the performance Adversely & was removed
> Cause of the "latch: cache buffers chains" is believed to be "INSERT into
> 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)
> 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
> could the INSERTs with different A/c Numbers still be accessing the SAME
> Any Other ideas? ... Will share the Statspack Report, if needed.