結論

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

cbc latch或cache buffer chains latch系列一

結論

測試

相關文章