RE: latch: cache buffers chains causing delays

From: <Ravi_Kulkarni_at_Dell.com>
Date: Mon, 16 Jan 2006 04:22:24 -0600
Message-ID: <DB108C8A1E08EC448BB22A51DC9FAB62D84BAF@ausx3mpc111.aus.amer.dell.com>

Deepak,

Is it a RAC database ? Is it possible for you to run awr report for the time of the load. Curious to see the top 5 events portion.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Deepak Sharma Sent: Thursday, January 12, 2006 11:43 AM To: oracle-l_at_freelists.org
Subject: latch: cache buffers chains causing delays

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
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 16 2006 - 04:23:17 CST