RE: latch: cache buffers chains causing delays
- From: <Ravi_Kulkarni@xxxxxxxx>
- To: <sharmakdeep_oracle@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Jan 2006 04:22:24 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Deepak Sharma
Sent: Thursday, January 12, 2006 11:43 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
