latch: cache buffers chains causing delays

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: 2006-01-12 18:43:10
Message-id: 20060112174310.13176.qmail@web52807.mail.yahoo.com

We have an application that has, since last weekend, started getting a lot of 'latch: cache buffers chains' wait event. From 10046 trace (level 12), we have found the following SQL (table names changed), that has the most elapsed time.

This application spawns 7 threads simultaneously, and each thread processes 1 partition of the table (eg: T06 below), based on a thread id. Why should there be a wait for the above event even though the table is partitioned and each thread accesses a different set of data? This issue has caused our loads to get delayed since last 5-6 days. BTW, it's 10.1.0.4

SELECT *
FROM TableA_raw
WHERE A_key IN (
  SELECT A_key
  FROM TableA_unique_list PARTITION(T06)   WHERE processed_flag = :b1
    AND thread_id = :b2 )

call     count       cpu    elapsed       disk     
query    current        rows

------- ------ -------- ---------- ----------

• ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 0.01 0 0 0 0 Fetch 1 829.89 839.99 0 4002133 0 195
  ------- ------ -------- ---------- ----------
  
• ---------- ---------- total 3 829.91 840.01 0 4002133 0 195

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS
Parsing user id: 85 (recursive depth: 2)

Elapsed times include waiting on following events:

 Event waited on                             Times  

Max. Wait Total Waited

• Waited
  ---------- ------------
  latch: cache buffers chains 134 0.01 0.07

  ---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 12 2006 - 18:43:10 CST