一、简述
之前听到Hash会Hash 树, Hash 链, Hash 函数等等,在Oracle Buffer Cache里面,个人觉得应该理解 Hash表 这个概念
之前听到Hash会Hash 树, Hash 链, Hash 函数等等,在Oracle Buffer Cache里面,个人觉得应该理解 Hash表 这个概念
Buffer Cache 中的HASH
Hash Table:
https://en.wikipedia.org/wiki/Hash_table
Bucket: 要读取文件File1中的块Block100, 将文件号、块号通过Hash函数运算,得出一个Hash值bucketN。这个块在内存中(buffer)应该放在bucketN中
Cache Buffer Chain: 如果File2中的块Block200,将文件号、块号通过Hash函数运算得出的Hash值也是BucketN,就产生Hash collisions. Chain在这里是解决Hash 冲突的一种方式。将Buffer Header 地址连接在链表中,BucketN指向链表的表头。(双向链表),参见下图
![dAWFbyz3pJusgoKCgsJdRbG7FfoBAyZIYhjdZ4YBQRJY+FKBwDts9N6HYR+ooPBNRNFuhX6gCVrAgtwSYaQhMEETPYGrkiQ5HU5Hl0OueTKkceSeZ06H08AO3v5GQeGbjqLdCv1gZs2AANh7dT4McNNw3rdrn0ajGVIcoYxcEmvfzn0PL3n43vxiUFD4DzKC3aEUFIZEMBisqqr6+OOP5Ta4wxgBSxgAFixYkJOT0ycJTUHhW4ai3QpfI3ie93g8X+WZlLOHlbxHhW89inYrKCgofPNQ3IIKCgoK3zz+H5Tq5Mjmy6wAAAAAAElFTkSuQmCC](https://img-blog.csdnimg.cn/2022010619201781464.png)
搜索Buffer的步骤:
根据文件号,块号,计算出Hash值;根据Hash值找到Bucket;搜索Bucket的后的链表,找到目标BH, 并读取Buffer Address; 根据BA访问Buffer.
Latch: 保护内存的一种锁机制。
Cache Buffer Chain Latch(CBC latch): 保护data buffer的闩锁。访问链表的时候,需要申请获得这把锁。如果需要修改Buffer,也需要在CBC latch的保护下给Buffer加上Buffer Pin锁。一个CBC latch 保护多个Hash Bucket。
Buffer Pin: 需要对Buffer进行修改,则需要加Buffer pin独占锁;如果是逻辑读,则将Buffer Pin锁为共享模式。CBC latch 在Buffer Pin锁获取后释放。如果是修改Buffer则会以独占模式获得CBC latch.
bucket 和latch的数量可通过如下方式查看:
点击(此处)折叠或打开
- SELECT *
- FROM (SELECT I.KSPPINM NAME,
- I.KSPPDESC DESCRIPTION,
- CV.KSPPSTVL VALUE,
- CV.KSPPSTDF ISDEFAULT,
- DECODE(BITAND(CV.KSPPSTVF, 7),
- 1,
- 'MODIFIED',
- 4,
- 'SYSTEM_MOD',
- 'FALSE') ISMODIFIED,
- DECODE(BITAND(CV.KSPPSTVF, 2), 2, 'TRUE', 'FALSE') ISADJUSTED
- FROM SYS.X$KSPPI I, SYS.X$KSPPCV CV
- WHERE I.INST_ID = USERENV('Instance')
- AND CV.INST_ID = USERENV('Instance')
- AND I.INDX = CV.INDX
- AND I.KSPPINM LIKE '/_%' ESCAPE '/'
- ORDER BY REPLACE(I.KSPPINM, '_', ''))
- WHERE NAME in ('_db_block_hash_latches','_db_block_hash_buckets');
![LPxXq8AzmQfxQ2mwOjCe3QGxzERNzlTdrz2JwUbr1YLbRVHuX316QUAAAAwavC2bQAAAACMnjxv287LIDsFAAAAwHjBk4IBAAAAMHogaAAAAAAwev4PFLFQ7yeg8pUAAAAASUVORK5CYII=](https://img-blog.csdnimg.cn/2022010619201742268.png)
二、实验数据准备:
点击(此处)折叠或打开
- CREATE TABLE TEST_BBW(ID NUMBER, NAME VARCHAR2(16));
- INSERT INTO TEST_BBW SELECT LEVEL, DBMS_RANDOM.STRING('A',16) FROM DUAL CONNECT BY LEVEL <=40
- 0;
- SELECT * FROM DBA_TABLESPACE_USAGE_METRICS;
- SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FNO,
- DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLKNO,
- DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RNO,
- A.*
- FROM TEST_BBW A;
1. TEST_BBW数据分布情况。
![n8DqGmgLn4NYQAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619201857941.png)
2. 找1号文件,110882号块latch
点击(此处)折叠或打开
- SELECT hladdr FROM x$bh WHERE file#=1 and dbablk=110882;
![QAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619201810875.png)
---查找哪些buffer共享 此latch
点击(此处)折叠或打开
- SELECT file#, dbablk, owner, object_name,object_type
- FROM x$bh a, dba_objects b
- where a.obj = b.data_object_id
- and a.hladdr = '00007FF91B82F080';
![EwmQvsXbzHjx+HLmJOyLnd1icXOQvZUVr5TJXJxIUhwzDzAE844fBvQRK9xTAMw3QOfjOVYRiGmcJagWEYhpnizYLE1j2GYZgZgNcKDMMwzBTWCgzDMMwU1goMwzDMlP8PhcB7Dc1EDQIAAAAASUVORK5CYII=](https://img-blog.csdnimg.cn/2022010619201851126.png)
刚好有个tmp表的4号文件496498号块与1号文件110882号块共享CBC latch,但查询这个tmp表发现,数据里面最小的块是496499,那么这个496498只能是段头块。
![H9QTJGzQUOuKwAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619201870332.png)
。
3. 为了模拟段头块的频繁修改,采用先开启行移动,再对其进行段分配、段回收频繁操作。
3. 为了模拟段头块的频繁修改,采用先开启行移动,再对其进行段分配、段回收频繁操作。
session 1: 循环执行一个查询
点击(此处)折叠或打开
- SELECT * FROM V$MYSTAT WHERE ROWNUM=1; --11
- declare
- v_num number;
- begin
- for i in 1 .. 1000000 loop
- select id into v_num FROM TEST_BBW WHERE id=400;
- end loop;
- end;
session2: 尝试频繁更新段头块
SELECT * FROM V$MYSTAT WHERE ROWNUM=1;--199
点击(此处)折叠或打开
- declare
- v_num number;
- v_sql1 varchar2(200) := 'alter table gn.TMP2016062412548345 allocate extent';
- v_sql2 varchar2(200) := 'alter table gn.TMP2016062412548345 shrink space compact';
- v_sql3 varchar2(200) := 'alter table gn.TMP2016062412548345 shrink space';
- begin
- for i in 1 .. 100000 loop
- execute immediate v_sql1;
- execute immediate v_sql2;
- execute immediate v_sql3;
- end loop;
- end;
两个会话同时执行,
![skwVZa9N9St9Smu3fI4ayVatWJPF9Oqip9vjKrJcEaleu1HhU6lLJ09PEAJSNAIBiV5S1D2FJsdlnvQKavTSXQAAAACARrDjNAAAAAAWo3LHaQAAAACAq8DkEQAAAAAW438AyNntFwygTu8AAAAASUVORK5CYII=](https://img-blog.csdnimg.cn/2022010619201861977.png)
4. 不过并不很明显,不能说明问题。因为alter的速度慢?。两个会话同时执行查询操作(代码略)(这么频繁的读取块,并未出现Buffer Busy Wait等待)。
![x+EXSv1S2vcWgAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619201917340.png)
通过抓取session里面等待事件参数:
点击(此处)折叠或打开
- SELECT addr, sleeps
- FROM v$latch_children c, v$latchname n
- WHERE n.name = 'cache buffers chains'
- and c.latch# = n.latch#
- and sleeps > 5000 --v$session_event 等待次数大
- ORDER BY sleeps;
--找到tch最到的
点击(此处)折叠或打开
- SELECT file#, dbablk, class, state, TCH
- FROM X$BH
- WHERE HLADDR in('00007FF91BA018C8','00007FF91BA4FDD0');
![wNCdowkOPZ6ugAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619201986966.png)
--1号文件的110889号块dump下来
![C4hN3erObaZTAAAAAElFTkSuQmCC](https://img-blog.csdnimg.cn/2022010619201918426.png)
![p22g0jo+Po08XQRAECYSpiszbEgRBECR5JPV31xEEQZA48P+DNscPgFEzwQAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619201944073.png)
居然发生在索引上。看来之前的思路就有些不对,因为先前在id上建立了索引,执行的语句又只选取了ID,根本不用回表,所以表块上不会存在这个竞争。
再加到上面的步骤,找到与这个索引块使用相同CBC latch的对象(方式同上) 找到1号文件10416号块,IDL_UB1$
![s1vFuZ3YNwgAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619202017448.png)
取这个块下面第一行rowid
点击(此处)折叠或打开
- SELECT DBMS_ROWID.rowid_create(1,225,1,10416,1) FROM DUAL;
再开两个会话测试:
--SID:203
点击(此处)折叠或打开
- declare
- v_num number;
- begin
- for i in 1 .. 10000000 loop
- select OBJ# into v_num FROM IDL_UB1$ WHERE ROWID = 'AAAADhAABAAACiwAAB';
- end loop;
- end;
--SID:8
点击(此处)折叠或打开
- declare
- v_num number;
- begin
- for i in 1 .. 10000000 loop
- select id into v_num FROM TEST_BBW WHERE id = 400;
- end loop;
- end;
1千万次略多,执行一部分就断开了,下图能看到效果
![fwXJj4ArAJ2swMAgEm51hkQAACwENOtgwYAAHACBw0AAJMCBw0AAJMCBw0AAJMCBw0AAJPyH6BgBd5zs1gWAAAAAElFTkSuQmCC](https://img-blog.csdnimg.cn/2022010619202065015.png)
出现latch: cache buffers chains 竞争,可以调整(增大)_db_block_hash_latches,_db_block_hash_buckets 参数,打乱链的维护关系。更多的是需要为什么频繁的访问这些块,从业务逻辑与SQL本身着手。
Buffer Busy Wait: 频繁的DML与DML,或者频繁的DML与SELECT, 不可能只是SELECT造成的。
1. DML+DML 同时修改一个块里面不同的行(不同的会话,假如只一个会话,对某个块的数据做修改,是不会产生Buffer Busy Wait事件的)
点击(此处)折叠或打开
- SELECT * FROM v$mystat WHERE rownum =1;--14
- declare
- v_num number;
- begin
- for i in 1 .. 10000000 loop
- update TEST_BBW set name = 'id400' WHERE id = 400;
- if mod(i, 1000) = 0 then
- commit;
- end if;
- end loop;
- end;
-
- SELECT * FROM v$mystat WHERE rownum =1;--206
- declare
- v_num number;
- begin
- for i in 1 .. 10000000 loop
- update TEST_BBW set name = 'id399' WHERE id = 399;
- if mod(i, 1000) = 0 then
- commit;
- end if;
- end loop;
- end;
![DHZgBfjWn25csXp7UcoBC9e+ULLbEdRbls5hJ1rSSG4+mm6uoVaVLtWeSIutPRQ1X49hUVsdFL7nfGi0A4An8OU6x70tusYP5y89f3POc3zRYCzSVgFIVxfuSAQBAAXMfAIAn6FMAAJ6gTwEAeII+BQDgCfoUAIAn6FMAAJ78P2qsUenLFY0JAAAAAElFTkSuQmCC](https://img-blog.csdnimg.cn/2022010619202049679.png)
2. DML+SELECT
点击(此处)折叠或打开
- SELECT * FROM v$mystat WHERE rownum =1;--10
- declare
- v_num number;
- begin
- for i in 1 .. 10000000 loop
- update TEST_BBW set name = 'id400' WHERE id = 400;
- if mod(i, 1000) = 0 then
- commit;
- end if;
- end loop;
- end;
-
- SELECT * FROM v$mystat WHERE rownum =1;--400
-
- declare
- v_num number;
- v_name varchar2(100);
- begin
- for i in 1 .. 10000000 loop
- select name into v_name from test_bbw where id = 399;
- end loop;
- end;
![5Bc5aMAAAAABJRU5ErkJggg==](https://img-blog.csdnimg.cn/2022010619202054530.png)
因为是同一个块里面的不同的行,一个被更新,一个被select。 可以认为读不阻塞写,但写可能阻塞读。原因在于DML的时候,会对Buffer持有独占的读取。
如果出现执块竞争,可以将数据或者索引打乱,常见的反转主键存放,hash 全局索引等。
4. Oracle Core Essential Internals for DBAs and Developers Jonathan Lewis
5. https://en.wikipedia.org/wiki/Hash_table
5. https://en.wikipedia.org/wiki/Hash_table
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27349469/viewspace-2123037/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27349469/viewspace-2123037/