作者：david_zhang@sh 【转载时请以超链接形式标明文章】
链接：http://www.cnblogs.com/david-zhang-index/p/3873357.html
【测试1】低效的SQL引起的catch buffers chains 锁存器争用
创建测试表
1 create table cbc_test(id number,name char(100)); 2 insert into cbc_test(id,name) select rownum,object_name from dba_objects;
创建索引
1 create index cbc_test_idx on cbc_test(id);
创建扫描表的procedure
1 create or replace procedure cbc_do_select
2 is 3 begin 4 for x in (select /*+ INDEX(cbc_test cbc_test_idx)*/ * from cbc_test where id >= 0) loop 5 null; 6 end loop; 7 end; 8 /
同时打开2000个会话执行读取工作
1 var job_no number; 2 begin 3 for idx in 1..2000 loop 4 dbms_job.submit(:job_no,'cbc_do_select;'); 5 commit; 6 end loop; 7 end; 8 /
查看整个系统当前等待
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;
结果如下：
1 EVENT TOTAL_WAITS TIME_WAITED 2 ------------------------------ ----------- ----------- 3 SQL*Net message from client 44 46352 4 library cache lock 202 253 5 buffer busy waits 56 113 6 latch: In memory undo latch 43 86 7 latch: cache buffers chains 33 55 8 control file sequential read 14154 22 9 db file sequential read 82 18 10 events in waitclass Other 6 11 11 library cache: mutex X 8 10 12 latch: shared pool 4 5 13 latch: row cache objects 1 2 14 SQL*Net message to client 45 0 15 Disk file operations I/O 4 0 16 SQL*Net break/reset to client 1 0 17 log file sync 1 0
根据cache buffers chains事件，找出对应的latch地址
1 select addr, gets, misses, sleeps 2 from v$latch_children 3 where name = 'cache buffers chains' 4 and misses > 100000 5 order by 3 desc;
结果：
1 ADDR GETS MISSES SLEEPS 2 ---------------- ---------- ---------- ---------- 3 0000000124ECCBB0 10485987 5260833 0 4 0000000124E2B5A8 20671341 9806756 27942
根据ADDR找出关联的文件号和块号
1 SQL> select FILE#,DBABLK,CLASS,STATE from x$bh where HLADDR='0000000124E2B5A8'; 2 3 FILE# DBABLK CLASS STATE 4 ---------- ---------- ---------- ---------- 5 4 3585766 1 1 6 3 9411 20 1 7 4 3651302 1 1 8 4 3581428 1 1 9 6 71918 1 1 10 6 71918 1 3 11 6 71918 1 3 12 6 71918 1 3 13 6 71918 1 3 14 6 71918 1 3 15 3 5073 38 0
根据文件号和块号，找出对象名称，输入6和71918
1 select owner, segment_name 2 from dba_extents 3 where file_id = &p1 4 and &p2 between block_id and block_id + blocks - 1;
找出的对象名称
1 OWNER SEGMENT_NAME 2 ------------------------------ ------------------------------ 3 SCOTT EMP_FP_IDX1
待续。。。
