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 -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> <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> EVENT SESSIONS<br> ---------------------------------------------------------------- ----------<br> latch: cache buffers chains 312<br> read by other session 147<br> SQL*Net message from client 69<br> rdbms ipc message 14<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-- -- //www.freelists.org/webpage/oracle-l