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

最新推荐文章于 2024-03-02 10:37:05 发布

palm_civet

最新推荐文章于 2024-03-02 10:37:05 发布

阅读量880

点赞数

分类专栏： Oracle 文章标签： cache database session application table buffer

Oracle 专栏收录该内容

56 篇文章 0 订阅

订阅专栏

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

From : "bkaltofen@xxxxxx" <bkaltofen@xxxxxx>
To : Oracle-L <oracle-l@xxxxxxxxxxxxx>
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
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, 
 
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. 
<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> 
 
Thanks for any suggestion. 
 
Björn 
</body>
</html>

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