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

If you are dealing with two separate problems it may be better to eliminate one of them. The first problem I would try to eliminate is the max processes. Start out with Shared Servers (a measure of success would be to know, by sampling, max number of active sessions); you could go to town by implementing connection pooling and multiplexing, but start with a simple shared server model. I have built perl/cgi based web apps and have successfully dealt with them using MTS/SS.

This may give you the breather necessary to see if the second problem is related to above or a separate issue.

Regards,
-Krish
President/Executive Architect, Quasar Database Technologies, LLC http://www.linkedin.com/in/quasardb

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn
Sent: Tuesday, February 26, 2008 6:39 PM To: bkaltofen_at_gmx.de
Cc: oracle-l_at_freelists.org
Subject: 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-spelunki ng/

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



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