【原】Cache Buffer Chain 第四篇

作者: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

待续。。。

 

posted @ 2014-07-28 16:21  david_zhang@sh  阅读(926)  评论(0编辑  收藏  举报