latch:cache buffers chains 原理 当一个数据块读入到sga中时,该块的块头(buffer header)会放置在一个hash bucket的链表(hash chain)中。该内存结构由一系列cache buffers chains子latch保护(又名hash latch或者cbc latch)。对Buffer cache中的块,要sele
latch:cache buffers chains 原理
当一个数据块读入到sga中时,该块的块头(buffer header)会放置在一个hash bucket的链表(hash chain)中。该内存结构由一系列cache buffers chains子latch保护(又名hash latch或者cbc latch)。对Buffer cache中的块,要select或者update、insert,delete等,都得先获得cache buffers chains子latch,以保证对chain的排他访问。若在过程中发生争用,就会等待latch:cache buffers chains事件。
产生原因: 1. 低效率的SQL语句(主要体现在逻辑读过高) 在某些环境中,应用程序打开执行相同的低效率SQL语句的多个并发会话,这些SQL语句都设法得到相同的数据集,每次执行都带有高 BUFFER_GETS(逻辑读取)的SQL语句是主要的原因。相反,较小的逻辑读意味着较少的latch get操作,从而减少锁存器争用并改善性能。注意v$sql中BUFFER_GETS/EXECUTIONS大的语句。 2.Hot block 当多个会话重复访问一个或多个由同一个子cache buffers chains锁存器保护的块时,热块就会产生。当多个会话争用cache buffers chains子锁存器时,就会出现这个等待事件。有时就算调优了SQL,但多个会话同时执行此SQL,那怕只是扫描特定少数块,也是也会出现HOT BLOCK的。
检查看下当前active的会话中产生的比较高buffer get的SQL: select * from (select sql_text,hash_value,buffer_gets/executions from v$sql where executions0 and hash_value in (select sql_hash_value from gv$session where statu
s='ACTIVE' )order by buffer_gets/executions desc ) where rownum
通过v$latch查看自实例启动以来cache buffers chains锁存器争用是否厉害: select round((misses/gets)*100)||'%',round(100*(immediate_misses/(immediate_gets+immediate_misses)))||' %' from v$latch where name='cache buffers chains'; 对于willing-to-wait,比较重要的是misses/gets,假如大于1%就应该发生争用了,大于10%,就有争用严重的情况了。对于no-wait模式,immediate_misses/(immediate_gets+immediate_misses)也一样。
通过查询子锁存器视图,看看是否有Hot Block,并且获取有Hot Block的子锁存器addr select * from (select addr,child#,gets,misses,sleeps from v$latch_children where name='cache buffers chains' order by sleeps desc ) where rownum
另外一种判断Hot block方法,是从当前等待latch:cache buffers chains事件的会话出发。通过v$session_wait视图,获得P1RAW即子锁存器的地址。通过重复观察v$session_wait视图,发现某个子锁存器地址较多地出现,那么该子锁存器管辖的chain可能有热块。 select p1,p1raw from v$session_wait where event='latch: cache buffers chains';
所以v$session的p1raw与x$bh的laddr,以及v$latch_children的addr是同样的东西,都是子锁存器的地址。大概思路是,通过子锁存器的热度来找到所管辖的对象,以及对象的热度。
通过子锁存器地址,即v$latch_children的addr字段,来获取这些子锁存器所管理的对象的文件号块号与热度。 注意到x$bh字典表中的tch字段表示的就是block的touch count,一般来说这个值越高那么这个块就越热,我们称这样的块就叫做热点块。 select hladdr,obj,(select object_name from dba_objects where (data_object_id is null and object_id=x.obj) or data_object_id=x.obj and rownum=1) as object_name,dbarfil,dbablk,tch from x$bh x where hladdr in ('00000000DA253C08','00000000DA380310') order by tch desc;
根据FILE#,dbablk来找出对应对象。 select * from dba_extents where file_id=10 and 36643122 between block_id and block_id + blocks - 1;
直接通过v$bh视图直接查找数据库热点块,从而找到热点的对象。 select * from (select hladdr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum 0 GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY SUM(TCH) DESC) WHERE ROWNUM
查看引起latch: cache buffers chains的sql select * from (select count(*),sql_id,nvl(o.object_name,ash.current_obj#) objn,substr(o.object_type,0,10) otype, CURRENT_FILE# fn,CURRENT_BLOCK# blockn from v$active_session_history ash,all_objects o where event like 'latch: cache buffers chains' and o.object_id (+)= ash.CURRENT_OBJ# group by sql_id, current_obj#, current_file#, current_block#, o.object_name,o.object_type order by count(*) desc )where rownum select sql_fulltext from v$sqlarea where sql_id='&sqlid';
解决方法 1.优化SQL,如优化nested loop join,如果有可能使用hash join代替nested loop join。 2.可以利用对热块索引进行hash分区,或者使用hash簇的方式减缓热块现象。 3.调整表的pctfree值,将数据尽可能的分布到多个块中,但相同的查询要扫更多块,有负面作用。 4.并行查询是直接读数据文件,不经过SGA,即direct path read,所以就不存在锁存器争用的情况了。但其一般是为了大量数据读取而使用的,不作为一般的解决方案。 5.等问题自己消失。有时当出现latch争用时,故障时刻确实没有较好的方式解决,找到病因才是关键。
附录:查看cache buffers chains有多少个子锁存器 Select count(*) from v$latch_children where name = 'cache buffers chains';
找出前10的热点块对象: select /*+rule*/ owner,object_name from dba_objects where data_object_id in (select obj from (select obj from x$bh order by tch desc) where rownum
Oracle11g联机文档中摘录: The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block). To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch. This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers: SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH FROM X$BH WHERE HLADDR = 'address of latch' ORDER BY TCH; X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block. Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query several times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment. After you have identified the hot block, you can identify the segment it belongs to with the following query: SELECT OBJECT_NAME, SUBOBJECT_NAME FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = &obj; In the query, &obj is the value of the OBJ column in the previous query on X$BH.
Latch: cache buffers chains Description: Blocks in the buffer cache are placed on linked lists (cache buffer chains) which hang off a hash table. The hash chain that a block is placed on is based on the DBA and CLASS of the block. Each hash chain is protected by a single child latch. Processes need to get the relevant latch to allow them the scan a hash chain for a buffer so that the linked list does not change underneath them. Contention: Contention for these latches can be caused by: - Very long buffer chains. There is a known problem that can result in long buffer chains - - very very heavy access to a single block. This would require the application to be reviewed. - To identify the heavily accessed buffer chain look at the latch stats for this latch under

如何有效监控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异,选择时需考虑项目规模和未来扩展性。

在需要高可用性、高级安全性和良好集成性的企业级应用场景下,应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能,如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色,支持内存优化表和列存储索引。

mySqlManagesCharacterSetsetSandCollationsyutusututf-8asthEdeFault,允许ConfigurationAtdataBase,table和columnlevels,AndrequiringCarefullageLignmentToavoidMismatches.1)setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2)conformentcollecharactersettersetertersetcollatertersetcollationcollation

MySQL触发器是与表相关联的自动执行的存储过程,用于在特定数据操作时执行一系列操作。1)触发器定义与作用:用于数据校验、日志记录等。2)工作原理:分为BEFORE和AFTER,支持行级触发。3)使用示例:可用于记录薪资变更或更新库存。4)调试技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5)性能优化:避免复杂操作,使用索引,管理事务。

在MySQL中创建和管理用户账户的步骤如下:1.创建用户:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限;4.优化权限:使用SHOWGRA

MySQL适合快速开发和中小型应用,Oracle适合大型企业和高可用性需求。1)MySQL开源、易用,适用于Web应用和中小型企业。2)Oracle功能强大,适合大型企业和政府机构。3)MySQL支持多种存储引擎,Oracle提供丰富的企业级功能。

MySQL相比其他关系型数据库的劣势包括:1.性能问题:在处理大规模数据时可能遇到瓶颈,PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性:水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制:在高级功能上不如PostgreSQL和Oracle,某些功能需要更多自定义代码和维护。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境

适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。

DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

VSCode Windows 64位 下载
微软推出的免费、功能强大的一款IDE编辑器

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)