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

最新推荐文章于 2025-04-25 10:50:32 发布

chenoracle

最新推荐文章于 2025-04-25 10:50:32 发布

阅读量526

点赞数 5

文章标签：数据库 oracle

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/143822942

版权

图片.png

思考

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等部分。
图片.png
查看对于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》