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

from:http://www.freelists.org/post/oracle-l/high-latch-cache-buffers-chains-waits-in-10203-DB

From : "[emailÂ protected]" <[emailÂ protected]>
To : Oracle-L <[emailÂ protected]>
Date : Tue, 26 Feb 2008 18:25:02 +0100

Content-Type: multipart/alternative;

boundary="------------050402000304000607000905"

This is a multi-part message in MIME format.

--------------050402000304000607000905

Content-Type: text/plain; charset=ISO-8859-15; format=flowed

Content-Transfer-Encoding: 8bit

Hello,

I'm experiencing a problem with a high number of "latch: cache buffers

chains" waits in a customer database.

Environment:

Solaris 10 x86

Oracle EE 10.2.0.3

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.

Workaround:

- Wait till latch contention is resolved --> Customer is not appy about

that. One time it took over 2 hours

- restart instance --> fast workaround, but buffer and library cache are

lost.

I think it has something to do with hot blocks.

select event, count(*) sessions from v$session_wait

where state='WAITING' group by event order by 2 desc;

Â 2

EVENTÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â

Â Â Â SESSIONS

----------------------------------------------------------------Â ----------

latch: cache buffers chainsÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 312

read by other sessionÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 147

SQL*Net message from clientÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 69

rdbms ipc messageÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 14

....

Top ten sleeps for latches:

select CHILD#Â "cCHILD"

,Â Â Â Â Â ADDRÂ Â Â "sADDR"

,Â Â Â Â Â GETSÂ Â Â "sGETS"

,Â Â Â Â Â MISSESÂ "sMISSES"

,Â Â Â Â Â SLEEPSÂ "sSLEEPS"

from v$latch_children

where name = 'cache buffers chains'

and SLEEPS>5

order by 5, 1, 2, 3;

Â Â cCHILD sADDRÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â sGETSÂ Â Â sMISSESÂ Â Â sSLEEPS

---------- ---------------- ---------- ---------- ----------

Â Â Â Â 57645 000000056FE9F7E8Â Â Â Â Â 88080Â Â Â Â Â Â 1933Â Â Â Â Â Â 1683

Â Â Â Â 60748 000000056FF37020Â Â Â Â 225492Â Â Â Â Â Â 2220Â Â Â Â Â Â 1686

Â Â Â Â 16968 0000000571A75420Â Â Â Â Â 65938Â Â Â Â Â Â 1737Â Â Â Â Â Â 1689

Â Â Â Â 38058 0000000570EAF120Â Â Â Â 167974Â Â Â Â Â Â 2065Â Â Â Â Â Â 1731

Â Â Â Â 40474 0000000570F250A0Â Â Â Â Â 61998Â Â Â Â Â Â 2403Â Â Â Â Â Â 1754

Â Â Â Â 43329 0000000570FB0718Â Â Â Â 424070Â Â Â Â Â Â 2381Â Â Â Â Â Â 1857

Â Â Â Â Â 2177 000000057277A380Â Â Â Â Â 53419Â Â Â Â Â Â 2332Â Â Â Â Â Â 1861

Â Â Â Â Â 5334 000000057183D310Â Â Â Â 121589Â Â Â Â Â Â 2306Â Â Â Â Â Â 1865

Â Â Â Â 21423 0000000571B4EC98Â Â Â Â Â 57113Â Â Â Â Â Â 2407Â Â Â Â Â Â 1917

Â Â Â Â 46775 000000056FC8CBB8Â Â Â Â 123352Â Â Â Â Â Â 2667Â Â Â Â Â Â 2169

With the following statement we identified one Table that has the most

contention.

select

Â e.owner ||'.'|| e.segment_nameÂ segment_name,

Â e.extent_idÂ extent#,

Â x.dbablk - e.block_id + 1Â block#,

Â x.tch,

Â l.child#

from

Â sys.v$latch_childrenÂ l,

Â sys.x$bhÂ x,

Â sys.dba_extentsÂ e

where

Â x.hladdrÂ = 'sADDR from the result above'

and

Â e.file_id = x.file# and

Â x.hladdr = l.addr and

Â x.dbablk between e.block_id and e.block_id + e.blocks -1

Â order by x.tch desc ;

The Table has about 15.000.000 rows and a size of 9 GByte.

Do you have any idea how to work around the latch contention? Our idea

is to move the table to a tablespace without automatic segment space

management as to increase the freelists of the table. Other idea is to

partition the table. But partitioning is not licensed at the monent (so

we can not use it).

Oracle Support suggests DocID: *163424.1 **How To Identify a Hot Block

Within The Database Buffer Cache.

https://metalink.oracle.com/metalink/plsql/f?p=130:14:7971216487423334302::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,163424.1,1,1,1,helvetica

Thanks for any suggestion.

BjÃ¶rn

--------------050402000304000607000905

Content-Type: text/html; charset=ISO-8859-15

Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

</head>

Hello,

I'm experiencing a problem with a high number of "latch: cache buffers

chains" waits in a customer database.

Environment:

Solaris 10 x86

Oracle EE 10.2.0.3

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.

Workaround:

- Wait till latch contention is resolved --> Customer is not appy

about that. One time it took over 2 hours

or

- restart instance --> fast workaround, but buffer and library cache

are lost.

I think it has something to do with hot blocks.

select event, count(*) sessions from v$session_wait

where state='WAITING' group by event order by 2 desc;

Â 2Â

EVENTÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â

Â Â Â SESSIONS

----------------------------------------------------------------

----------

latch: cache buffers chainsÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 312

read by other sessionÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 147

SQL*Net message from clientÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 69

rdbms ipc messageÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â

14