Re: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs
- From: "조동욱" <ukja.dion@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- 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@xxxxxxxxxxxxxxxxxx>:
>
>
> 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@xxxxxxxxxxx>
> To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
> Cc: <oracle-l@xxxxxxxxxxxxx>
> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
