高级OWI学习笔记，select引起的read by other session latch争用！

继续owi学习，欢迎大家拍砖，谢谢！

上篇学习地址： http://www.itpub.net/viewthread.php?tid=1401723

背景：
select引起的buffer lock争用，发生在将相同块载入到内存的过程中。在Oracle 9i中，相当于
      Reason code 130 的buffer busy waits等待。

获取buffer lock过程：      
获得buffer lock的模式有shared 模式和exclusive 模式， 获取缓冲区的过程中需要以shared 模式获得锁，修改过程中需
要以exclusive模式获得锁。欲获得buffer lock的会话之间没有共享性时，就会发生争用。buffer lock 和cache buffer chains
latch、Tx 锁一起对缓冲区的修改器到同步化的作用。为了修改 一个行，获得latch或lock的过程如下：
      1  为了查找要修改的行相应块存在的位置（hash chain），请求cache buffer chain latch。
      2  对载入块的缓冲区请求buffer lock,并释放cache buffers chain latch。
      3  请求Tx lock，修改相应行。若在此过程中没有获得Tx lock，则释放buffer lock 并进入等待状态，之后重复过程1
      4  修改相应（欲修改的）行后，释放buffer lock.

测试方案：   
      1 创建拥有5万行的BFW_TEST表。
    2 多个进程同时对BFW_TEST表执行select。
    3 在此过程中，多个进程同时想对同一个块获得buffer lock过程中，发生buffer lock竞争。



创建BFW_TEST表

SQL>  create table bfw_test(id char(1000)) ;

Table created

生成50000行数据

SQL> insert into bfw_test select ' ' from dba_objects where rownum <= 50000;

48322 rows inserted


创建对BTW_TEST表执行select的Procedure

SQL> create or replace procedure bfw_do_select
  2  is
  3  begin
  4  for x in (select * from bfw_test) loop
  5  null;
  6  end loop;
  7  end;
  8  /

Procedure created

同时在20个会话上执行select

SQL> var job_no number;
SQL>  begin
  2  for idx in 1 .. 20 loop
  3  dbms_job.submit(job=>:job_no,what=>'bfw_do_select;',interval=> 'SYSDATE+1/14400');
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed
job_no
---------
1005

查看等待事件：

SQL> Select count(*), event
  2    from v$session_wait
  3   where event not in ('smon timer', 'pmon timer', 'rdbms ipc message',
  4          'SQL*Net message from client')
  5   group by event
  6   order by 1 desc;

  COUNT(*) EVENT
---------- ----------------------------------------------------------------
         9 read by other session         
         1 Queue Monitor Wait
         1 db file scattered read
         1 log file parallel write

说明：
     读取位于SGA上的块时是以shared模式获得buffer lock的，所以不发生读取工作引起的buffer lock争用。但是发生物理I/O后将新
块载入到SGA需要创建新的缓冲区，因此最初创建缓冲区的进程以exclusive模式获得buffer lock。这样其他想以shared 模式读取此块的会话
需要等待exclusive 模式的buffer lock释放，此时就会发生read by other session等待。


解决方案：
1 应该通过对SQL进行优化，以便能以最少的I/O获取所需的结果。
2 增加SGA物理大小。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23203681/viewspace-701254/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23203681/viewspace-701254/