Re: latch: cache buffers chains causing delays

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: 2006-01-12 19:35:06
Message-id: 039c01c617a6$eb732640$71bc21c8@porgand

Hi,

Tanel.

> We have an application that has, since last weekend,
> started getting a lot of 'latch: cache buffers chains'

0.07 seconds spend on cache buffers chains when the job took 840 sec total is not a lot...

Tkprof output showed that you are spending about 830 seconds of it burning CPU for fetching. So probably your execution plans changed after the possible analyze last weekend to maybe some nested loops where hashing was done before?

As you're on 10g, you should have a history accessible from ASH views or EM to compare the profiles for this statement (see buffer_gets from DBA_HIST_SQLSTAT and compare values in DBA_HIST_SQL_PLAN).

> 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

Tanel.

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

Received on Thu Jan 12 2006 - 19:35:06 CST