【原】Cache Buffer Chain 第四篇

作者：david_zhang@sh 【转载时请以超链接形式标明文章】

链接：http://www.cnblogs.com/david-zhang-index/p/3873357.html

【测试1】低效的SQL引起的catch buffers chains 锁存器争用

创建测试表

 create table cbc_test(id number,name char(100));

 insert into cbc_test(id,name) select rownum,object_name from dba_objects;

创建索引

 create index cbc_test_idx on cbc_test(id);

创建扫描表的procedure

 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个会话执行读取工作

 var job_no number;

 begin

 for idx in 1..2000 loop

 dbms_job.submit(:job_no,'cbc_do_select;');

 commit;

 end loop;

 end;

 /

查看整个系统当前等待

 SELECT event,

   total_waits ,

   time_waited

 FROM v$session_event

 WHERE sid=

   (SELECT sid FROM v$mystat WHERE rownum=1

   )

 ORDER BY 3 DESC;

结果如下：

 EVENT                          TOTAL_WAITS TIME_WAITED

 ------------------------------ ----------- -----------

 SQL*Net message from client             44       46352

 library cache lock                     202         253

 buffer busy waits                       56         113

 latch: In memory undo latch             43          86

 latch: cache buffers chains             33          55

 control file sequential read         14154          22

 db file sequential read                 82          18

 events in waitclass Other                6          11

 library cache: mutex X                   8          10

 latch: shared pool                       4           5

 latch: row cache objects                 1           2

 SQL*Net message to client               45           0

 Disk file operations I/O                 4           0

 SQL*Net break/reset to client            1           0

 log file sync                            1           0

根据cache buffers chains事件，找出对应的latch地址

 select addr, gets, misses, sleeps

   from v$latch_children

  where name = 'cache buffers chains'

    and misses > 100000

  order by 3 desc;

结果：

 ADDR                   GETS     MISSES     SLEEPS

 ---------------- ---------- ---------- ----------

 0000000124ECCBB0   10485987    5260833          0

 0000000124E2B5A8   20671341    9806756      27942

根据ADDR找出关联的文件号和块号

 SQL> select FILE#,DBABLK,CLASS,STATE from x$bh where HLADDR='0000000124E2B5A8';

      FILE#     DBABLK      CLASS      STATE

 ---------- ---------- ---------- ----------

          4    3585766          1          1

          3       9411         20          1

          4    3651302          1          1

          4    3581428          1          1

          6      71918          1          1

          6      71918          1          3

          6      71918          1          3

          6      71918          1          3

          6      71918          1          3

          6      71918          1          3

          3       5073         38          0

根据文件号和块号，找出对象名称，输入6和71918

 select owner, segment_name

   from dba_extents

  where file_id = &p1

    and &p2 between block_id and block_id + blocks - 1;

找出的对象名称

 OWNER                          SEGMENT_NAME

 ------------------------------ ------------------------------

 SCOTT                           EMP_FP_IDX1

待续。。。

巴特西

【原】Cache Buffer Chain 第四篇

最新文章

热门文章