文章转自：http://oracle.chinaitlab.com/induction/862509.html，文章前部分转载，后部分自己加上的。
Oracle数据库只读模式的CACHE BUFFERS CHAINS测试是本文我们主要要介绍的内容,虽然从Oracle 9i里边引入了只读模式的CACHE BUFFERS CHAINS,但是在获取BUFFER LOCK的时候,还是会请求CACHE BUFFERS CHAINS,但是情形没有8i里边那么严重了,接下来我们对其进行测试.
测试步骤如下:
1.创建表
1 create table test (a int,b varchar2(20));
2.往这个表里INSERT 50000行数据
1 declare i int; 2 begin for i in 1..50000 loop insert into test values(i,'adsfsafsa'); 3 end loop; 4 end;
3.在A的栏位上创建一个INDEX
1 create index test_i on test(a);
4.编一个存储过程，进行大量的索引扫描操作
1 create or replace procedure test_k is 2 begin for i in (select /*+ INDEX(TEST TEST_I) */ * from test where a > 20000) loop null; 3 end loop; 4 end;
5.打开2000个会话来执行这个存储过程
1 var i number; 2 begin for j in 1..2000 loop dbms_job.submit(:i,'TEST_K;'); 3 commit; 4 end loop; 5 end;
6.查询等待事情发现latch: cache buffers chains
1 SELECT event, 2 total_waits , 3 time_waited 4 FROM v$session_event 5 WHERE sid= 6 (SELECT sid FROM v$mystat WHERE rownum=1 7 ) 8 ORDER BY 3 DESC;
EVENT TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- SQL*Net message from client 223 167283 resmgr:cpu quantum 14 1022 db file sequential read 222 21 library cache lock 47 12 control file sequential read 15400 7 latch: shared pool 1 1 direct path write 30 1 SQL*Net message to client 224 0 SQL*Net more data to client 1 0 SQL*Net break/reset to client 10 0 library cache: mutex X 2 0 events in waitclass Other 6 0 log file sync 4 0 enq: RO - fast object reuse 3 0 buffer busy waits 3 0 latch: cache buffers chains 8 0 Disk file operations I/O 8 0 latch: In memory undo latch 8 0
结论:就算以只读模式获取cache buffers chains LATCH并不能完全解决这个LATCH争用的问题,只是争用程度不那么严重了.
