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

From: 조동욱 <ukja.dion_at_gmail.com>
Date: Sun, 17 Feb 2008 20:38:06 +0900
Message-ID: <43c2e3d60802170338i23241188raef34f98d16d69ab@mail.gmail.com>


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.
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- 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"
> 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.
>
> Cheers
>
> Vivek
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Feb 17 2008 - 05:38:06 CST

Original text of this message