WAIT EVENT: latch: cache buffers chains
来源:互联网 发布:deno在c语言中的意思 编辑:程序博客网 时间:2024/04/29 18:31
关于CACHE BUFFERS CHAINS描述
CACHE BUFFERS CHAINS latch is acquired when searching
for
data blocks cached
in
the buffer cache.
Since the Buffer cache is implemented as a
sum
of chains of blocks, each of those chains is protected
by a child of this latch when needs to be scanned. Contention
in
this latch can be caused by very heavy
access to a single block. This can require the application to be reviewed.
产生CACHE BUFFERS CHAINS原因
The main cause of the cache buffers chains latch contention is usually a hot block issue.
This happens when multiple sessions repeatedly access one or
more
blocks that are protected
by the same child cache buffers chains latch.
CACHE BUFFERS CHAINS 处理方法
1) Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object.
处理方法如下:
--通过报告确定latch: cache buffers chains 等待
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: cache buffers chains 74,642 35,421 475 6.1 Concurrenc
CPU
time
11,422 2.0
log
file
sync
34,890 1,748 50 0.3 Commit
latch
free
2,279 774 340 0.1 Other
db
file
parallel write 18,818 768 41 0.1 System I
/O
-------------------------------------------------------------
--找出逻辑读高sql
SQL ordered by Gets DB
/Inst
: Snaps: 1-2
-> Resources reported
for
PL
/SQL
code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 265,126,882
-> Captured SQL account
for
99.8% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
256,763,367 19,052 13,477.0 96.8
######## ######### a9nchgksux6x2
Module: JDBC Thin Client
SELECT * FROM SALES ....
1,974,516 987,056 2.0 0.7 80.31 110.94 ct6xwvwg3w0bv
SELECT COUNT(*) FROM ORDERS ....
--逻辑读大对象
Segments by Logical Reads
-> Total Logical Reads: 265,126,882
-> Captured Segments account
for
98.5% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
DMSUSER USERS SALES TABLE 212,206,208 80.04
DMSUSER USERS SALES_PK INDEX 44,369,264 16.74
DMSUSER USERS SYS_C0012345 INDEX 1,982,592 .75
DMSUSER USERS ORDERS_PK INDEX 842,304 .32
DMSUSER USERS INVOICES TABLE 147,488 .06
-------------------------------------------------------------
处理思路:
1.Look
for
SQL that accesses the blocks
in
question and determine
if
the repeated reads are necessary.
This may be within a single session or across multiple sessions.
2.Check
for
suboptimal SQL (this is the most common cause of the events)
look
at the execution plan
for
the SQL being run and try to reduce the
gets per executions
which
will minimize the number of blocks being accessed
and therefore reduce the chances of multiple sessions contending
for
the same block.
Note:1342917.1 Troubleshooting ‘latch: cache buffers chains’ Wait Contention
2) Decrease the buffer cache -although this may only help in a small amount of cases.
3) DBWR throughput may have a factor in this as well.If using multiple DBWR’s then increase the number of DBWR’s.
4) Increase the PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block.
找出热点对象
First determine
which
latch
id
(ADDR) are interesting by examining the number of
sleeps
for
this latch. The higher the
sleep
count, the
more
interesting the
latch
id
(ADDR) is:
SQL>
select
CHILD
# "cCHILD"
, ADDR
"sADDR"
, GETS
"sGETS"
, MISSES
"sMISSES"
, SLEEPS
"sSLEEPS"
from
v
$latch_children
where name =
'cache buffers chains'
order by 5, 1, 2, 3;
Run the above query a few
times
to to establish the
id
(ADDR) that has the most
consistent amount of sleeps. Once the
id
(ADDR) with the highest
sleep
count is found
then
this latch address can be used to get
more
details about the blocks
currently
in
the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest
sleep
count.
SQL> column segment_name
format
a35
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 =
'&ADDR'
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 ;
Example of the output :
SEGMENT_NAME EXTENT
# BLOCK# TCH CHILD#
-------------------------------- ------------ ------------ ------ ----------
SCOTT.EMP_PK 5 474 17 7,668
SCOTT.EMP 1 449 2 7,668
Depending on the TCH column (The number of
times
the block is hit by a SQL
statement), you can identify a hot block. The higher the value of the TCH column,
the
more
frequent the block is accessed by SQL statements.
5) Consider implementing reverse key indexes (if range scans aren’t commonly used against the segment)
- WAIT EVENT: latch: cache buffers chains
- latch: cache buffers chains
- latch: cache buffers chains
- latch: cache buffers chains
- latch: cache buffers chains
- latch: cache buffers chains
- cache buffers chains latch
- latch: cache buffers chains
- Cache buffers chains latch 与Cache buffers LRU chain latch
- Cache Buffers Chains and Latch Spelunking
- latch:cache buffers chains等待问题
- 模拟cache buffers chains latch实验
- latch: cache buffers chains等待导致CPU100%
- latch: cache buffers chains故障处理总结
- latch:cache buffers chains的优化思路
- latch: cache buffers chains---AWR实战分析
- latch: cache buffers chains (cbc)等待事件
- 深入理解latch: cache buffers chains
- Exchange常用端口
- Vim 的 tab 设置
- 让程序在崩溃时体面的退出之Unhandled Exception
- android蓝牙传输文件过程中关机,开机后状态栏没有对应通知
- 把类成员函数封装成线程API所需要的函数
- WAIT EVENT: latch: cache buffers chains
- tomcat部署项目中包含中文名称文件,报404找不到文件错误
- oracle创建表空间详解
- poj2987 最大权闭合图
- android 如何修改蓝牙搜索设备的最大数目
- 变革渠道营销策略
- Source Insight 3.X utf8支持插件震撼发布
- ZOJ 3813 Alternating Sum (牡丹江网络赛E题)
- vs2010中CString类的初始化及输出