mysql热块争用_消除热块（hot block）

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

查找块竟争

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,,,,0) start_rowid

FROM dual;

--rowid for the first row in the block

SELECT dbms_rowid.rowid_create(1,,,,500) end_rowid

FROM dual;

--rowid for the 500th row in the block

Insert .

select *

FROM .

WHERE rowid BETWEEN AND

delete FROM .

WHERE rowid BETWEEN AND

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 来打算数据