buffer cache实验7-buffer busy waits-完成

1.buffer busy waits产生原理分析：

一次逻辑读时CBC latch锁及Buffer pin锁的获取和释放过程如下：
1.加Latch X
2.进入hash chain，在相应的BH上加Buffer pin S (0-->1)
3.释放Latch X
4.进行逻辑读--也就是通过BH中的buffer adderss找到数据块在内存中真实位置  ---假如读了1MS
5.加Latch X
6.释放Buffer pin S (1-->0)  0:没锁  1:共享锁 -SELECT    2:独占锁-DML
7.释放Latch X
从这个过程中可以发现和推断：比如进程A在BH上加buffer pin 独占锁时，此时CBC latch已经释放，
在高并发环境下，此时如有进程B获取CBC latch，在hash chain上找到BH，准备加锁时，发现bh上已经加了独占锁，就发生了buffer busy waits
要注意的是Buffer pin 上的共享锁不会阻塞独占锁，独占锁会阻塞独占锁--即当前读时，再有进程来当前读。独占锁也会阻塞共享锁--即当前读阻塞一致性读

在实验中可以发现：读进程也会阻塞写进程，但是次数相比写进程阻塞读进程的，非常少。这是因为回滚段的块争用引起BUFFER BUSY WAITS
读时构造CR块需要在UNDO块加一致读块，一个事务的多个DML语句可能对应同一个UNDO。读操作使用UNDO块构造CR块，可能会引起BUFFER BUSY WAITS。这个可以考虑：同一事务的多个DML语句分多次提交，减少UNDO块竞争--可以COMMIT时加个if mod(a,5000)=0;commit的判断 每5K或者1W次提交下。
当然在BH上加buffer pin锁和释放是一个很快的过程-ns级吧；这里只是说了一个理想情况下的假设。
同一事务同一回滚块，直到回滚块满，再用下一个。
不同事务，使用不同回滚块。
同一回滚块在同一时间只能有一个事务。
10G后，有buffer busy waits与read by other session

2.buffer busy waits争用的常见场景：

表的热块--多个会话并发访问同一数据块，
ASSM下段头的争用--超高并发插入时，对段头L1块的争用--详见：http://blog.csdn.net/haibusuanyun/article/details/18776657#t2
不合理PCTFREE值引起的块空闲状态的频繁变化，L1块争用--详见：http://blog.csdn.net/haibusuanyun/article/details/18776657#t2
MSSM管理下更容易在大并发插入时因段头争用产生buffer busy waits，原理和ASSM下高并发插入对L1块争用原理类似，只是现在用MSSM比较少，不详说了。
undo header，可以通过增加回滚段(rollback segment)来解决缓冲区的问题。如果等待位于undo block上，我们可能需要检查相关应用，适当减少大规模的一致性读取，或者降低一致性读取(consistent read)的表中的数据密度或者增大DB_CACHE_SIZE，或者是在事务的多个DML语句中增加提交的次数。--- 也就是OLTP中尽量用小事务可以降低buffer busy waits等待。
还有一个冷门的是： IMU模式有助减少BUFFER BUSY WAITS等待--减少从BUFFER CACEH中构建CR块从而减少了LATCH竞争。

热块在导致 buffer busy waits的同时，因为并发访问，还需要获取CBC LATCH。可能的情况已经有会话获取了Buffer pin锁，此时会话1是获取了CBC LATCH，但是无法获得Buffer pin锁，此时产生buffer busy waits。
如果有其它会话想要访问此CBC LATCH下的其它hash chain或者访问和会话1相同的BH，还会出现CBC LATCH等待。

3.read by other session等待

常见情况是：一个或多个会话要访问buffer cache中不存在的块，发现此数据块正在被另一会话读取。此时，正在读取数据块的会话出现：db file sequential read  / db file scattered read;
其它会话出现：read by other session

4.解决buffer busy waits争用方法是：

临时方法：搜集相关数据，KILL会话，临时解决，让系统先恢复--不过可能正常一下很快就又不行了。
治标方法：找出涉及热块争用的SQL,优化SQL，或者调整应用。

5.热块是普通表上的数据块的解决方法:

1.增大PCTFREE或者使用更小的block size
2.使用小数据块-2K-4K
3.使用HASH表分区
3.反向索引
热块是段头的L1块：-ASSM下段头的争用--超高并发插入时，对段头L1块的争用--详见：http://blog.csdn.net/haibusuanyun/article/details/18776657#t2
热块是undo header：可以通过增加回滚段(rollback segment)来解决缓冲区的问题。
热块是undo block上：我们可能需要检查相关应用，适当减少大规模的一致性读取，或者降低一致性读取(consistent read)的表中的数据密度或者增大DB_CACHE_SIZE。
4.常操作的小表考虑放进KEEP POOL
5.调整隐藏参数_spin_count，增加进程成功获取latch的可能性，这个方法要慎用，增大_spin_count会增加cpu的负荷从而可能造成负面效果

6.查询有buffer busy waits等待的SQL语句及相应热块的块号文件号

查出最近有buffer busy waits的SQL语句：---可能通过V$ACTIVE_SESSION_HISTORY视图进行查询：关于此视图详见：http://blog.csdn.net/haibusuanyun/article/details/17959973

查出过去十分钟内产生buffer busy waits的语句P1 数据文件号，P2数据块号，P3数据块类型：  --把等待事件改一下，就可以查CBC LATCH等等了。

select SESSION_ID，event,SQL_ID,p1text,p1,p2text,p2,p3text,p3，WAIT_TIME from v$active_session_history where event like 'buffer busy waits%' and sample_time=sysdate-10/(24*60);
如果语句多且重复可能要用到GROUP BY .

--查询30分钟之内的最占用时间的sqlid 与等待事件是buffer busy waits/cache buffers chains
col event for a20
select sql_id,event,count(*) from v$active_session_history
where sample_time > sysdate - 30/1440   
group by sql_id,event order by count(*) desc;

通过SQL_ID找出SQL语句：
select sql_text from v$sql where SQL_ID='';

7.关于产生buffer busy waits的实验

见：点击打开链接

##############################
附：还有一种查法：查找热块及对应的SQL语句--因为涉及X$BH，生产环境如buffer cache大，还涉及到按TCH排序，也容易产生性能问题甚至系统HANG，不建议用）：
A、找到最热的数据块的latch和buffer信息
select   b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps   from   
(select   *   from   (select   addr,ts#,file#,dbarfil,dbablk,tch,hladdr   from   x$bh   order   by   tch   desc)   where   rownum <11)   a,
(select   addr,gets,misses,sleeps   from   v$latch_children   where   name= 'cache   buffers   chains ')   b
where   a.hladdr=b.addr;

B、找到热点buffer对应的对象信息：
col   owner   for   a20
col   segment_name   for   a30
col   segment_type   for   a30

select   distinct   e.owner,e.segment_name,e.segment_type   from   dba_extents   e,   
(select   *   from   (select   addr,ts#,file#,dbarfil,dbablk,tch   from   x$bh   order   by   tch   desc)   where   rownum <11)   b
where   e.relative_fno=b.dbarfil
and   e.block_id <=b.dbablk
and   e.block_id+e.blocks> b.dbablk;

C、找到操作这些热点对象的sql语句：
break   on   hash_value   skip   1
select   /*+rule*/   hash_value,sql_text   from   v$sqltext   where   (hash_value,address)   in   
(select   a.hash_value,a.address   from   v$sqltext   a,(select   distinct   a.owner,a.segment_name,a.segment_type   from   dba_extents   a,
(select   dbarfil,dbablk   from   (select   dbarfil,dbablk   from   x$bh   order   by   tch   desc)   where   rownum <11)   b   where   a.relative_fno=b.dbarfil
and   a.block_id <=b.dbablk   and   a.block_id+a.blocks> b.dbablk)   b
where   a.sql_text   like   '% '||b.segment_name|| '% '   and   b.segment_type= 'TABLE ')
order   by   hash_value,address,piece;

D.根据执行计划,调整SQL语句。比如的表的连接方式，访问路径等。