1。模拟思路:
原理上来说,buffer cache中block的header是被放置到hash chains上,而hash chains又是放在hash bucket中,多个hash bucket被一个cache buffers chains latch保护。当多个session并发访问同一个数据块上的数据,每个session都要首先获得cache buffers chains latch,这样将造成cache buffers chains latch的争用。
2。试验步骤
(1)创建测试表big1,并创建索引
SQL> create table big1 as select * from sys.dba_objects;
Table created.
SQL> create index idx_big1 on big1(object_id);
Index created.
(2)打开5个session,各自执行如下过程:
declare
r integer;
begin
for i in 1 .. 3000000 loop
select object_id into r from big1 where object_id=97;
dbms_lock.sleep(0.001);
end loop;
end;
/
(3)执行同时查看相应的等待事件:
select spid,
a.sid,
d.SQL_ID,
d.SQL_TEXT,
d.SQL_FULLTEXT,
a.EVENT
from v$session a, v$process b, v$sesstat c, v$sqlarea d
where c.statistic# = 12
and c.sid = a.sid
and a.paddr = b.addr
and a.status = 'ACTIVE'
AND A.TYPE = 'USER'
and a.SQL_ID = d.SQL_ID /* and SQL_TEXT like 'SELECT * FROM( SELECT X.* , ROWNUM RN%'*/
order by value desc;
SPID SID SQL_ID SQL_TEXT SQL_FULLTEXT EVENT
1 535 718 4fpd5mb7pqmqw SELECT OBJECT_ID FROM BIG1 WHERE OBJECT_ID=97 <CLOB> latch: cache buffers chains
2 543 654 4fpd5mb7pqmqw SELECT OBJECT_ID FROM BIG1 WHERE OBJECT_ID=97 <CLOB> latch: cache buffers chains
3 539 582 4fpd5mb7pqmqw SELECT OBJECT_ID FROM BIG1 WHERE OBJECT_ID=97 <CLOB> latch: cache buffers chains
4 541 581 4fpd5mb7pqmqw SELECT OBJECT_ID FROM BIG1 WHERE OBJECT_ID=97 <CLOB> latch: cache buffers chains
可见,出现大量的cache buffers chains的latch free等待事件。