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

Other related posts: