cbc latch或cache buffer chains latch系列一

最新推荐文章于 2020-11-11 10:59:53 发布

clg10051

最新推荐文章于 2020-11-11 10:59:53 发布

阅读量149

点赞数

文章标签：数据库 java

结论

1,测试环境为oracle 10.2.0.5
2,cbc latch的子LATCH个数共计8192，其值可由参数_db_block_hash_latches控制
3，_db_block_hash_latches参数指定的值并非完全等于手工指值，还与其它因素有关，可能是BUFFER CACHE大小以及粒度大小
4，_db_block_hash_buckets参数控制共计多少个管理BUFFER CACHE的HASH BUCKET个数，
其值亦可以调整，同上理，也不能随意调整值，也与其它因素有关
5，_db_block_hash_buckets/_db_block_hash_latches等于多少个BUFFER CACHE HASH BUCKET使用一个CBC LATCH
6，一个表或索引或其它对象使用BUFFER CACHE，最终使用哪个CBC LATCH,由其文件号以及数据块号，进行HASH后使用指定的CBC LATCH
7，如果发生CBC LATCH等待事件后，其它并发查询以及DML会话（基于相同表的数据）会产生等待事件cursor: pin S wait on X以及CBC LATCH
8, 仅首次获取不到CBC LATCH的会话V$LATCH_CHILDREN的IMMEDIATE_MISSES会增加，而随后的相关会话不会增加此列值
9，v$latch_misses不会包括所有的V$LATCH的LATCH
10，手工用ORADEBUG POKE持CBC LATCH，从V$LATCH_MISSES看，其竞争的函数在kcbrls: kslbegin等待最高
11, 从目前测试看，SGA内存大小以及CPU_COUNT参数的调整，仍不会改变cbc latch个数以及_db_block_hash_buckets参数控制共计多少个管理BUFFER CACHE的
HASH BUCKET个数
仅是

测试

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

SQL> select latch#,name from v$latch where latch#=124;

LATCH# NAME
---------- --------------------------------------------------
124 cache buffers chains

SQL> select count(*) from v$latch_children where latch#=124;

COUNT(*)
----------
8192

---如下参数控制共计多少个CBC LATCH
_db_block_hash_latches 8192 Number of database block hash latches

---如下参数控制共计多少个管理BUFFER CACHE的HASH BUCKET个数
_db_block_hash_buckets 262144 Number of database block hash buckets

---可见一个CBC LATCH控制32个BUFFER CACHE HASH BUCKET的并发访问
SQL> select 262144/8192 from dual;

262144/8192
-----------
32

SQL> alter system set "_db_block_hash_latches"=888;
alter system set "_db_block_hash_latches"=888
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set "_db_block_hash_latches"=888 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 901775360 bytes
Fixed Size 2100424 bytes
Variable Size 234881848 bytes
Database Buffers 658505728 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

---可见最终cbc latch个数我理解可能还与其它因素有关，可能是buffer cache的粒度大小，先搁置这个问题，一会儿再研究这个问题
_db_block_hash_latches 1024

SQL> alter system set "_db_block_hash_buckets"=12000;
alter system set "_db_block_hash_buckets"=12000
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set "_db_block_hash_buckets"=12000 scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 901775360 bytes
Fixed Size 2100424 bytes
Variable Size 234881848 bytes
Database Buffers 658505728 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

--可见此参数与上述参数_db_block_hash_latches同理，最终真正的参数值，受限于其它因素，不是手工指定多少就是多少
_db_block_hash_buckets 16384

--parent latch addr不同于child latch addr
SQL> select latch#,name,addr from v$latch where latch#=124;

LATCH# NAME ADDR
---------- -------------------------------------------------- ----------------
124 cache buffers chains 0000000060014000

SQL> select count(*) from v$latch_children where latch#=124;

COUNT(*)
----------
1024

---buffer cache granule粒度大小
SQL> select component,granule_size/1024/1024 granule_mb from v$sga_dynamic_components;

COMPONENT GRANULE_MB
---------------------------------------------------------------- ----------
shared pool 4
large pool 4
java pool 4
streams pool 4
DEFAULT buffer cache 4
KEEP buffer cache 4
RECYCLE buffer cache 4
DEFAULT 2K buffer cache 4
DEFAULT 4K buffer cache 4
DEFAULT 8K buffer cache 4
DEFAULT 16K buffer cache 4

COMPONENT GRANULE_MB
---------------------------------------------------------------- ----------
DEFAULT 32K buffer cache 4
ASM Buffer Cache 4

13 rows selected.

SQL> select addr,latch#,level#,name,child# from v$latch_children where latch#=124 order by 5;

ADDR LATCH# LEVEL# NAME CHILD#
---------------- ---------- ---------- -------------------------------------------------- ----------
0000000094EB6E70 124 1 cache buffers chains 1
0000000094EB7038 124 1 cache buffers chains 2
0000000094EB7200 124 1 cache buffers chains 3
0000000094EB73C8 124 1 cache buffers chains 4
0000000094EB7590 124 1 cache buffers chains 5
0000000094EB7758 124 1 cache buffers chains 6
0000000094EB7920 124 1 cache buffers chains 7
0000000094EB7AE8 124 1 cache buffers chains 8
0000000094EB7CB0 124 1 cache buffers chains 9
0000000094EB7E78 124 1 cache buffers chains 10
0000000094EB8040 124 1 cache buffers chains 11

SQL> create table t_buffer(a int,b int);

Table created.

SQL> insert into t_buffer values(1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_number from t_buffer;

FILE_ID BLOCK_NUMBER
---------- ------------
4 181

SQL> select file#,dbarfil,hladdr from x$bh where file#=4 and dbablk=181;

FILE# DBARFIL HLADDR
---------- ---------- ----------------
4 4 0000000094F19A68

SQL> select addr,latch#,level#,name,child# from v$latch_children where latch#=124 and addr='0000000094F19A68';

ADDR LATCH# LEVEL# NAME CHILD#
---------------- ---------- ---------- -------------------------------------------------- ----------
0000000094F19A68 124 1 cache buffers chains 888

SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000094F19A68 4 1
BEFORE: [094F19A68, 094F19A6C) = 00000000
AFTER: [094F19A68, 094F19A6C) = 00000001

---可见若等待CBC LATCH,查询会话也会HANG住，即会阻塞读操作
SQL> select * from t_buffer;

SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid in (149);

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- ----------------------------------- ---------- ------------------------- ---------- -------------------- ---------- --------------------
149 latch: cache buffers chains 2498861672 address 124 number 0 tries

--可见p1即cbc latch 的child latch addr
SQL> select to_char('2498861672','xxxxxxxx') from dual;

TO_CHAR('
---------
94f19a68

---可见持有CBC LATCH不会阻塞写操作
SQL> select sid from v$mystat where rownum=1;

SID
----------
148

SQL> insert into t_buffer values(2,2);

1 row created.

---可见若等待CBC LATCH，第一个查询等待事件是CBC LATCH，而其它的查询会话等待事件是cursor:pin s wait on x
SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid in (149,148);

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- ----------------------------------- ---------- ------------------------- ---------- -------------------- ---------- --------------------
148 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
149 latch: cache buffers chains 2498861672 address 124 number 0 tries

SQL> select sid from v$mystat where rownum=1;

SID
----------
146

SQL> select * from t_buffer;

SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid in (149,148,146);

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- ----------------------------------- ---------- ------------------------- ---------- -------------------- ---------- --------------------
146 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
148 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
149 latch: cache buffers chains 2498861672 address 124 number 0 tries

SQL> select sid from v$mystat where rownum=1;

SID
----------
145

---可见若查询会话仅查询部分表记录，亦会产生等待事件cbc latch
SQL> insert into t_buffer values(4,4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_buffer where a=4;

SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid in (149,148,146,145);

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- ----------------------------------- ---------- ------------------------- ---------- -------------------- ---------- --------------------
145 latch: cache buffers chains 2498861672 address 124 number 0 tries
146 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
148 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
149 latch: cache buffers chains 2498861672 address 124 number 0 tries

SQL> select sid from v$mystat where rownum=1;

SID
----------
143

SQL> select * from t_buffer;

SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid in (149,148,146,145,143);

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- ----------------------------------- ---------- ------------------------- ---------- -------------------- ---------- --------------------
143 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
145 latch: cache buffers chains 2498861672 address 124 number 1 tries
146 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
148 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
149 latch: cache buffers chains 2498861672 address 124 number 0 tries

SQL> select sid from v$mystat where rownum=1;

SID
----------
142

SQL> update t_buffer set a=3 where a=2;

SQL> select sid,event,p1,p1text,p2,p2text,p3,p3text from v$session where sid in (149,148,146,145,143,142);

SID EVENT P1 P1TEXT P2 P2TEXT P3 P3TEXT
---------- ----------------------------------- ---------- ------------------------- ---------- -------------------- ---------- --------------------
142 latch: cache buffers chains 2498861672 address 124 number 0 tries
143 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
145 latch: cache buffers chains 2498861672 address 124 number 1 tries
146 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
148 cursor: pin S wait on X 4294590594 idn 6.3995E+11 value 2.1475E+10 where|sleeps
149 latch: cache buffers chains 2498861672 address 124 number 0 tries

6 rows selected.

SQL> select statistic#,name,class from v$statname where lower(name) like '%pin%' and statistic# in (336,337);

STATISTIC# NAME CLASS
---------- ---------------------------------------------------------------- ----------
336 buffer is pinned count 72
337 buffer is not pinned count 72

---恢复不再持有cbc latch
SQL> oradebug poke 0x0000000094F19A68 4 0
BEFORE: [094F19A68, 094F19A6C) = 00000001
AFTER: [094F19A68, 094F19A6C) = 00000000

---模拟持有CBC LATCH
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000094F19A68 4 1
BEFORE: [094F19A68, 094F19A6C) = 00000000
AFTER: [094F19A68, 094F19A6C) = 00000001

SQL> select addr,latch#,child#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name='cache buffers chains' and latch#=124 and child#=888;

ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- -------------- ----------------
0000000094F19A68 124 888 cache buffers chains 381 8 8 80 2

SQL> select count(distinct parent_name),count(*) from v$latch_misses;

COUNT(DISTINCTPARENT_NAME) COUNT(*)
-------------------------- ----------
369 3721

--可见不是所有的latch会记录到v$latch_misses中
SQL> select count(*) from v$latch;

COUNT(*)
----------
395

--可见LATCH的底层函数非常多
SQL> select count(*) from v$latch_misses where parent_name='cache buffers chains';

COUNT(*)
----------
207

SQL> select addr,latch#,child#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name='cache buffers chains' and addr='0000000094F19A68';

ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- ---------- ------------------------- ---------- ---------- ---------- -------------- ----------------
0000000094F19A68 124 888 cache buffers chains 208 0 0 79 1

SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000094F19A68 4 1
BEFORE: [094F19A68, 094F19A6C) = 00000000
AFTER: [094F19A68, 094F19A6C) = 00000001

--hang住查询
SQL> select * from t_buffer;

---可见immediate_misses增加1个
SQL> select addr,latch#,child#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name='cache buffers chains' and addr='0000000094F19A68';

ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- ---------- ------------------------- ---------- ---------- ---------- -------------- ----------------
0000000094F19A68 124 888 cache buffers chains 208 0 0 79 2

---可见新增的会话，不会增加immediate_misses列值
SQL> select * from t_buffer;

SQL> select addr,latch#,child#,name,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name='cache buffers chains' and addr='0000000094F19A68';

ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- ---------- ------------------------- ---------- ---------- ---------- -------------- ----------------
0000000094F19A68 124 888 cache buffers chains 208 0 0 79 2

---再从cbc latch底层的函数分析下cbc latch机制，可见kcbrls: kslbegin等待最高
SQL> select parent_name,nwfail_count,sleep_count,wtr_slp_count,location from v$latch_misses where parent_name='cache buffers chains' and nwfail_count<>0 or sleep_count<>0 order by 3 desc;

PARENT_NAME NWFAIL_COUNT SLEEP_COUNT WTR_SLP_COUNT LOCATION
-------------------------------------------------- ------------ ----------- ------------- ----------------------------------------------------------------
cache buffers chains 0 4 0 kcbrls: kslbegin

8 rows selected.

---
SQL> select * from t_buffer;

SQL> select parent_name,nwfail_count,sleep_count,wtr_slp_count,location from v$latch_misses where parent_name='cache buffers chains' and nwfail_count<>0 or sleep_count<>0 order by 3 desc;

PARENT_NAME NWFAIL_COUNT SLEEP_COUNT WTR_SLP_COUNT LOCATION
-------------------------------------------------- ------------ ----------- ------------- ----------------------------------------------------------------
cache buffers chains 0 5 0 kcbrls: kslbegin

8 rows selected.

---再看看不同CBC LATCH的子LATCH若阻塞，其函数的变化情况
SQL> select addr,latch#,level#,name,child# from v$latch_children where latch#=124 and rownum<=5 order by 5;

ADDR LATCH# LEVEL# NAME CHILD#
---------------- ---------- ---------- ------------------------- ----------
0000000094EB6E70 124 1 cache buffers chains 1
0000000094EB7038 124 1 cache buffers chains 2
0000000094EB7200 124 1 cache buffers chains 3
0000000094EB73C8 124 1 cache buffers chains 4
0000000094EB7590 124 1 cache buffers chains 5

SQL> oradebug poke 0x0000000094EB7038 4 1
BEFORE: [094EB7038, 094EB703C) = 00000000
AFTER: [094EB7038, 094EB703C) = 00000001

SQL> select tname from tab;

SQL> select parent_name,nwfail_count,sleep_count,wtr_slp_count,location from v$latch_misses where parent_name='cache buffers chains' and sleep_count>0;

PARENT_NAME NWFAIL_COUNT SLEEP_COUNT WTR_SLP_COUNT LOCATION
-------------------------------------------------- ------------ ----------- ------------- ----------------------------------------------------------------
cache buffers chains 0 7 0 kcbrls: kslbegin

SQL> select count(*) from v$segment_statistics;

COUNT(*)
----------
6090

SQL> select count(*) from v$segment_statistics where object_name='T_BUFFER';

COUNT(*)
----------
15

SQL> select owner,object_name,object_type,statistic_name,value from v$segment_statistics where object_name='T_BUFFER' order by 5 desc;

OWNER OBJECT_NAME OBJECT_TYPE STATISTIC_NAME VALUE
------------------------------ ------------------------------ ------------------ ---------------------------------------------------------------- ----------
SCOTT T_BUFFER TABLE logical reads 16
SCOTT T_BUFFER TABLE physical reads 2
SCOTT T_BUFFER TABLE db block changes 0
SCOTT T_BUFFER TABLE physical writes 0
SCOTT T_BUFFER TABLE physical reads direct 0
SCOTT T_BUFFER TABLE physical writes direct 0
SCOTT T_BUFFER TABLE gc cr blocks received 0
SCOTT T_BUFFER TABLE gc current blocks received 0
SCOTT T_BUFFER TABLE ITL waits 0
SCOTT T_BUFFER TABLE row lock waits 0
SCOTT T_BUFFER TABLE space used 0

OWNER OBJECT_NAME OBJECT_TYPE STATISTIC_NAME VALUE
------------------------------ ------------------------------ ------------------ ---------------------------------------------------------------- ----------
SCOTT T_BUFFER TABLE space allocated 0
SCOTT T_BUFFER TABLE gc buffer busy 0
SCOTT T_BUFFER TABLE segment scans 0
SCOTT T_BUFFER TABLE buffer busy waits 0

15 rows selected.

---再看下cbc latch个数与SGA内存大小的关系
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 860M
SQL>
SQL>
SQL>
SQL>
SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 860M
SQL> alter system set sga_target=300m scope=spfile;

System altered.

SQL> alter system set sga_max_size=300m scope=spfile;

System altered.

SQL> show parameter cpu_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 8
SQL> alter system set cpu_count=1;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2095992 bytes
Variable Size 96470152 bytes
Database Buffers 209715200 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 300M
sga_target big integer 300M

_db_block_hash_buckets 16384 Number of database block hash buckets
_db_block_hash_latches 1024 Number of database block hash latches

SQL> select component,granule_size/1024/1024 granule_mb from v$sga_dynamic_components;

COMPONENT GRANULE_MB
---------------------------------------------------------------- ----------
shared pool 4
large pool 4
java pool 4
streams pool 4
DEFAULT buffer cache 4
KEEP buffer cache 4
RECYCLE buffer cache 4
DEFAULT 2K buffer cache 4
DEFAULT 4K buffer cache 4
DEFAULT 8K buffer cache 4
DEFAULT 16K buffer cache 4

COMPONENT GRANULE_MB
---------------------------------------------------------------- ----------
DEFAULT 32K buffer cache 4
ASM Buffer Cache 4

13 rows selected.

SQL> alter system set sga_target=150m scope=spfile;

System altered.

SQL> alter system set sga_max_size=150m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 159383552 bytes
Fixed Size 2094800 bytes
Variable Size 75499824 bytes
Database Buffers 75497472 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.

---可见调整SGA大小后，LATCH的内存地址也进行了调整
SQL> oradebug poke 0x0000000094F19A68 4 0
ORA-00081: address range [0x094F19A68, 0x094F19A6C) is not readable

SQL> oradebug poke 0x0000000068B19A68 4 1
BEFORE: [068B19A68, 068B19A6C) = 00000000
AFTER: [068B19A68, 068B19A6C) = 00000001

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1868609/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1868609/

clg10051

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
cbc latch或cache buffer chains latch系列一

结论1,测试环境为oracle 10.2.0.52,cbc latch的子LATCH个数共计8192，其值可由参数_db_block_hash_latches控制3，_db_block_hash_latch...
复制链接

扫一扫