当前位置: 开发笔记 > 数据库 > 正文

WAITEVENT:latch:cachebufferschains

作者：yyjj7212 | 来源：互联网 | 2018-06-06 23:13

关于CACHEBUFFERSCHAINS描述CACHEBUFFERSCHAINSlatchisacquiredwhensearchingfordatablockscachedinthebuffercache.SincetheBuffercacheisimplementedasasumofchainsofblocks,eachofthosechainsisprotectedbyachild

关于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

关于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
/*&＃43; RULE */
       e.owner ||'.'|| e.segment_name  segment_name,
       e.extent_id  extent#,
       x.dbablk - e.block_id &＃43; 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 &＃43; 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)

推荐阅读

数据库
SQL查询技巧：合并多行数据为单行展示

探讨如何通过SQL查询将来自多个表的多行信息整合到同一行中展示，特别适用于需要汇总特定商品所有相关信息的场景。 ... [详细]

蜡笔小新 2024-12-17 22:47:34
oracle
Oracle触发器在PL/SQL中的调试方法

本文旨在详细介绍如何在PL/SQL环境中调试Oracle数据库中的触发器。虽然触发器能够实现某些复杂的功能，但其使用可能增加系统的维护难度。因此，本文不仅提供技术指导，还讨论了触发器使用的利弊。 ... [详细]

蜡笔小新 2024-12-17 19:33:58
数据库
PHP 中 preg_match 函数的 isU 修饰符详解

本文详细解析 PHP 中 preg_match 函数中 isU 修饰符的具体含义及其应用场景，帮助开发者更好地理解和使用正则表达式。 ... [详细]

蜡笔小新 2024-12-17 13:35:59
数据库
SQL技巧：批量删除用户自定义表和存储过程

当面临数据库清理任务时，若无删除或重建数据库的权限，可以通过编写SQL脚本来实现批量删除用户自定义的数据表和存储过程。本文将详细介绍如何构造这样的SQL脚本。 ... [详细]

蜡笔小新 2024-12-17 13:24:06
数据库
深入分析十大PHP开发框架

随着PHP技术的发展，各类开发框架层出不穷，成为了开发者们热议的话题。本文将详细介绍并对比十款主流的PHP开发框架，旨在帮助开发者根据自身需求选择最合适的工具。 ... [详细]

蜡笔小新 2024-12-17 11:15:55
oracle
Oracle性能提升：深入探讨SQL优化与类型转换的影响

本文详细分析了在Oracle数据库中如何通过正确的数据类型匹配来避免不必要的类型转换，从而提高SQL查询效率。 ... [详细]

蜡笔小新 2024-12-17 09:41:09
数据库
iTOP4412开发板QtE5.7源码编译指南

本文详细介绍了如何在iTOP4412开发板上编译QtE5.7源码，包括所需文件的位置、编译器设置、触摸库编译以及QtE5.7的完整编译流程。 ... [详细]

蜡笔小新 2024-12-17 08:19:54
数据库
Java远程教学系统毕业设计：源代码、MySQL数据库及文档

本文介绍了Java语言开发的远程教学系统，包括源代码、MySQL数据库配置以及相关文档，适用于计算机专业的毕业设计。系统支持远程调试，采用B/S架构，适合现代教育需求。 ... [详细]

蜡笔小新 2024-12-17 05:12:29
sql
MySQL学习指南：从基础到高级

本文档提供了MySQL的全面学习指南，涵盖从数据库的基本概念到高级特性的详细解析，包括SQL基础命令、数据完整性约束、查询技术、内置函数、存储过程、视图管理、事务处理和索引优化等内容。 ... [详细]

蜡笔小新 2024-12-16 22:06:09
sql
深入理解SQL Server中的聚集与非聚集索引

本文探讨了SQL Server数据库中两种主要的索引类型——聚集索引和非聚集索引，通过对比分析它们的特点及应用场景，旨在帮助读者更好地理解和利用这两种索引以优化查询性能。 ... [详细]

蜡笔小新 2024-12-16 20:18:16
sql
探索Pandas中的SQL式数据操作技巧

虽然SQL因其直观易学的语法受到广泛欢迎，但转向Pandas进行数据处理时，初学者可能会感到不适应。本文旨在通过一系列实例，展示如何在Pandas中实现类似SQL的数据查询功能。 ... [详细]

蜡笔小新 2024-12-16 19:23:42
oracle
Hibernate 中的高效批量更新与删除策略

本文探讨了如何利用 Hibernate 进行高效的批量更新和删除操作，包括直接使用 Hibernate API 的方法及其局限性，以及如何通过 JDBC 或存储过程实现更优的性能。 ... [详细]

蜡笔小新 2024-12-16 19:15:43
数据库
MySQL 使用技巧与心得

本文总结了MySQL的一些实用技巧，包括查询版本、修改字段属性、添加自动增长字段、备份与恢复数据库等操作，并提供了一些常见的SQL语句示例。 ... [详细]

蜡笔小新 2024-12-16 16:30:43
数据库
解决Java Web项目连接MySQL时驱动未加载问题

作为一名Java Web开发新手，我在尝试将项目部署至Tomcat服务器并连接MySQL数据库时遇到了驱动加载失败的问题。经过一番排查和努力，最终找到了解决方案。 ... [详细]

蜡笔小新 2024-12-16 13:37:04
insert
sqlserver动态分区方案例子

sqlserver动态分区方案例子当我们存储的数据量比较大时，比如超过千万，上亿级别时单纯的使用索引可能效果不明显了，此时我们可以考虑采 ... [详细]

蜡笔小新 2024-12-16 13:29:29

yyjj7212

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章