ORACLE之热块优化方法小结
访问频率非常高的数据块被称为热块(Hot Black),当很多用户同时访问某个数据块时,就会导致Lanch争用。常见的Latch争用
A buffer busy waits
B cache buffer chain
一、如果是cache buffer chain Latch的,要看访问相同数据块的会话会太长或这个列表太长
1)如果这个列表太长
原因:
如果一条hash chain比其他hash chain长,它所管理的buffer header更多,那么发生cache buffers chains latch等待的几率就更大
办法:
针对这个问题,可以通过增加hash bucket的数量进而影响hash chain的数量来达到缩短hash chain的目的.参数_db_block_hash_latches
原理:8.0之前,hash buckets的个数等于db block buffers/4。也就是说,平均一个hash bucket上挂4个block。当然这只是平均值,很有可能通过hash函数转换block header的地址后会导致一个bucket上挂很多个block。
8.0以后,oracle改成了hash buckets=2*db block buffers。主要是为了降低一个bucket上挂的block的个数,从而减少扫描bucket所花费的时间。所以说,平均是一个bucket上挂0.5个block。但是实际上,一个bucket上可能会挂较多的block,而有些bucket上则没有挂block,这由block的地址决定。所以,增加bucket的个数,就可以增加block平均分布在bucket上的概率。
注:其实在Oracle9i之后,我们基本上不会遇到这个问题了,除非遇到Bug。所以这个是不推荐的,记住,在对Oracle的隐含参数做修改之前一定要咨询Oracle Support。
2)访问相同数据块的会话会太长
原因:
当多个会话重复访问一个或多个由同一个子chche buffer chains锁存器保护的块时,热块就产生。
办法:
调整隐藏参数_spin_count,增加进程成功获取latch的可能性,这个方法要慎用,增大_spin_count会增加cpu的负荷从而可能造成负面效果
二、如果是buffer busy waits
1、、找到热块产生的SQL,根据执行计划查看是否合理,改变SQL为最优SQL
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语句。比如的表的连接方式,访问路径等。
2、加大表或者索引的PCTFREE,使每个数据块中存放更少的行
调整PCTFREE时, 参数虽然是可以动态调整的,但是只能对新的数据插入起作用,对于已经填充过满的老数据块,是无法起作用的,必须调整参数后,对表进行重组,才能对表中的所有数据块都起作用。
表中的行分布在更多的数据块上,导致Oracle需要读取更多的数据块来完成查询,大大降低了一个数据块被重复读取的概率。降低了数据的性能。
3、减小表空间BLOCK SIZE,使每个数据块中存放更少的行
如果减小表空间BLOCK SIZE,可能会造成行链接,也可能造成性能上影响。
5、使用hash 簇表和使用HASH 分区表,使数据分布更为分散
6、降低程序的并发度,如果程序中使用了parallel查询,降低parallel degree,以免多个
parallel slave同时访问同样的数据对象而形成等待降低性能。
7、把经常操作的小表写入内存
8、增加hot block上的initrans值。
但注意不要把initrans值设置的过于高了,通常设置为5就足够了。因为增加事务意味着要增加ITL事务槽,而每个ITL事务槽将占用数据块中24个字节长度。
默认情况下,每个数据块或者索引块中是ITL槽是2个,在增加initrans的时候,可以考虑增大数据块所在的表的PCTFREE值,这样Oracle会利用PCTFREE部分的空间增加ITL slot数量,最大达到maxtrans指定。