high "latch: cache buffers chains" waits in 10.2.0.3 DB
Date: Tue, 26 Feb 2008 18:25:02 +0100
Message-ID: <47C44B6E.6030406@gmx.de>
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
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....
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 /*+ RULE */
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 -1order 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>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
I'm experiencing a problem with a high number of "latch: cache buffers chains" waits in a customer database.<br> <br>
Environment:<br>
Solaris 10 x86<br>
Oracle EE 10.2.0.3<br>
Application Type: Web-Application which opens a session for each request (no comment. We have no influence on the application)<br> <br>
Symptoms:<br>
"latch: cache buffers chains" waits go up<br> number of sessions increases until "max processes" is reached, so no new connections can be established.<br>
Web-application stops responding, as no more sessions are possible<br> I can not reproduce the issue by will and there is no test database at customer side.<br>
<br>
Workaround:<br>
- Wait till latch contention is resolved --> Customer is not appy
about that. One time it took over 2 hours<br> or<br>
- restart instance --> fast workaround, but buffer and library cache
are lost.<br>
<br>
I think it has something to do with hot blocks.<br> <br>
select event, count(*) sessions from v$session_wait<br> where state='WAITING' group by event order by 2 desc;<br> 2 <br>
EVENTSESSIONS<br>
--------------------------------------------------------------------------<br>
latch: cache buffers chains 312<br> read by other session 147<br> SQL*Net message from client 69<br> rdbms ipc message14<br>
....<br>
<br>
Top ten sleeps for latches:<br>
<br>
select CHILD# "cCHILD"<br> , ADDR "sADDR"<br> , GETS "sGETS"<br> , MISSES "sMISSES"<br> , SLEEPS "sSLEEPS" <br>
from v$latch_children <br>
where name = 'cache buffers chains'<br>
and SLEEPS>5<br>
order by 5, 1, 2, 3;<br>
<br>
cCHILD sADDR sGETS sMISSES sSLEEPS<br> ---------- ---------------- ---------- ---------- ----------<br> 57645 000000056FE9F7E8 88080 1933 1683<br> 60748 000000056FF37020 225492 2220 1686<br> 16968 0000000571A75420 65938 1737 1689<br> 38058 0000000570EAF120 167974 2065 1731<br> 40474 0000000570F250A0 61998 2403 1754<br> 43329 0000000570FB0718 424070 2381 1857<br> 2177 000000057277A380 53419 2332 1861<br> 5334 000000057183D310 121589 2306 1865<br>21423 0000000571B4EC98 57113 2407 1917<br> 46775 000000056FC8CBB8 123352 2667 2169<br> <br>
With the following statement we identified one Table that has the most contention.<br>
<br>
select /*+ RULE */<br>
e.owner ||'.'|| e.segment_name segment_name,<br> e.extent_id extent#,<br> x.dbablk - e.block_id + 1 block#,<br> x.tch,<br> l.child#<br>
from<br>
sys.v$latch_children l,<br> sys.x$bh x,<br> sys.dba_extents e<br>
where<br>
x.hladdr = 'sADDR from the result above'<br> and<br>
e.file_id = x.file# and<br> x.hladdr = l.addr and<br> x.dbablk between e.block_id and e.block_id + e.blocks -1<br> order by x.tch desc ;<br>
<br>
The Table has about 15.000.000 rows and a size of 9 GByte.<br> <br>
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).<br>
<br>
Oracle Support suggests DocID: <font face="helvetica"><strong>163424.1 </strong></font><font face="helvetica"><strong>How To Identify a Hot Block Within The Database Buffer Cache.<br> <a class="moz-txt-link-freetext" href="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">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</a><br> </strong></font><br>
Thanks for any suggestion.<br>
<br>
Björn<br>
</body>
</html>
--------------050402000304000607000905--
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 26 2008 - 11:25:02 CST