oracle 消除块竞争(hot blocks)

上篇日志提到了，那么高的负载，是存在数据块读竞争，下面介绍几个方法来消除块竟争

查找块竟争

SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'read by other session';

如果查出来是相同的块或是相近的范围，这就是存在竞争的块或对象

找出对象

SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1;

解决竟争
1，优化低效查询
2，重组热块数据
reinsert and delete hot row will move them to new datablock,可以减小热块提高性能，

SELECT data_object_id 
 FROM dba_objects 
 WHERE owner='&owner' AND object_name='&object';
 
 SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,0) start_rowid
 FROM dual;
 --rowid for the first row in the block
 
 SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,500) end_rowid
 FROM dual;  
 --rowid for the 500th row in the block
 
Insert <owner>.<segment_name>
select *
 FROM <owner>.<segment_name>
 WHERE rowid BETWEEN <start_rowid> AND <end_rowid>

delete FROM <owner>.<segment_name>
WHERE rowid BETWEEN <start_rowid> AND <end_rowid>

3,调整 PCTFREE
djusting the PCTFREE value downward for an object will reduce the number of rows physically stored in a block

Note: PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks, while freelists, pctused and pctincrease are ignored with ASSM because they are used for transaction/block management

4，把hot 对象移动到小block size tablespace中 如 2k block size

5，优化索引
a low cardinality index has a relatively small number of unique values, e.g. a column containing state data with only 50 values. Similar to inefficient queries, the use of a low cardinality index could cause excessive number of blocks to be read into the buffer cache and cause premature aging out of “good” blocks

6，反转索引优化 减小index block 竟争

7，cluster hash table, hash partition table 来打算数据