Re: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 17 Feb 2008 10:10:43 -0000
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:
