RE: high "latch: cache buffers chains" waits in 10.2.0.3 DB

Greg

            Sorry, I am jumping in to this thread late.  

1. Were you able to identify the latch children that are in contention and buffers protected by that latch children? Which one of the buffers has higher touch count? Which object those buffers belong to?
2. Were you able to identify SQL causing latch waits? If yes, can you map the objects identified in step (1) with explain plans in step (2).
3. What kind of server usage do you have just before the issue? Do you have high cpu usage during the issue? Was there any change to _spin_count parameters?
4. What platform? How many CPUs you have? What is the value for active session count ? select count(*) form v$Session where state='ACTIVE';
5. Do you have statspack or AWR report confirming that indeed time is spent waiting for latch free contention on cache buffers? Do you have sql trace file associating sql and latch free waits? Can you please send them to offline, if possible?

            Sorry to ask many questions, hopefully I am not repeating them. We need to identify root cause and then fix will be quick. Most probably, this latch contention is result of tight nested loops against one or many index accesses or against very small tables. Statistics can be used effectively avoid that critical path.  

Thanks  

Riyaj "Re-yas" Shamsudeen

ERP Financials DBA, New AT&T

--
http://www.freelists.org/webpage/oracle-l