Oracle数据库,update阻塞select问题分析

思考: Oracle数据库中，select会被阻塞吗？写会阻塞读吗？ 理论上，如果select时需要获取的Latch等锁资源无法立即获取到，就可能会出现等待，例如:

场景1:在Buffer Cache查找具体Buffer时，无法立即获取到CBC Latch资源，可能会出现等待。
场景2:SQL解析时，申请不到library cache lock、library cache pin等资源，可能会出现等待。

本文主要介绍场景1，并模拟update语句阻塞select的场景: 先看一下Oracle在Buffer Cache中如何找到需要的Buffer(逻辑读过程)?

1.进程根据要访问块的文件号、块号通过HASH算法计算出具体HASH值。
2.根据HASH值找到HASH Bucket。
3.搜索Bucket后的链表，查找对应的BH(Buffer Header)。
4.找到目标BH，从中取出Buffer的BA(Buffer Address)。
5.通过BA访问具体的Buffer.

由于SGA是公共内存，访问公共内存中任何数据都需要锁机制进行保护(Latch和Mutex)。 在上面的逻辑读过程中，搜索Bucket后的链表、访问BH中的BA，都需要Latch保护，这个Latch就是Cache Buffer Chain Lath(简称CBC Latch)。 那么如果select 执行时，申请不到Cache Buffer Chain Lath，select操作会被阻塞吗？ 实验如下: 创建测试数据:

create table cjc.t1(id int,name varchar2(20));
create index cjc.i_t1_id on cjc.t1(id);
insert into cjc.t1 values(1,'chen');
insert into cjc.t1 values(2,'ju');
insert into cjc.t1 values(3,'chao');
commit;
select * from cjc.t1;
    ID NAME
---------- --------------------
     1 chen
     2 ju
     3 chao

查看数据rowid,文件号，块号等。

select 
rowid,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#,
id,
name
from cjc.t1;
ROWID            FILE#      BLOCK#     ID NAME
------------------ ---------- ---------- ---------- --------------------
AAAVVhAAFAAAACHAAA        5         135      1 chen
AAAVVhAAFAAAACHAAB        5         135      2 ju
AAAVVhAAFAAAACHAAC        5         135      3 chao

根据DBA获取CBC Latch 地址

SQL> select HLADDR from x$bh where file#=5 and DBABLK=135;
HLADDR
----------------
000000008022FE60
000000008022FE60

查询此Latch保护的Buffer

set line 100
col owner for a10
col object_name for a15
select file#,DBABLK,owner,object_name,object_type from x$bh a,dba_objects b where HLADDR='000000008022FE60' and a.obj=b.data_object_id;
     FILE#     DBABLK OWNER     OBJECT_NAME     OBJECT_TYPE
---------- ---------- ---------- --------------- -------------------
     1    34695 SYS     I_OBJ2      INDEX
     1    52280 SYS     OBJ$         TABLE
     1    60956 SYS     I_IDL_UB11     INDEX
     1     4096 SYS     TABSUBPART$     TABLE
     2     3164 SYS     WRI$_ADV_TASKS  TABLE
     5      135 CJC     T1         TABLE
6 rows selected.

查询CBC Latch被获取的次数

col name for a20
select name,gets from v$latch_children where addr='000000008022FE60';
NAME               GETS
-------------------- ----------
cache buffers chains        452

读取第一行数据

select id,name from cjc.t1 where rowid='AAAVVhAAFAAAACHAAA';
    ID NAME
---------- --------------------
     1 chen

再次查询，访问次数多了2次

SQL> select name,gets from v$latch_children where addr='000000008022FE60';
NAME               GETS
-------------------- ----------
cache buffers chains        454

执行awr快照

exec dbms_workload_repository.create_snapshot();

通过oradebug工具，模拟latch: cache buffers chains不释放

SQL> oradebug setmypid
SQL> oradebug peek 0x000000008022FE60 4
[08022FE60, 08022FE64) = 00000000
锁定
SQL> oradebug poke 0x000000008022FE60 4 1
BEFORE: [08022FE60, 08022FE64) = 00000000
AFTER:    [08022FE60, 08022FE64) = 00000001

会话40:可以正常查询

SQL> select distinct sid from v$mystat;
SQL> select id,name from cjc.t1 where rowid='AAAVVhAAFAAAACHAAA';
    ID NAME
---------- --------------------
     1 chen

会话31:无法对这条数据执行update，因为申请X模式的cbc latch时出现等待

SQL> select distinct sid from v$mystat;
SQL> update cjc.t1 set id=100 where rowid='AAAVVhAAFAAAACHAAA';
卡住

会话29:select查询出现了等待，因为update 需要申请X模式的cbc latch，导致后面的select 申请不到S模式的cbc latch进一步被阻塞。

SQL> select id,name from cjc.t1 where rowid='AAAVVhAAFAAAACHAAA';
卡住

查询:

SQL> select * from dba_waiters;
no rows selected

被阻塞的会话29和31等待事件都是 latch: cache buffers chains

set line 300
col username for a10
col event for a30
select SID,USERNAME,STATUS,event,P1RAW,P2RAW from v$session where username='CJC';
       SID USERNAME   STATUS   EVENT                  P1RAW           P2RAW
---------- ---------- -------- ------------------------------ ---------------- ----------------
    29 CJC          ACTIVE   latch: cache buffers chains    000000008022FE60 00000000000000B1
    31 CJC          ACTIVE   latch: cache buffers chains    000000008022FE60 00000000000000B1

查看等待latch信息

select sid,p1raw,p2,p3,seconds_in_wait,wait_time,state from v$session_wait where event='latch: cache buffers chains';
       SID P1RAW            P2           P3 SECONDS_IN_WAIT  WAIT_TIME STATE
---------- ---------------- ---------- ---------- --------------- ---------- -------------------
    29 000000008022FE60       177        0          234       0 WAITING
    31 000000008022FE60       177        0          258       0 WAITING

执行awr快照

exec dbms_workload_repository.create_snapshot();

收集AWR，查看AWR: 具体查看:Events、SQL ordered by Gets、 Segments by Logical Reads等部分。   查看对于spid

select s.sid,s.serial#,p.spid from v$process p,v$session s where p.addr=s.paddr and s.username='CJC';
       SID    SERIAL# SPID
---------- ---------- ------------------------
    40       27 3010
    31       29 3122
    29       31 3234

收集dump

SQL> oradebug setospid 3234
Oracle pid: 33, Unix process pid: 3234, image: oracle@cjc-db-01 (TNS V1-V3)
SQL> oradebug dump processstate 8
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_3234.trc
cp /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_3234.trc /home/oracle/tmp/

收集ssd

[oracle@cjc-db-01 ~]$ sqlplus  -prelim "/as sysdba"
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug  dump systemstate 266
SQL> oradebug  dump systemstate 266
SQL> oradebug  tracefile_name
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5519.trc
cp /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_5519.trc /home/oracle/tmp/

简单看下

[root@cjc-db-01 ~]# vi /home/oracle/tmp/cjc_ora_3234.trc

搜索关键字:waiting for

waiting for 0x8022fe60 Child cache buffers chains level=1 child#=1797

可以看到等待的lath是0x8022fe60，也就是之前查到的000000008022FE60。 下面是客户端信息及Session Wait History信息。

client details:
  O/S info: user: oracle, term: pts/3, ospid: 3233
  machine: cjc-db-01 program: sqlplus@cjc-db-01 (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
Current Wait Stack:
 0: waiting for 'latch: cache buffers chains'
    address=0x8022fe60, number=0xb1, tries=0x0
    wait_id=29 seq_num=30 snap_id=1
    wait times: snap=22 min 47 sec, exc=22 min 47 sec, total=22 min 47 sec
    wait times: max=infinite, heur=22 min 47 sec
    wait counts: calls=0 os=0
    in_wait=1 iflags=0x2520
Wait State:
  fixed_waits=0 flags=0x28 boundary=0x813fb840/0
Session Wait History:
    elapsed time of 0.000028 sec since current wait
 0: waited for 'SQL*Net message to client'
    driver id=0x62657100, #bytes=0x1, =0x0
    wait_id=28 seq_num=29 snap_id=1
    wait times: snap=0.000004 sec, exc=0.000004 sec, total=0.000004 sec
    wait times: max=infinite
    wait counts: calls=0 os=0
    occurred after 0.000172 sec of elapsed time
 1: waited for 'SQL*Net message from client'
    driver id=0x62657100, #bytes=0x1, =0x0

没有对应的holder信息。 释放latch

SQL>  oradebug poke 0x000000008022FE60 4 0
BEFORE: [08022FE60, 08022FE64) = 00000001
AFTER:    [08022FE60, 08022FE64) = 00000000

参考:

《Oracle内核技术揭秘》
云贝教育-详解oracle中的latch:cache buffer chains

###chenjuchao 20240907### 欢迎关注我的公众号《IT小Chen》