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

Unfortunately I would say this is a poster child example of how *not* to write an application. I understand that you didn't write it, but your efforts in trying to make it run well by attempting to tune the symptoms away is probably nothing less than a vertical climb with very little reward at the top.

The Metalink note query might be a bit much to run on a production db.  Especially one that is in peril. I suggest reading the documentation for a better one.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref796

Jeremy also has a nice little write up of a troubleshooting escapade: http://www.ardentperf.com/2007/09/13/cache-buffers-chains-and-latch-spelunking/

I would also suggest that if the issue appears to investigate the ASH report. It should contain details to point you in the right direction. The AWR report might even be enough - look for the busy segments.

Bottom line: Root Cause: Recommend to fix the application. There will much more reward at the end of that rainbow.

On 2/26/08, bkaltofen_at_gmx.de <bkaltofen_at_gmx.de> wrote:
> I'm experiencing a problem with a high number of "latch: cache buffers
> chains" waits in a customer database.
>
> Application Type: Web-Application which opens a session for each request
> (no comment. We have no influence on the application)
>
> Symptoms:
> "latch: cache buffers chains" waits go up
> number of sessions increases until "max processes" is reached, so no new
> connections can be established.
> Web-application stops responding, as no more sessions are possible
> I can not reproduce the issue by will and there is no test database at
> customer side.
>
> I think it has something to do with hot blocks.

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l