实验一：延迟块清除的产生
1.先构建一个1万条数据的表
create table test as select * from dba_objects where rownum<10001;
2.使用循环来插入数据。
初始数据1W条，则每次循环后表内数据量依次是2，4 ，8 ，16 ，32 ，64 ，128， 256，512，1024，需要10次循环。
begin
for i in 1 .. 10 loop
insert into test select * from test;
commit;
end loop;
end;
3.执行查询，执行计划中可以看到 2204 redo size
SQL> select count(*) from test;
COUNT(*)
———-
5120000
SQL> select count(1) from dba_indexes where table_name=’TEST’; —没有索引
COUNT(1)
———-
0
SQL> alter system flush buffer_cache; —清空buffer_cache
System altered.
SQL> set autotrace on
SQL> select count(*) from test where object_name like ‘%gc%’;

COUNT(*)
———-
0
Execution Plan

省略。。。

Statistics
———————————————————-
5 recursive calls
0 db block gets
101073 consistent gets
67716 physical reads
2204 redo size
421 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

实验二：延迟块清除的避免
因为延迟块清除有可能会导致ORA-01555错误(操作大数据时），所以应该避免。
在操作大数据量的DML操作后做一个select 操作。

建表并使用存储过程插入数据
SQL> create table test as select * from dba_objects where rownum<10001;
Table created.
SQL>
declare
v_select clob;
begin
for i in 1 .. 5 loop
insert into test select * from test;
commit;
select count(*) into v_select from test;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> set autotrace on;
SQL> alter system flush buffer_cache;

从查询结果中可以看到， 0 redo size，没有产生REDO。
SQL> select count(*) from test where object_name like ‘%gc%’;
COUNT(*)
———-
0
Execution Plan
省略。。。
Statistics
———————————————————-
5 recursive calls
0 db block gets
4212 consistent gets
4363 physical reads
0 redo size
421 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

1.buffer cache的大小：
如16CPU,32G内存：
初步分配：
SGA_TARGET=16G –内存一半
BUFFER CACHE=12G —SGA的40%起，可设置到70%-80%–保守点也可以设置到10G，后期可以根据系统运行情况结合buffer pool advisory进行修改。
shared_pool=3G

当参数：statistics_level为typical 或者all；或者db_cache_advice参数设置为ON，ORACLE会根据系统负载，自动估算出当前实例的buffer cache最佳大小 –通过v$db_cache_advice;或者ASR报告中：buffer pool advisory一栏。之后可以根据建议大小结合当前系统内存使用情况设置一个合理值。
以下为我虚拟机中环境数据：–SIZE_FOR_ESTIMATE单位为M，系统当前BUFFER_CACHE大小为32M。
SYS@ bys3>select SIZE_FOR_ESTIMATE,BUFFERS_FOR_ESTIMATE,ESTD_PHYSICAL_READ_FACTOR,ESTD_PHYSICAL_READS from v$db_cache_advice;
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
—————– ——————– ————————- ——————-
4 496 2.4576 527548
8 992 2.3653 507728
12 1488 2.2055 473442
16 1984 1.887 405072
20 2480 1.3526 290349
24 2976 1.1332 243258
28 3472 1.0613 227818
32 3968 1 214661
36 4464 .9342 200530
40 4960 .8871 190428
44 5456 .7987 171447
48 5952 .7203 154614
52 6448 .66 141675
56 6944 .6248 134123
60 7440 .5859 125766
64 7936 .5529 118684
从此视图可以看到：系统当前BUFFER_CACHE大小为32M，ESTD_PHYSICAL_READS字段是物理读的块数，ESTD_PHYSICAL_READ_FACTOR字段是物理读/逻辑读的比值，越小越好。
可以看到，BUFFER_CACHE越大，物理读块数越少，物理读/逻辑读的比值越小，所以我这实验环境是要增大BUFFER_CACHE的。可以先增加到64M，然后运行一段时间，再继续结合v$db_cache_advice视图，最终设置一个最优值。
2.buffer cache的命中率
命中率高意味着服务器进程基本上都是从 buffer cache中获取数据块。
但是有的情况下命中率并不能说明性能问题，
如大表全表扫描时buffer cache命中率会下降；
热块问题严重时，虽然buffer cache命中率是100%，性能却不佳；
执行计划出错，所涉及的数据块都在buffer cache中，命中率100%，性能不佳；
3.buffer cache的争用
主要指标是：free buffer waits/buffer busy waits
原因主要也就是：
free buffer waits-找不到空闲 buffer–buffer cache空间不足
buffer busy waits–热块争用

其实在前面的等待事件的博文中，也已经提到过详细解决思路，这里不重新贴了。

DBWR：谁扣动了DBWR的扳机
（1）、三秒超时
I. 检查LRUW
II. 结合硬件能力，查看脏块数、Redo块数，判断恢复时间。如果恢复时间过长，开始增量检查点写。
（2）、关闭数据库或执行完全检查点
（3）、日志切换遇到检查点未完成等待
（4）、OFFLINE表空间或OFFLINE数据文件时
（5）、Truncate 某个对象时
（6）、直接读路径读前。
（7）、_db_block_max_scan_pct，free buffer waits
（8）、_db_large_dirty_queue 脏块数在BUFFER CACHE中比例：达到也要DBWR

OLTP中11G的 直接路径读/自适应游标特性 –建议关闭

检查点写和LRUW写的比例
DBWR checkpoint buffers written：由增量检查点机制或日志切换触发，此资料记录所有从CKPT-Q中写到磁盘中的脏块数量。
physical writes from cache：总物理写（块数）
（physical writes from cache-physical writes non checkpoint）：纯增量检查点机制写（不包括日志切换等等）
physical writes from cache-DBWR checkpoint buffers written：从LRUW或其他链表中写出的脏块。
CKPT-Q：主要写脏块

检查点写：目的是为了实例恢复，不考虑脏块的冷、热。
检查点不频繁：
（1）、脏块数过多，增加扫描LRU链表的时间、增加LRU Latch的持有时间。
（2）、实例恢复时间较长。
LRUW写：LRUW写多，对于提升写效率有帮助。可以跳过热脏块，减少写IO块数 因为：(1). 热脏块不会被移到LRUW (2). 从LRUW中写的脏块，后被移往辅助LRU。从CKPT-Q中写的脏块，不影响它在LRU中的位置。
MTTR参数：在保证LRU Latch竞争没有大幅度增加情况下，让检查点间隔时间尽量长。

DBWR写和db file parallel write的理解
写IO 等待 aiowait
db file parallel write：和写IO次数并没有对应关系，和batch有关
redo file size 500M —> 2G，db file parallel write等待时间减少了

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语句。比如的表的连接方式，访问路径等。