【Oracle技术文章】-内存闩锁分析(1)

云贝教育 2022-11-23

551

一、Latch通用数据结构

讲解闩锁前我们先讲一个数据结构，也就是散列表（hash table ）,下图为拉链法表示的散列表

这里不用关注散列表如何实现。我们关注其特性

l给定key,传入到hash函数，返回value代表其内存地址，所以查询非常快

l如果不同的key值，返回相同的内存地址，表示发生了hash碰撞，碰撞的key值存储在上图的链表中

l链表的首段挂在一个数组后面，数组里存放的hash函数的value，链表里存放的是key

lKey的多少决定了数组的大小，如果固定数组大小，新写入一个key，如果发生碰撞，则需要维护链表，插入到链表的尾端。

讲完这个散列表，跟我们的闩锁有什么关系呢。

简单回顾一下oracle解析步骤

1.加载到共享池- SQL 源代码被加载到 RAM 中进行解析。（“硬”解析步骤）

2.语法分析- Oracle 分析语法以检查拼写错误的 SQL 关键字。

3.语义解析- Oracle 验证字典中的所有表和列名称，并检查您是否有权查看数据。

4.查询转换——Oracle 将把复杂的 SQL 转换为更简单、等效的形式，并酌情用物化视图替换聚合。在 Oracle 的早期版本中，必须为实体化视图重写设置query_rewrite=true 参数。

5.优化- Oracle 然后根据您的模式统计信息（或者可能来自 10g 中的动态采样的统计信息）创建一个执行计划。Oracle 在此期间构建成本决策树，选择感知成本最低的路径。

6.创建可执行文件- Oracle 使用本机文件调用构建可执行文件以服务 SQL 查询。

7.获取行- Oracle 然后执行对数据文件的本地调用以检索行并将它们传递回调用程序。

当载入一条SQL文本的时候，如果库缓存中存在此SQL文本，表示是软解析，跳过上面的步骤1，如果库缓存中不存在，则表示是硬解析。

上面的步骤涉及到“存在”和“不存在”，所以是一个find 问题。此时hash表这个数据结构就有了用武之地。

上图演化为：

Library cache:库缓存

Library cache latch，库缓存闩锁。

为什么需要这个闩锁呢。其实就是问共享池内存数据为什么需要被保护。也就是为什么内存数据会涉及到DML，如果是只读的，且不修改任何属性，也就不存在问题。

我们知道共享池用于缓存执行计划，数据字典缓存，SQL结果集和函数缓存，而缓存是有大小的，那些使用次数最少的缓存会从内存中老化退出。也就是针对上图的hash table，链表中的数据会被清除掉，而新的缓存又不断加入进来，所以这个链表会动态变化，修改链表的next值等于next的next的时候(其实是双向链表，所以还有last),如果这时还有会话去访问此链表，那么就会有问题。相当于读的时候，不能写，写的时候不能读。这不就是共享锁和排它锁吗？

但是Oracle的锁(lock)，是用于保护磁盘数据，如果要处理高速内存数据，机制太慢，我们需要一个轻量级的内存锁-闩（lacth），要求获取和释放时间都极短，去保护内存的数据。Lock最主要是需要排队，而latch谁在一个时间片内抢到了就算谁的。

锁和闩分别对应视图v$lock和v$latch。除此之外我们还有互斥锁，pin锁，DDL锁，自定义锁。我们的hash table的数组上，每一个桶都有一个互斥锁（如果是保护lock的latch,则每个桶上还会有latch锁，比如enqueue hash chains），而pin锁会锁链表上的每一个节点。比如链表节点存储了执行计划，为了防止你正在使用执行计划EXECUTE SQL的时候，系统把此节点从链表删除.

我们主要关注桶前面的library cache latch锁

Latch 分类

1.愿意等待:如果获取不到闩，会话休眠，然后再次尝试。休眠时间称之为latch free wait time。library cache latches主要是愿意等待模式

2.立即：如果当前进程获取不到闩，转而获取另一个闩，直到所有闩的都获取失败。

Latch视图分析

查询v$latch有如下字段

Addr 地址

latch# 等于v$latch_children.LATCH#

level# 级别

name 闩锁名称

hash 等于v$latch_children.HASH

gets 当前进程以愿意等待模式成功获取到闩锁的次数，如果闩自旋了1千次，才最终获取到闩锁，gets+1而不是+1001，

misses 当前进程以愿意等待模式尝试获取闩锁，当第一次尝试获取就失败了，则missed+1,并开始自旋

sleeps进程以愿意等待模式尝试获取闩锁，第一次获取失败，然后自旋，几千次循环都没成获取，则开始睡眠，sleeps+1。

immediate_gets 进程以立即模式获取闩锁，第一次尝试获取就获取成功，则immediate_gets+1

immediate_misses 立即模式获取失败

waiters_woken

waits_holding_latch

spin_gets 进程以愿意等待模式尝试获取闩锁，首次获取失败，然后开始自旋。如果在自旋里成功获取闩锁，spin_gets+1

wait_time 进程总等待时间，单位一般为微秒

参数增加顺序：

misses-> spin_gets自旋也没获取到-> sleeps

正常情况misses= spin_gets+sleeps

获取闩锁的顺序

1.成功获取，然后释放

2.获取失败->自旋->自旋超过spin_count，开始从CPU调度出去，开始休眠->休眠完毕唤醒，继续获取->（1获取又失败misses+1，->自旋->……睡眠时间上升）->（2成功获取gets+1,然后释放）

为什么要“自旋”？

任务调度出CPU又调度回来涉及到上下文切换，而正常情况下闩锁获取释放速度很快，所以我在循环里一直获取，正常情况下循环几次就获取到了，如果循环个几千次，还是获取不到，才开始睡眠。这样平均等待时间比前者（指获取不到就开始睡眠）要快很多。

进程调度出CPU需要切换上下文，耗时较长，所以循环获取

监测latch: library cache

因为这个库缓存闩锁比较少见，因此不去分析latch视图了，我们后面详细分析快块缓冲区闩锁现象，下面给出监测方法：这些SQL和会话都是有问题的

查询等待事件

SELECTsid, serial#, osuser, program
  FROM v$session
 WHEREsidIN (SELECT blocking_session
                 FROM v$session
                WHERE event = 'latch: library cache');
复制

查询高子游标计数和高版本sql

SELECTDISTINCT sql_id FROM v$sql_plan WHERE child_number >= 300
SELECT sql_id, version_count, sql_text
  FROM v$sqlarea
 WHERE version_count > 300;
复制

查询高绑定变量sql

SELECTAVG(bind_count) avg_num_binds
  FROM (SELECT sql_id, COUNT(*) bind_count
          FROM v$sql_bind_capture
         WHERE child_number = 0
         GROUPBY sql_id);
SELECT *
  FROM (SELECT sql_id, COUNT(*) bind_count
          FROM v$sql_bind_capture
         WHERE child_number = 0
         GROUPBY sql_id)
HAVING bind_count >=300
复制

再次讨论latch: cache buffers chains

分析这个词

Latch:闩锁

Cache:缓存，比如从磁盘读入数据，放入缓存，下一次直接读缓存而不是磁盘

buffers：缓冲区，比如有写缓冲区，写满了之后，再统一将缓冲区写磁盘，

chains:链

当我们遇到cache buffers就知道是块缓冲区缓存，而不是库缓存

粒度

下图是块缓冲区的各个缓冲池示意图

来源：https://codeantenna.com/a/dBDXzPb3zQ

我们先接触一个词，granule(粒度)，BLOCK是数据库最小I/O单元，那么granule就是共享池最小分配进程的连续虚拟内存单元，也是访问数据的最小工作单元。比如我们的自动内存管理，共享池和块缓冲区的大小会动态调整，那么增减的单元就是granule。查询v$sga_resize_ops可以看到最近的调整结果

上图可以看到：

一个缓冲池有多个granule,granule内部分为《缓冲区头部数组》和《缓冲区链》，一个工作集可以跨多个granule。这里《缓冲区头部数组》其实就是我们之前看到的X$BH

查询粒度大小，我本地是4M

select * from v$sgainfo where name='Granule Size';
复制

查询共享池和块缓冲区大小

SELECT s.component
        ,s.current_size  1024  1024 current_size
        ,s.granule_size  1024  1024 granule_size
    FROM v$sga_dynamic_components s
   WHERE s.component in ('DEFAULT buffer cache','shared pool')


      COMPONENT   CURRENT_SIZE   GRANULE_SIZE
1   shared pool   240   4
2   DEFAULT buffer cache   28   4
复制

可以看到共享池240M，60个粒度，块缓冲区默认池28M,也就是7个粒度

查询块缓冲区粒度的链表

SELECT ge.grantype
      ,ct.component
      ,ge.granprev
      ,ge.grannum
      ,grannext
  FROM x$ksmge  ge
      ,x$kmgsct ct
 WHERE ge.grantype != 6
   AND ct.grantype = ge.grantype
   and ct.component='DEFAULT buffer cache'
      GRANTYPE   COMPONENT   GRANPREV   GRANNUM   GRANNEXT
1   9   DEFAULT buffer cache   0   63   64
2   9   DEFAULT buffer cache   63   64   65
3   9   DEFAULT buffer cache   64   65   66
4   9   DEFAULT buffer cache   65   66   67
5   9   DEFAULT buffer cache   66   67   68
6   9   DEFAULT buffer cache   67   68   69
7   9   DEFAULT buffer cache   68   69   0
复制

GRANNEXT指向下一个粒度的指针，GRANNUM是当前指针，从上图可以看到按顺序排列，所以可以得出结论，我的块缓冲区没有动态调整过，当然因为我启用的是手动SGA内存管理。缓冲池内的所有的GRANULE双向链表串联起来组成大的缓冲池。

--查询所有内存组件的粒度

SELECT ct.component
      ,COUNT(1) * 4
  FROM x$ksmge  ge
      ,x$kmgsct ct
 WHERE 1 = 1
   AND ct.grantype = ge.grantype
 GROUP BY ct.component
复制

--查询当前大小

  SELECT s.component
        ,s.current_size  1024  1024 current_size
        ,s.granule_size  1024  1024 granule_size
    FROM v$sga_dynamic_components s
   WHERE s.component in ('DEFAULT buffer cache','shared pool')
复制

--查询当前块缓冲区详细信息

select * from v$buffer_pool;
复制

LRU

我们的缓冲池里面的缓冲区个数是固定的，缓冲区链由工作集管理，当新的数据进入内存的时候，如何决定哪些缓冲区需要clear,LRU(Least recently used)算法就是最近最少被使用缓冲区应该被老化退出。

回到我们的工作集

  SELECT k.cnum_set --工作集中缓存区数量
        ,k.set_latch --cache buffer latch地址
        ,k.cnum_repl --缓冲区数量总和
        ,k.nxt_repl--最多使用的缓冲区,缓冲区链的热端（链表头部）
        ,k.prv_repl--最少使用的缓冲区，缓冲区链的冷端（链表尾部）
        ,k.cold_hd --缓冲区头部链表冷热分界点
        ,K.NXT_REPLAX--备用链头部，备用链比主链少了刷新脏块，获取和释放pin，所以从备用链获取可老化的缓冲区比主链更快
        --一般而言，备用链头部链接在k.prv_repl对应的主链的尾部
        ,K.PRV_REPLAX--备用链尾部
        ,k.addr--等于x$bh.set_ds 表示此工作集有多少缓冲区


   FROM x$kcbwds k;
复制

我们只关注主链，这个表的每一行就是一个工作集，由链表头部nxt_repl和链表尾部prv_repl决定的链表就是缓冲区头部链表，那么此链表的每一个节点就是代表一个个缓冲区。

缓冲区头部可以简单理解成缓冲区的指针，所以后面遇到缓冲区还是缓冲区头部，可以理解他们都是指代同一个东西，就是缓冲区。每个缓冲区内部里面有很多block。

上面的链表就是cache buffer lru chain 链表，每一个缓冲区上有一个TCH接触计数，这个TCH的修改机制比较复杂，这里不详细描述，所以我们简化一下，可以简单看成，链表左边的TCH较高，越往右越低，温度较低的缓冲区是从内存老化的优先选择对象。

(TCH计算逻辑并不是访问一次就+1,这里不详细讨论，只是大概表达意思)

而我们下面要讲到的cache buffer chain ，虽然节点还是一样的，都是缓冲区，不过他们能链接到一个链表上完全是因为hash 散列的结果，所以这个cache buffer chain链上的温度排列并不是左边的最高，右边的越低

Latch锁

现在我们有了一个LRU链表，决定哪些缓冲区需要老化，但是我们还需要前面讲到的hash table 结构去快速判断是物理读还是逻辑读。回想我们通过索引访问数据的过程，通过where条件对应的索引树，找到叶子节点对应的ROWID，ROWID是文件号，块号，块内行号组成的伪列，所以拿到ROWID就确定了是哪一个块，此次我们需要使用hash table，还是以上图举例

这里的key值简单等价为块号,传入的桶对应的链表就是缓冲区，也就是上图链表中的矩形节点。我们顺序读，读入一个BLOCK，然后通过散列函数，计算出它应该放入那个桶中，然后按某种规则加入到缓冲区链表中。

桶1	-------	缓冲区1	缓冲区2	缓冲区3
桶2	-------	缓冲区4	缓冲区5
桶3	-------	缓冲区6	缓冲区7
桶4	-------	缓冲区8
桶5	-------	缓冲区9
桶6	-------	缓冲区10	缓冲区11	缓冲区12	缓冲区13

举例：

现在我们传入磁盘block号,通过hash table得到了桶1，那么接下来应该选择放入桶1的三个缓冲区中的一个，具体选择哪一个就是上一节讲到的LRU链，现在我们把LRU链也画一下，假设我们有2个工作集

工作集1	缓冲区3	缓冲区5	缓冲区1	缓冲区10
	缓冲区8	缓冲区2	缓冲区7

工作集2	缓冲区4	缓冲区6	缓冲区9	缓冲区11
	缓冲区13	缓冲区12

颜色越淡，表示越冷，因此备选缓冲区是2，我们需要修改缓冲区2的数据，回顾我们的库缓存latch，修改前必须要对其加锁，所以改造一下我们的缓冲区hash链

latch1	桶1	-------	缓冲区	缓冲区	缓冲区
桶2	-------	缓冲区	缓冲区
latch2	桶3	-------	缓冲区	缓冲区
桶4	-------	缓冲区
latch2	桶5	-------	缓冲区
桶6	-------	缓冲区	缓冲区	缓冲区	缓冲区

我们必须首先获取桶1和桶2对应的latch1，然后再去修改桶1的缓冲区链表，这个latch就是标题的latch:cache buffers chains 既然是我们熟悉的latch锁，回想一下

Misses，spin_gets，sleeps这些关键词，一旦我们有两个SQL访问的块位于同一个latch管辖的桶内，这两个SQL又都在大量进行buffer gets，他们都会争抢latch锁，最后造成此等待事件。

此时我们假如获取到了latch1,同时通过LRU链我们选择把数据放在缓冲区2中，先pin住缓冲区2，如果不能pin住，则需要等待，此时触发等待事件 buffer busy wats 成功获取pin之后释放闩锁，再缓存我们的数据（这里都不考虑一致性读情况），然后获取闩锁，解除pin，再解除latch锁，完成访问。（LRU链也发生了更新，所以LRU上也会有LATCH锁去控制，这里不详细讨论）

重现latch: cache buffers chains

很多文章讲到这个等待事件，给出的解决方案是解决热点块现象，比如多个会话访问同一个块，不过热点块并不是决定因素，同一个buffer chain上的块+多会话+频繁访问才会出现。而buffer busy wait，才与热点块关系比较紧密。热点块以及块上TCH过高，只是说有可能有关系，我们现在来重现一下。

首先随便在latch链中找到一行数据（如果oracle能提供函数，传入块信息，返回latch信息，那么我就能自己造测试数据了）

SELECT addr
    FROM v$latch_children
   WHERENAME = 'cache buffers chains'
     ANDrownum = 1;
然后查一下这个buffer header 链上都有哪些块的数据
SELECT e.owner || '.' || e.segment_name segment_name
      ,e.file_id
      ,e.extent_id extent#
      ,x.dbablk
      ,x.dbablk - e.block_id + 1 block#
      ,x.tch
      ,l.child#
      ,l.gets
      ,l.misses
      ,l.sleeps
      ,l.wait_time  1000000  3600
      ,l.latch#
      ,l.hash
  FROM v$latch_children l
      ,x$bh             x
      ,dba_extents      e
 WHERE1 = 1
   AND l.addr = '000000006BDE6660'
   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
 ORDERBY x.tch DESC;
复制

SEGMENT_NAME	FILE_ID	EXTENT#	DBABLK	BLOCK#	TCH	CHILD#	GETS	MISSES	SLEEPS
SYS.AQ$_SUBSCRIBER_LWM	1	0	17432	1	4	1024	66047	0	0
SYS.C_OBJ#	1	17	12264	105	0	1024	66047	0	0
SYS.OPTSTAT_SNAPSHOT$	1	20	118530	3	0	1024	66047	0	0
SYS.SYS_C003253	1	0	20016	1	1	1024	66047	0	0

第一行块号是1，因此简单查询第一行就行（反复执行此SQL你会发现TCH会持续增长）

select * from SYS.AQ$_SUBSCRIBER_LWM where rownum=1;
复制

第二行是一个聚簇，与索引聚簇表有关，第4行是一个索引，这里我们没有函数去查询索引块对应的数据是哪一行，第三行是一个表，因此用第三行对应的表测试。

select * from dba_extents d where d.file_id=1  and d.extent_id=20;
复制

OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	EXTENT_ID	FILE_ID	BLOCK_ID	BYTES	BLOCKS
SYS	OPTSTAT_SNAPSHOT$		TABLE	SYSTEM	20	1	118528	1048576	128

查询此表分配了128个块

尝试从block_id 118528开始，找3个块，一直访问此数据，但是并没有增加接触计数

SELECT ROWID
      ,dbms_rowid.rowid_block_number(ROWID)
  FROM sys.optstat_snapshot$ s
 WHERE dbms_rowid.rowid_block_number(ROWID) - 118528 <= 3
   AND dbms_rowid.rowid_block_number(ROWID) - 118528 >= 0;
复制

因此直接按ROWID排序，取前1000行数据

select * from (select  * from sys.optstat_snapshot$ order by rowid ) where rownum<=1000;
复制

因此改造这两个SQL,期望重现latch锁等待事件

窗口1：

--SELECT userenv('sid') FROM dual;--470




declare
cursor a_cur is
select * from SYS.AQ$_SUBSCRIBER_LWM whererownum=1;




begin
  for i in1..1000000 loop
     for a_rec in a_cur loop
       null;
     endloop;
  endloop;
end;
复制

窗口2：

--SELECT userenv('sid') FROM dual; --467
declare
cursor a_cur is
select * from (select  * from sys.optstat_snapshot$ orderbyrowid ) whererownum<=1000;




begin
  for i in1..10000 loop
     for a_rec in a_cur loop
       null;
     endloop;
  endloop;
end;
复制

执行后查看等待事件表

SELECT * FROM v$session_wait s WHERE s.sid IN (467, 470)
复制

SID	SEQ#	EVENT	P1TEXT	P1	P1RAW
467	1938	direct path write temp	file number	201	00000000000000C9
470	431	latch: cache buffers chains	address	1809474512	000000006BDA63D0

成功重现latch: cache buffers chains

查看此p1的值，并不是我们上图取出来的'000000006BDE6660'

如果我们代入000000006BDA63D0会发现出现的段是其它两个系统段，

而且按我们期望，取消467的查询，却并不能缓解470上的latch锁现象

当我反复执行上面的查询时候，我发现接触计数高的是这两个段

SYS.AQ$_SUBSCRIBER_LWM 和 SYS.C_OBJ# ，只能说这两个对象不能独立访问，因此我们去实际环境中重现此等待事件。

1.找到一个latch锁，其中包含表数据

SELECT l.addr, e.owner || '.' || e.segment_name
  FROM v$latch_children l, x$bh x, dba_extents e
 WHERE1 = 1
   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
   AND e.segment_type = 'TABLE'
   AND e.tablespace_name <> 'SYSTEM'
复制

2.随便取一个latch,将此latch地址传入

SELECT e.owner || '.' || e.segment_name
      ,segment_name
      ,l.addr
      ,e.extent_id extent#
      ,x.dbablk
      ,x.dbablk - e.block_id + 1 block#
      ,x.tch
      ,l.child#
      ,l.gets
      ,l.misses
      ,l.sleeps
      ,l.wait_time  1000000  3600
      ,l.latch#
      ,l.hash
  FROM v$latch_children l
      ,x$bh             x
      ,dba_extents      e
 WHERE l.addr in ('0000001934656D68')
   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
复制

SEGMENT_NAME	DBABLK
MTL_SYSTEM_ITEMS_B	3219221
PO_REQUISITION_LINES_ALL	1738115

3.通过块号找到缓存的ROWID

SELECTROWID
      ,dbms_rowid.rowid_block_number(ROWID)
  FROM MTL_SYSTEM_ITEMS_B s
 WHERE dbms_rowid.rowid_block_number(ROWID) = 3219221;




SELECTROWID
      ,dbms_rowid.rowid_block_number(ROWID)
  FROM PO_REQUISITION_LINES_ALL s
 WHERE dbms_rowid.rowid_block_number(ROWID) =1738115
复制

4.开两个窗口循环取数

--2266
DECLARE
  CURSOR a_cur IS
    SELECT *
      FROM po_requisition_lines_all msi
     WHEREROWID  IN ('AAAdlDAIbAAGoWDAAA',
                     'AAAdlDAIbAAGoWDAAB',
                     'AAAdlDAIbAAGoWDAAD',
                     'AAAdlDAIbAAGoWDAAC',
                     'AAAdlDAIbAAGoWDAAG',
                     'AAAdlDAIbAAGoWDAAF',
                     'AAAdlDAIbAAGoWDAAE',
                     'AAAdlDAIbAAGoWDAAI',
                     'AAAdlDAIbAAGoWDAAH',
                     'AAAdlDAIbAAGoWDAAK',
                     'AAAdlDAIbAAGoWDAAJ',
                     'AAAdlDAIbAAGoWDAAM',
                     'AAAdlDAIbAAGoWDAAL');




BEGIN
  for i in1..100000 loop
  FOR a_rec IN a_cur LOOP
    NULL;
  ENDLOOP;
  endloop;
END;




--4045
DECLARE
  CURSOR a_cur IS
    SELECT *
      FROM mtl_system_items_b msi
     WHEREROWID  IN ('AAQ54NAKNAAMR8VAAA',
                     'AAQ54NAKNAAMR8VAAC',
                     'AAQ54NAKNAAMR8VAAE',
                     'AAQ54NAKNAAMR8VAAG',
                     'AAQ54NAKNAAMR8VAAI',
                     'AAQ54NAKNAAMR8VAAK',
                     'AAQ54NAKNAAMR8VAAM',
                     'AAQ54NAKNAAMR8VAAO');
BEGIN
  FOR i IN1 .. 100000LOOP
    FOR a_rec IN a_cur LOOP
      NULL;
    ENDLOOP;
  ENDLOOP;
END;
复制

5.查询等待事件

select * from v$session_wait s where s.sid in (4045,2266);
复制

SID	EVENT	P1RAW
2266	latch: shared pool	60292678
4045	latch: shared pool	00000000602922B8

没测试出来块缓冲区上的latch锁，却测试出来 latch: shared pool

共享池闩锁的争用是由于共享池容量不足、未共享 SQL 或数据字典的大量使用，查询硬解析

select * from V$SESS_TIME_MODEL s where s.SID  in (4045,2266) orderbyvaluedesc;
复制

硬解析花费时间并不严重，所以这个等待事件可能是我第一次装载此SQL，同时绑定变量过多导致，再次重复执行

SID	EVENT	P1
2266	SQL*Net message from client	1952673792
4045	latch free	107898301512

出现了latch free,这个包括了所有的闩锁现象，如果我们对此会话开启了跟踪，并打开原始trace文件，搜索latch free

WAIT #1: nam='latch free' ……number=228

搜索此number号

select * from   v$latchname l where l.latch#=228
          LATCH#       NAME       DISPLAY_NAME       HASH       CON_ID
1       228       cache buffers chains       cache buffers chains       3563305585       0
复制

就显示为实际的latch锁。

根据我们前面的判断，如果停掉2266会话的查询，而是单独查询4045，则不会出现跟buffer cache有关的闩锁，此时我们重新按12345的步骤测试一下，唯一的区别是不执行2266的查询，等待事件结果如下：

SID	EVENT	P1
2266	SQL*Net message from client	1952673792
4045	SQL*Net message from client	1952673792

符合我们的预测

如何解决latch锁导致的等待问题

回顾一下它的原理，我们有多个进程正在访问同一个buffer cache chain 上的缓冲区，一个获取到了，另一个就不得不等待。所以核心就是减少额外的buffer gets.

如何减少：

1.避免循环中重复读取相同的块，而且循环本身速度较快的话，闩锁现象越严重

2.检查索引效率，避免出现索引全扫描或者大段的索引范围扫描，本不需要扫描额外的buffer,由于糟糕的索引或者执行计划，额外扫描了这些block

3.避免全表扫描，不过oracle有机制控制，读大表的时候会避免更新TCH,我们期望这些大表不会污染我们的块缓冲区（可以放入回收池），一趟全表扫描并不可怕，因为我们的等待来源于逻辑读，全表扫描第一次全是物理读

如果你不能减小buffer gets,也就是说就是要这么频繁访问，那么第二步就是直接路径读，直接路径读不在赘述

如果你也不能采用直接路径读，那么我们只能从降低争用入手

1.如果争用发生在同一个块的不同行，尝试使用反向键，防止同时访问同一个块

2.因为latch散列只与块属性有关，所以你要么分时间段执行这两个SQL，要么增大块缓冲区，并期望能散列到别的latch上。

3.由2可知，对争用比较严重的段采用create as select ,期望生成新的BLOCK_ID并散列到不同的LATCH上（未测试）

oracle 链表 oracle执行计划缓冲区分析 sql优化

文章转载自云贝教育，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。